Remove blank cells in a Row

[JTL9161]

I have a row of data columns "B2" through "I2".
Not every cell in the in the row has data.

How would I show the same row elsewhere on the spreadsheet minus the blank cells.
So if cell B2-C2-E2-G2-I2 have data and D2-F2-H2 do not what is the (IFERROR(INDEX?) formula I need so only the B2-C2-E2-G2-I2 data show?


Thank you for your assistance.
James

 

[Fluff]

How about
=IFERROR(INDEX($B2:$I2,AGGREGATE(15,6,(COLUMN($B2:$I2)-COLUMN($B2)+1)/($B2:$I2<>""),COLUMNS($L2:L2))),"")
Change the references to L to reflect the column with the formula

 

[Finalfight40]

Maybe like this as an array which got entered into M2:

=IFERROR(INDEX($B$2:$I$2, SMALL(IF($B$2:$I$2<>"", COLUMN($B$2:$I$2)-COLUMN($B$2)+1, ""), COLUMNS($M$2:M2))), "")

 

[JTL9161]

Not sure if these are getting posted.

Need a VLOOKUP at the beginning of the above formula to read (for this example) cell L2 for data then look for that same data in cell a2...a10 then show the line it finds the data from columns B through I without blanks.

 

[Fluff]

That is completely different from your original question.
Can you please post some sample data showing what you have & what you are trying to get.

 

[JTL9161]

Sorry, put the cart in front of the horse.

Cells A2 through A10 have codes (for this example) A2=ABC, A3=XYZ, A4=LMN with then the columns B through I with data but missing in random columns

for this each row. What I want to do is use the code above to remove the blank cells (which it does) but at the other spot on the spreadsheet Cell M2,M3,M4 I have the same ABC, XYZ, LMN. So I need a (Index) to look at cell M2 and then find that same name in the A2 through A10 cells then put the data across the B through I columns with no blanks.


A B C D E F G
1)ABC 555 666 777 888
2)XYZ 444 999 222

M2= ABC (so cells N2, O2, P2 & Q2) would look like below:

M2 N2 O2 P2 Q2
ABC 555 666 777 888


Hope this formats ok so you can understand
Thanks

 

[Fluff]

Ok, how about
=IFERROR(INDEX($B$2:$I$10,MATCH($M2,$A$2:$A$10,0),AGGREGATE(15,6,(COLUMN($B2:$I2)-COLUMN($B2)+1)/(INDEX($B$2:$I$10,MATCH($M2,$A$2:$A$10,0),0)<>""),COLUMNS($N2:N2))),"")

 

[JTL9161]

Thanks. Works perfect!

 

[Fluff]

You're welcome & thanks for the feedback