Remove Blank Cells in table

[JTL9161]

I inquired before about something similar to this but this is a different spreadsheet.

I have a large table (about 60 rows) but I will base my example on just 1 row then I will replicate the formula throughout.

The large table on worksheet B has reference code in cell A1 (ABC) then different results are in B1, C1 & D1.

On worksheet A I do a 3 Vlookup's for the ref. code (ABC) in cell D1, E1, & F1 bringing over the results on wrksht B B1,C1 & D1. BUT 1 or more of those cells could be blank so for example B1 contains 123, C1 is blank and D1 contains 789 which would translate over to D1 (123) E1 (blank) & F1 (789) on Wrksheet A.

I am looking for a formula (index?) that would remove the blank E1 cell on worksheet A and fill it with the data from D1 of wrksht b so the end result would be D1 (123), E1 (789) & F1 (blank). Basically remove the blank cells from the table and move all the data to the left not showing any blanks on worksheet A.

Hope I explained this good enough.

Thank you,
James

 

[hernantorres23]

Hi, would be wonderful for all, if you try prepare a data example using add-in specifically for this and it can be found here XL2BB
If would necesary, pay attention to this post XL2BB 2 Square

 

[BenMiller]

If you're only having three columns being returned, why not keep it simple and do something like =IF(ISBLANK(VLOOKUP(Sheet2!A1:D1,3,0)),VLOOKUP(Sheet2!A1:D1,4,0),VLOOKUP(Sheet2!A1:D1,3,0))

 

[BenMiller]

So in D1 you would do two nested IFs and in E1 you would do one.