Forum Discussion
Find and concatenate random columns from second column to last used third column on every row
Hi all, I have a file with 100K+ records where values are in multiple columns randomly from Column A to AG Here is an example but not limited to the columns shown below. Any help to: 1. Ext...
Hello, take a look at my example document. I have implemented your wishes with:
=IFERROR(INDEX(A2:I2,LOOKUP(2,1/--(A2:I2<>""),COLUMN(A2:I2))-1),"")
=IFERROR(TEXTJOIN(" ",TRUE,INDEX(A2:I2,SEQUENCE(1,LOOKUP(2,1/--(A2:I2<>""),COLUMN(A2:I2))-3,2))),"")
I hope it fits so far. It doesn't always come out what you said it would. There is still a mistake somewhere.
A more modern version with XLOOKUP and XMATCH:
=XLOOKUP("*",B2:I2,A2:H2,"",2,-1)
=IFERROR(TEXTJOIN(" ",TRUE,INDEX(A2:I2,SEQUENCE(1,XMATCH("*",A2:I2,2,-1)-3,2))),"")
