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

[sarad agarwal]

Hi All,

I have data in this format:
[TABLE="width: 155"]
[TR]
[TD]Auditor[/TD]
[TD]Locale_DE[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD] [/TD]
[/TR]
[/TABLE]

What formula will give me this result?
[TABLE="width: 278"]
[TR]
[TD]Auditor[/TD]
[TD]Locale_DE[/TD]
[TD]Required Value[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Yes[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[TD]Orange_Locale_DE[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[TD]Orange_Locale_DE[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[TD]Orange_Locale_DE[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Yes[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
[/TABLE]

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!