How to find a text in a group of columns and return the matching column headers.

Ramashesh

New Member
Joined
Dec 18, 2016
Messages
6
Hi,

I need to know if there is a way to search for a text in a group of columns and return the respective column headers. Also would need a custom text while returning the value.

I have a spreadsheet of 6000 records which gives me the details of the states to which it pertains, so i need a formula to know if the record is applicable to a state and if yes, then display the column header "which has the state code". Here is an example.

Record NameWAORNVIDExpected Result
Record 1XXXXAll
Record 2XN/AN/AXAll - Except OR, NV
Record 3N/AN/AN/AXID Only
Record 4N/AXXXAll - Except WA

<tbody>
</tbody>

So in the above example,
Record 1 - if "X" is present in all the 4 states, then i need to display "All" in the result.
Record 2 - if "N/A" is present in 2 states, then in the result column i need to display custom text "All - Except" and then pull the column headers which has "N/A" (so that i get "All - Except OR, NV")
Record 3 - if "N/A" is present in 3 states and "X" is present in only in one state i need to display the column header of the state which has "X" and also a custom text which says "Only" ( so that i can display "ID Only")
Record 4 - > if "X" is present in 3 states and "N/A" is present in one state then i need to display custom text "All - Except" and then pull the column headers which has "N/A" (so i need to display"All - Except WA")

Can we build a formula which can use be used to get the desired results as mentioned above? I used the Index and match combination, hlookup, vlookup, "If and If error combination" and noting seems to be working.

Your help in this regard would be highly appreciated.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi Ramashesh
Welcome to the board

Please post which excel version you are using so that we know what's available to you.
 
Last edited:
Upvote 0
Unfortunately with excel 2013 you still don't have functions to concatenate arrays and so you can't have a general formula for your problem

If you have always just a very small number of states you could try a formula that will try all the values one by one, not pretty


For a general solution I see several options, all with vba. Here are 2 extreme ones, all vba or minimum vba:

1 - you write a vba solution that reads the table and does all the processing needed and writes the results

2 - you just write a small UDF that concatenates an array and write a normal formula.

This is an example for option 2.

In F2:

=IF(COUNTIF(B2:E2,"X")=COLUMNS(B2:E2),"All",IF(COUNTIF(B2:E2,"X")=1,INDEX($B$1:$E$1,MATCH("X",B2:E2,0))&" Only","All - Except "&ConcatArray(", ",IF(B2:E2<>"X",$B$1:$E$1,""))))

This in an array formula, you have to confirm it with CTRL-SHIFT-ENTER

Copy down

For the ConcatArray() I used:

Code:
' Concats the elements of an array, ignores blanks
Function ConcatArray(sDelimiter As String, vArray As Variant) As String
Dim v As Variant
Dim s As String

For Each v In vArray
    If v <> "" Then s = s & sDelimiter & v
Next v
ConcatArray = Mid(s, Len(sDelimiter))
End Function


I tested with your data:


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >F</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >G</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Record Name</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">WA</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">OR</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">NV</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">ID</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Expected Result</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Record 1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">All</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Record 2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">N/A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">N/A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">All - Except OR, NV</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Record 3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">N/A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">N/A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">N/A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">ID Only</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Record 4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">N/A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">X</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">All - Except WA</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=8 style="background:#9CF; padding-left:1em" > [Book1]Sheet2</td></tr></table>
 
Last edited:
Upvote 0
In the cell that says "All" (F2) in your example, put:
=IF(COUNTIF(B2:E2,"X")=4,"All ",IF(COUNTIF(B2:E2,"X")=1,IF(B2="X",B$1,"")&IF(C2="X",C$1,"")&IF(D2="X",D$1,"")&IF(E2="X",E$1,"")&" Only ","All - Except "&IF(B2="X","",B$1&",")&IF(C2="X","",C$1&",")&IF(D2="X","",D$1&",")&IF(E2="X","",E$1)))

And to the right of that cell, put:
=LEFT(H2,LEN(H2)-1)

And hide the column to the left. You can actually combine both formulas, but it's long. In F2:
=LEFT(IF(COUNTIF(B2:E2,"X")=4,"All ",IF(COUNTIF(B2:E2,"X")=1,IF(B2="X",B$1,"")&IF(C2="X",C$1,"")&IF(D2="X",D$1,"")&IF(E2="X",E$1,"")&" Only ","All - Except "&IF(B2="X","",B$1&",")&IF(C2="X","",C$1&",")&IF(D2="X","",D$1&",")&IF(E2="X","",E$1))),LEN(IF(COUNTIF(B2:E2,"X")=4,"All ",IF(COUNTIF(B2:E2,"X")=1,IF(B2="X",B$1,"")&IF(C2="X",C$1,"")&IF(D2="X",D$1,"")&IF(E2="X",E$1,"")&" Only ","All - Except "&IF(B2="X","",B$1&",")&IF(C2="X","",C$1&",")&IF(D2="X","",D$1&",")&IF(E2="X","",E$1))))-1)
 
Upvote 0
This should cover all the criteria:

=IF(COUNTIFS(B2:E2,"x")=4,"ALL",IF(COUNTIFS(B2:E2,"N/A")=3,LOOKUP("x",B2:E2,$B$1:$E$1)&" only",IF(COUNTIFS(B2:E2,"N/A")=1,"All - Except "&LOOKUP("N/A",B2:E2,$B$1:$E$1),"All - Except "&IF(B2="N/A",B$1&" ","")&IF(C2="N/A",C$1&" ","")&IF(D2="N/A",D$1&" ","")&IF(E2="N/A",E$1,""))))
 
Upvote 0
Hi Vogel,

Thanks for your formula it is working, however this works for the static data but in my case the states might increase and i might have all the 54 states, so i guess the permutations in the formula provided by you needs to be re-written every time. Is my understanding correct?

I there any way i can make it dynamic just by increasing the range of the column when a new state is added?
 
Upvote 0
Hi PCG,

Looks like the second option would work fine for me, but how do i create the UDF. If i copy your code in VBA editor it is asking for a macro name.
 
Upvote 0
Hi PCG,

I was able to create a UDF successfully.

Thanks a ton for your help. The formula with UDF combination worked for my problem statement.

You made my day..

I have tested the second option for 10 states and it worked fine, will this formula extend to as many states as possible?

I appreciate your help in this regard again.
 
Upvote 0
Hi Vogel,

Thanks for your formula it is working, however this works for the static data but in my case the states might increase and i might have all the 54 states, so i guess the permutations in the formula provided by you needs to be re-written every time. Is my understanding correct?

I there any way i can make it dynamic just by increasing the range of the column when a new state is added?


This will accommodate more columns besides the Multiple states listing, small adjustment for additional state necessary.

=IF(COUNTIFS(B2:E2,"x")=COLUMNS(B2:E2),"ALL",IF(COUNTIFS(B2:E2,"N/A")=COLUMNS(B2:E2)-1,LOOKUP("x",B2:E2,$B$1:$E$1)&" only",IF(COUNTIFS(B2:E2,"N/A")=1,"All - Except "&INDEX($B$1:$E$1,,MATCH("N/A",B2:E2,0)),"All - Except "&IF(B2="N/A",B$1&" ","")&IF(C2="N/A",C$1&" ","")&IF(D2="N/A",D$1&" ","")&IF(E2="N/A",E$1,""))))
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top