CODEWITHSUNDEEP
excelexcel-formulavba
elagarde
elagarde

Reputation: 13

Excel: Find value across multiple columns and return header column

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

Upvotes: 1

Views: 2179

Answers (1)

Domenic
Domenic

Reputation: 8124

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)

Upvotes: 0

Related Questions