Find cell values >0 and return column header label

[camilleso]

Hi, I'm looking for help in creating a formula that can return column header names if the value in the cell is greater than 0. If Id 1 has a cell values >0, I want tag (header 2) and API (header 3) to be returned on a separate list.

Example of data:

Pixel(header1) Tag (header 2) API (header 3)
ID 1 0 5000 100000
ID 2 1000 0 0
ID 3 10000 100000 1000000

 

[Joe4]

Assuming the following:
- ID is in column A
- Pixel is in column B
- Tag is in column C
- API is in column D
- Header is row 1
- Data starts on row 2

Then you can enter this formula in cell E2 and copy down for all rows:

=TRIM(IF(B2>0,B$1,"") & " " & IF(C2>0,C$1,"") & " " & IF(D2>0,D$1,""))

 

[camilleso]

Thank you! this worked. One more question, how can I separate the values within the same formula? I want to add a "+" to separate the values from b, c, d.

 

[Joe4]

Try:

=SUBSTITUTE(TRIM(IF(B2>0,B$1,"") & " " & IF(C2>0,C$1,"") & " " & IF(D2>0,D$1,""))," ","+")

 

[camilleso]

Ok, sorry, one more question. I now need to relabel the values I pulled out in the previous formula. I made an iferror, but also need help separating the values. here is what i have, and currently the values show up next to each other, but not separated by a "+"

=IFERROR(IF(Search("*prebid*",L2,1),"Prebid"),"")&IFERROR(IF(Search("*js*",L2,1),"JS Tag"),"")&IFERROR(IF(Search("*img*",L2,1),"Image Tag"),"")&IFERROR(IF(Search("*s2s*",L2,1),"RT API"),"")

 

[Joe4]

Try:

[COLOR=#000000][FONT=Inconsolata]=SUBSTITUTE(TRIM([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IFERROR[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Search[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"*prebid*"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]L2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Prebid "[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IFERROR[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Search[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"*js*"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]L2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"JS Tag "[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IFERROR[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Search[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"*img*"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]L2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"Image Tag "[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IFERROR[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]IF[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]Search[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"*s2s*"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]L2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"RT API"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]""[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]))," ","+")
[/FONT][/COLOR]

 

[camilleso]

Great, thank you! This worked

 

[camilleso]

Hi, so this worked, but it also now added a "+" in the values in addition to the spaces in between. For example, the values I'm relabeling are to be JS Tag, RT API, Image Tag. When I added this new formula, the values came out like JS+Tag, Image+Tag instead of JS Tag and Image Tag. Is there anyway to leave the space in the value but add the "+" in between each value?

 

[Joe4]

What version of Excel are you using?
If using the newest version, the TEXTJOIN function is perfect for this.
See: https://www.techonthenet.com/excel/formulas/textjoin.php

 

[camilleso]

I'm not sure, I'm using it through google. it's an online version, not offline.