Google sheets get first occurrence from duplicate column headers-CodePudding

I need to get the first occurrence from duplicate column headers as follows:

A workaround was to group all the same columns and use an IF blank then get next column formula. However, it seems not efficient if a column was added in between or the columns were re-arranged in some way. So the quickest way I could think of is to just get the first occurrence from a range that's equal to the column header.

CodePudding user response：

first column:

=INDEX(FILTER(A1:E5, A1:E1="name"),,1)

first non empty:

=INDEX(IFERROR(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(
 FILTER(A1:E5, A1:E1="name")),,9^9))), " ")),,1)

whole table:

={INDEX(IFERROR(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(FILTER(A1:E5, A1:E1="name")),,9^9))), " ")),,1), B1:B5, 
  INDEX(IFERROR(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(FILTER(A1:E5, A1:E1="email")),,9^9))), " ")),,1)}

update:

=INDEX(LAMBDA(a, h, x, {
 INDEX(IFERROR(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(FILTER(a, h="name")),,9^9))), " ")),,1), x, 
 INDEX(IFERROR(SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(FILTER(a, h="email")),,9^9))), " ")),,1)})
 (SUBSTITUTE(A1:E10, " ", CHAR(9)), A1:E1, B1:B10))

CodePudding user response：

I see you already accepted an answer, but just in case you or anyone needs this with more columns, you could use this formula:

=query({A1:Z},"Select Col"&JOIN(", Col",byrow(filter(unique(transpose(A1:1)),unique(transpose(A1:1))<>""),lambda(each,match(each,A1:1,0)))),true)

This will find first appearance of all unique headers and make the query ;)