Excel: Find value across multiple columns and return header column

Question

I am trying to find a way to look for "Unknown" value across multiple columns. When it is present, I would like to return all of the header columns that the value appears in into one cell for each row in column B.

For example for row 2 (the first row below my header row), I want it to return every column name that "Unknown" appears from Column F to Column Y for row 2 only and put the column name in B2. I want to repeat that process for all of my 9064 rows.

I am using Excel 2010. I looked up Match Index but was not able to find a way to do what I wanted to do. Is there a way to look for a value across multiple columns and return every column header that values appears in for that row and put all of the column headers into one cell?

Data View

" I want the number of times "Unknown" appears" and " return every column header that values appears in for that row and put all of the column headers into one cell" seems to contradict... Can you give an explicit example of the result? — EBH
– EBH, Commented Jul 22, 2016 at 10:16
I've made a correction, that was worded poorly. I would like to return every column name that Unknown is present in for each row. So in my image "Data View", for example, I would like it to have B2 say Install_Method_CD Manuf_Cd Coating_Cd Wall_Thickness Join_Method_Cd etc. because Unknown is in those columns. — elagarde
– elagarde, Commented Jul 22, 2016 at 11:34

score 0 · Accepted Answer · 2016-07-22 14:12:42Z

0

Here's a custom function. First place the following code in a regular module (Alt+F11 >> Insert >> Module >> Copy/paste >> Alt+Q)...

Function AConcat(a As Variant, Optional Sep As String = "") As String
' Harlan Grove, Mar 2002

    Dim Y As Variant

    If TypeOf a Is Range Then
        For Each Y In a.Cells
            AConcat = AConcat & Y.Value & Sep
        Next Y
    ElseIf IsArray(a) Then
        For Each Y In a
            AConcat = AConcat & Y & Sep
        Next Y
    Else
        AConcat = AConcat & a & Sep
    End If

    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))

End Function

Then enter the following formula in AA2, confirm with CONTROL+SHIFT+ENTER, and copy down:

=SUBSTITUTE(AConcat(IF(LEFT(F2:Y2,3)="Unk",", "&$F$1:$Y$1,"")),", ","",1)

edited Jul 22, 2016 at 14:12

answered Jul 21, 2016 at 23:51

user6585761

Sign up to request clarification or add additional context in comments.

2 Comments

elagarde Over a year ago

That worked awesome. I'm just noticing that there are some values only as "Unk" and I apologize for not seeing that earlier. Is there an easy correction to do so it would pick up anything with "Unk". There are a couple of fields that were filled in by the user so there could be misspellings and what not so it wouldn't just be "Unk" or "Unknown". I tried adding a wildcard in the Array but it didn't work. Any suggestions?

user6585761 Over a year ago

Okay, I've edited the formula so that it now searches for cells that start with "Unk".

Collectives™ on Stack Overflow

Excel: Find value across multiple columns and return header column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related