plb2862 said:
I have a sheet with (6) columns and (306) rows. I would like to
search for empty cells in column (6) and return the values in
columns {2, 3, 4 & 5}. And continue until the last row 308 is
processed.
For Example:
G307 is empty returns
B307 2643F
C307 pg 2 of 3
D307 Orange
E307 Friday
= 2643F pg 2 of 3 Orange Friday
The empty Cell is
G308
B308 2643F
C308 pg 3 of 3
D308 Orange
E308 Friday
= 2643F pg 3 of 3 Orange Friday
Unclear whether you want the col 2-5 values concatenated into the same
cell or returned in separate columns. Easy enough to show both.
If you want col 2-5 values returned in separate columns, the easiest
way would be to select your entire data table, run the menu command
Data > Filter > AutoFilter to set an autofilter on your table, then
use the drop-down button in col G to select (Blanks) from the
autofilter drop-down list. That should result in Excel displaying all
rows in your data table in which col G is blank. Select cols B-E in
the filtered rows, copy, and paste into a different worksheet.
If you want to do this with formulas and concatenate the col 2-5
values into a single result cell for each row, and if your data table
were in A2:G1000 and the topmost return cell were X2, try these array
formulas. [To enter array formulas you need to hold down [Ctrl] and
[Shift] keys before pressing [Enter].]
X2:
=INDEX($B$2:$B$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))
&INDEX($C$2:$C$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))
&INDEX($D$2:$D$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))
&INDEX($E$2:$E$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))
Fill X2 down as far as needed. If you want to include spaces between
each column's values, use
X2:
=INDEX($B$2:$B$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))&" "
&INDEX($C$2:$C$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))&" "
&INDEX($D$2:$D$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))&" "
&INDEX($E$2:$E$1000,MATCH(TRUE,ISBLANK($G$2:$G$1000),0))
