Search code examples
excelexcel-formulaexcel-2019

Conditional Transform in excel

How can I transform a row in excel based on condition using formula. Like here three column A, B, C. If a column contain yes then the name of the header add to the activities column in a new row. Here example

Before:

name    | A     | B       | C
name1   | yes   | yes     | no
name2   | no    | yes     | yes
name3   | yes   | no      | no

After:

name    | Activities
name1   | A
        | B
name2   | B
        | C
name3   | A

now write formula

Solution

  • If you need it to include blanks in case of repeated names: =LET(y,B2:D4="yes",a,TOCOL(IFS(y,A2:A4),2),HSTACK(IF(XMATCH(a,a)=SEQUENCE(ROWS(a)),a,""),TOCOL(IFS(y,B1:D1),2)))

    Where IFS is used to create an array returning the name of the row where the value in range B2:D4 equals text yes. Else it returns an error. Wrapped in TOCOL with argument 2 (skip error values) returns the flattened names of the array.

    Same logic is used for the header values.

    To not repeat the name values, we perform XMATCH on the calculated names array. This returns an array of the position the name is first found within the array. If that position equals the sequence of the rows of the array it returns the name, else a blank. Else: =LET(y,B2:D4="yes",HSTACK(TOCOL(IFS(y,A2:A4),2),TOCOL(IFS(y,B1:D1),2)))

    enter image description here

    Edit to include a solution for older Excel, which isn't as straightforward as in Office 365:

    For the names: =IFERROR(TEXTJOIN(,,IF(AGGREGATE(15,6,(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1/ISNUMBER(LEN(IF($B$2:$D$4="yes",$A$2:$A$4,NA()))),ROW(A1))=(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1,IF($B$2:$D$4="yes",$A$2:$A$4,NA()),"")),"")

    For the header titles: =IFERROR(TEXTJOIN(,,IF(AGGREGATE(15,6,(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1/ISNUMBER(LEN(IF($B$2:$D$4="yes",$B$1:$D$1,NA()))),ROW(A1))=(ROW($B$2:$D$4)-ROW($B$2))*COLUMNS($B$2:$D$4)+COLUMN($B$2:$D$4)-COLUMN($B$2)+1,IF($B$2:$D$4="yes",$B$1:$D$1,NA()),"")),"")

    I think both need being entered using ctrl+shift+enter (array formula).