Link to home
Start Free TrialLog in
Avatar of Candace Hagood
Candace HagoodFlag for United States of America

asked on

Excel combination cell help. Loop?

Parent Child Example.xlsxHi all, 

Trying to create a combined cell that looks at column A and B and combines these values separated by an underscore.  The problem is column A has a sort of header to the section them empty until next section (see attached). Is there a way to say if the column C =  If A is not blank, A&"_"&B, if A is blank go up one row until a not null field is found for A.   I know there is a more efficient and elegant way of framing that as well.  Thank you! 

ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
 =IF(B2="","",LOOKUP("zzzzz",A$1:A2)&"_"&B2)

Open in new window

Based on the sample data, you could do something simple like:


=IF(B2="","",IF(AND(A2="",A1<>""),A1&"_"&B2,LEFT(C1,FIND("_",C1))&B2))

All three suggested formulas are working as you originally requested. See sample workbook attached to this post.

If your workbook isn't working as desired, please post it (or something that reproduces the problem) so we can fix it.

Please make sure that your Excel is in Automatic calculation mode, as copying down a formula in Manual calculation mode won't make it update. You change this setting in the File...Options...Formulas ribbon item.


Parent-Child-Example.xlsx

Just playing with different parent names and it looks like it doesn't matter on sort order for any of the above suggestions. I knew Rory's suggestion would not have any sort order dependency as it just looks at the row above if A is blank whereas thought that my and Brad's suggestions would have. By the way, Brad's LOOKUP is doing the same as I described with my INDEX/MATCH combination but with one function. I am in the habit of using INDEX/MATCH combination rather than lookup as the combination is more versatile and less volatile but neither of those matter where lookup and return value are in the same column.

Another possible problem occurs if you use an absolute reference to row 2 in Rob Henson's formula (and mine). It should be A$1:A2 and not A$1:A$2. The formulas are correct as posted, but if you retyped them, it is possible that an undesired dollar sign was added.
=IF(B2="","",INDEX(A$1:A2,MATCH("zzzzzzz",A$1:A$2))&"_"&B2)   wrong
=IF(B2="","",INDEX(A$1:A2,MATCH("zzzzzzz",A$1:A2))&"_"&B2)   correct
 
=IF(B2="","",LOOKUP("zzzzz",A$1:A$2)&"_"&B2)   wrong
=IF(B2="","",LOOKUP("zzzzz",A$1:A2)&"_"&B2)   correct

Open in new window