Excel Question

[paulrockliffe]

Right.

I have a 4 step hierarchy of detail mapped out in Excel with each layer of detail in a separate column eg:

Top Level.
...................Next Level
......................................Level Three
............................................................Level 4.

Not all of the hierarchies go down to four levels, some have more than one item at each level.

What I want to do is write a formula that will Concatenate each row, so that the full hierarchy for each line is on each line, as well as having the clear breakdown shown like the above. So row 1 of the above would read "Top Level.", row two would read "Top Level. - Next Level" etc. I know hoe to add the hyphens in between the detail names. I've managed to write a formula that does what I want, so long as each row contains all four parts of the hierarchy:

=IF(AND(B6<>"",C6<>"",D6<>"",E6<>"")=TRUE,CONCATENATE(B6," - ",C6," - ",D6," - ",E6),IF(AND(B6<>"",C6<>"",D6<>"")=TRUE,CONCATENATE(B6," - ",C6," - ",D6),IF(AND(B6<>"",C6<>"")=TRUE,CONCATENATE(B6," - ",C6,),IF(B6<>"",B6,""))))

However, I need the formula to Concatenate different cells, ie, go up the rows to the next non-empty cell.

Can this be done without gong all VBA?

Cheers

Edited by paulrockliffe on Monday 29th March 17:22

[paulrockliffe]

I added spaces before the four lines of my example, so that they were stepped and in different columns, but the spaces have vanished, so my example isn't as clear as I thought!

[bitwrx]

Concatenate stuff like this.

="some text" & "-" & cell_references_without_quotes

I'll think about the rest of the stuff.

[Mattt]

Heading	Heading 2
Cell1	Cell2

If you need Excel help, that's the best way to post it.

[paulrockliffe]

Thanks Matt, did you just copy and paste that from Excel or what?

[Mattt]

It's PH format code - quote my post then it'll show you how.

[paulrockliffe]

|

Overview	More Detail	Even More Detail	Full Detail
Athletics
	Info
		1
		2
			4

Ok, cheers, that's the sort of thing I have above. I want to be able to produce I'm allocating bank transactions to different parts of each revenue stream, there are complicated reasons why I'm doing it this way. I need to be able to produce a drop-down list from the above table that will let me to effectively allocate costs to each line, without cluttering the table with the full details. Having them separated in different columns will let me display different level of details depending on circumstance. I need to be able to change the revenue streams easily, so trying not to hard-code any of the table setup info.

Cheers

Edited by paulrockliffe on Monday 29th March 18:13

[theaxe]

I'm not sure I understand but I think that you just need a pivot table. Have you tried this? Also, I think it would be easier if all of the 'Athletics' rows had 'Athletics' in column A. Otherwise you can't sort/group the data together.

Edited by theaxe on Monday 29th March 19:12