getting new value if the reference value has a blank cell in next column

[sarad agarwal]

Hi All,

I have data in this format:

Auditor	Locale_DE
Apple
Apple	Yes
Apple
Orange
Orange
Orange
Grape	Yes
Grape
Grape

What formula will give me this result?

Auditor	Locale_DE	Required Value
Apple
Apple	Yes
Apple
Orange		Orange_Locale_DE
Orange		Orange_Locale_DE
Orange		Orange_Locale_DE
Grape	Yes
Grape
Grape

I.e if all the cells for orange is blank in column b then result in column c should give me the concatenated value of the column a and the header of column b.

 

[Eric W]

How about:

C2: =IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"<>")=0,A2&"_"&$B$1,"")

 

[sarad agarwal]

Thank you so much Eric. It worked like wonders.

Can you also help me in understanding the way this formula is working ?

 

[Eric W]

Sure.

In C2, the COUNTIFS counts how many times that A2 (Apple) appears in A2:A10, AND a non-blank cell appears in B2:B10. In this case it is 1 (row 3). The IF asks, is that zero? It is not, so it goes to the FALSE section of the IF, returning "".

In C5, the COUNTIFS counts how many times that A5 (Orange) appears in A2:A10, AND a non-blank cell appears in B2:B10. In this case it is 0. The IF asks, is that zero? Since it is, it goes to the TRUE section of the IF, returning A5 concatenated (the &) with an underscore and $B$1.

 

[sarad agarwal]

Sure.

In C2, the COUNTIFS counts how many times that A2 (Apple) appears in A2:A10, AND a non-blank cell appears in B2:B10. In this case it is 1 (row 3). The IF asks, is that zero? It is not, so it goes to the FALSE section of the IF, returning "".

In C5, the COUNTIFS counts how many times that A5 (Orange) appears in A2:A10, AND a non-blank cell appears in B2:B10. In this case it is 0. The IF asks, is that zero? Since it is, it goes to the TRUE section of the IF, returning A5 concatenated (the &) with an underscore and $B$1.

Got it, Thank you so much!

 

[Eric W]

You're welcome!