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 ;)