Search code examples
excelvbaif-statementexcel-formula

Check cells in table for value "x" and transfer each in new table

I have a big dataset which I'm showing here simplified:
enter image description here

I need to check the cells in the columns C to E whether they have an "x" value.
If they do, I need to transfer them in a second table, as well as their corresponding value in the columns A and B.

The outcome should look like this:
enter image description here

I tried If-functions as well as INDEX-functions and VBA coding.

My problem is, that if you got more than one "x" value in a line, I don't know how to return both the first and the second cells as well as for both cells their corresponding values in A and B.

Solution

  • Since OP has tagged Excel and if one is using MS365 then can use the following formula:

    enter image description here

    =HSTACK(TOCOL(IFS(C2:E4="x",C1:E1),3),TEXTSPLIT(TEXTAFTER("|"&TOCOL(IFS(C2:E4="x",B2:B4&"|"&A2:A4),3),"|",{1,2}),"|"))

    Using LET() to make readability easier,

    =LET(
     α, TOCOL(IFS(C2:E4="x",C1:E1),3),
     φ, TOCOL(IFS(C2:E4="x",B2:B4&"|"&A2:A4),3),
     HSTACK(α,TEXTSPLIT(TEXTAFTER("|"&φ,"|",{1,2}),"|")))

    Note: Formulas shown above won't work with Structured References since it will give #SPILL! error.

    This can be accomplished quickly and easily using POWER QUERY. To achieve this using the said procedure, follow this simple steps using POWER QUERY window UI:

    • First convert the source ranges into a table and name it accordingly, for this example I have named it as Table1
    • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
    • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Value"}, "Attribute", "Value.1"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "Value", "Name", "Value.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Value.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Frame"}})
in
    #"Renamed Columns"

    enter image description here

    • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

    enter image description here