Silly little Concatenate

[jdash]

I'm racking my brain to get this and I am sure I am missing something simple?

I have 2 columns and want to concatenate them into a third. If there is data in Col 1 then there is no data in Col 2. I want Col 3 to print Col1 if there is data there. If there is data in Col2 then I want to Concatenate(last non-zero value from Col1,"_",Col2)

So if I have (pardon the dots, they are to get the spacing right)

Col1 .....Col2 .... Col3
01.....................01
............0013.....01_0013


What am I missing, tried this:

=IF($AE11="",AD11,CONCATENATE(INDEX($AD$2:$AD11,MATCH(9999,$AD$2:$AD11)),"_",AE11))

But I think maybe because Col 1 is general data not necessarily a number that it got messed. So then I tried to match for text and still nada.

??

 

[alx7000]

Hey jdash,

Slightly different approach but the following might work:

Excel Workbook
	AD	AE	AF
1
2	01		01
3		0013	01_0013
4		0014	01_0014
5	02	0013	02_0013
6		0016	02_0016
7	01		01
8		0013	01_0013
9		0014	01_0014
10	02	0013	02_0013
Sheet1

Excel 2010

Cell Formulas
Range	Formula
AF2	=IF(AE2="",AD2,IF(AD2="",IFERROR(LEFT(AF1,FIND("_",AF1)),AF1&"_")&AE2,AD2&"_"&AE2))

It uses IFERROR so assumes you are using 07/10, if not you'll need to use an IF(ISERROR()) instead.

Hope this does what you were after, any questions please let me know.

Cheers,
alx7000

 

[jdash]

Re: [SOLVED] Silly little Concatenate

Yes mate!

Far more brilliant and simple than what I ended up doing, which was create unique IDs for each 1st column entries, then in another column I ran this:

=VLOOKUP(LARGE($J$2:$K13,1),$J$2:$K13,2)

Which sought out the unique IDs and basically pulls the last Col1 entry. This pasted down filled the column which then I concatenated with

=IF(Y13="",Z13,CONCATENATE(Z13,"_",Y13))

The unique ID was in itself a 2 step process. First I put a 1 in each row where there was an entry in the Col1 using the len() of it. All these values came from a single column where 01 was on a row above 0013, 0014, 0015, etc. So the real task was to create a unique identifier (for future VLOOKUP) for each row using the embedded hierarchy - hence the 01_0013, 01_0014, etc.
Then with the columns of 1's I put another column that summed up those 1s to that row and thereby created a unique id for each heading (our column 1). Those two formulas in case anyone cares are:

=IF(LEN(K13)=8,1,"")
=IF(NOT(F13=""),SUM($F$3:$F13),"")

Sorry those columns jump all over because of course there is lots of other data between them all. Wish I knew how to make the embedded table/spreadsheet like you did to help illustrate.

I know it's awful and has more spins then a Congress person.. but it did finally work. However I'm going to clean it all up and incorporate your process flow, so thanks (wo)man!