Find and concatenate random columns from second column to last used third column on every row

[mickeymao]

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. Extract all second last column in every row and paste in another new column

e.g. 

102221 return "ACEWS"

102221245 return "HRRecruit"

2. Extract and concat values from columnB to last third column (need to find in every row e.g. ColumnG for RefID "102221245"). Copy and paste in another new column

102221245 return "Participant + Update Projects"

10222 return "Credits Frequently Ask Qns New Approach HSM"

Edit or View is always the last column in every row.

RefID	ColumnB	ColumnC	ColumnD	ColumnE	ColumnF	ColumnG	ColumnH	ColumnI
102221	text1	ACE	Delight	ACEWS	Edit
102221	text1	ACE	Delight	ACEWS	Edit
102221245	text2		Participant	+	Update	Projects	HRRecruit	Edit
102221245	text3		Participant	+	Update	Projects	HRRecruit	Edit
102221241	Budget	&	Planning	Development	ACP	View
102221241	Budget	&	Planning	Development	ACP	View
10222	Credits	Frequently	Ask	Qns	New	Approach	HSM	View
10222	Credits	Frequently	Ask	Qns	New	Approach	HSM	View

Help is greatly appreciated. It's taking me many hours to figure out.

[#2]

@mickeymao 

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))),"")

[#3]

@dscheikeymany thanks for your help.

I do see the formula having Name? issue after I tried applying the same.

Since it contains unsupported like --, _xlfn, Sequence in your formula.

However manage to get this working:

=TEXTJOIN(" ",,B2:(INDIRECT(ADDRESS(ROW(B2),COUNTA(A2:P2)-2))))