Search for & return list of values
 

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

Is there a non VBE/VBA solution?

TIA,
Phil

Search for & return list of values
 

Would you consider using an Advanced Filter?

Try this example:

A5:E9 contains these values
Ref......Field1...Field2.....Field3..Field4
(blank)..2643F....pg 2 of 3..Orange..Friday
abc......1111a....pg 1 of 3..Red.....Monday
(blank)..2643F....pg 3 of 3..Orange..Friday
abc......1111a....pg 2 of 3..Red.....Monday

A1: Ref
A2: <*

H1:K1 contains these column headings
Field1...Field2...Field3...Field4

Select A5:E9
Then....from the Excel main menu
<data<filter<advanced filter
Check: Copy to another location
List Range: $A$5:$E$9
Criteria: $A$1:$A$2
Copy to: $H$1:$K$1
Click the [OK]

In the example, this table is returned
Field1...Field2.....Field3..Field4
2643F....pg 2 of 3..Orange..Friday
2643F....pg 3 of 3..Orange..Friday

Note: With some minor tweaking, you can use that technique to pull the
values to another sheet in the same workbook. Post back if you have
questions.

Is that something you can work with?

----------------------------
Regards,

Ron
Microsoft MVP - Excel

"plb2862" wrote in message
...
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

Is there a non VBE/VBA solution?

TIA,
Phil

Search for & return list of values
 

"plb2862" wrote...
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$100 0),0))
&INDEX($C$2:$C$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))
&INDEX($D$2:$D$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))
&INDEX($E$2:$E$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),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$100 0),0))&" "
&INDEX($C$2:$C$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))&" "
&INDEX($D$2:$D$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))&" "
&INDEX($E$2:$E$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))

Search for & return list of values
 

"Harlan Grove" wrote in message
ups.com...

X2:
=INDEX($B$2:$B$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))
&INDEX($C$2:$C$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))
&INDEX($D$2:$D$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))
&INDEX($E$2:$E$1000,MATCH(TRUE,ISBLANK($G$2:$G$100 0),0))


Harlan,

Am I missing something or doing something wrong?

I get the contents of the first row with a blank all the way down the
column.

If I replace the $2 in both the INDEX and ISBLANK functions with just 2 I
get the contents of the first row with a blank until that row and then the
contents of the next row with a blank until there are not rows with blanks
left, whereupon I get no return at all which surprises me even more!

Im using XL97 but I can'y see how it can be any different using absolute
ranges.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Search for & return list of values
 

"Sandy Mann" wrote...
"Harlan Grove" wrote in message
X2:
=INDEX($B$2:$B$1000,MATCH(TRUE,ISBLANK($G$2:$G$1 000),0))
&INDEX($C$2:$C$1000,MATCH(TRUE,ISBLANK($G$2:$G$1 000),0))
&INDEX($D$2:$D$1000,MATCH(TRUE,ISBLANK($G$2:$G$1 000),0))
&INDEX($E$2:$E$1000,MATCH(TRUE,ISBLANK($G$2:$G$1 000),0))

Am I missing something or doing something wrong?
....

Nope. I screwed up. The X3 array formula should be

X3:
=INDEX($B:$B,SMALL(IF(ISBLANK($G$2:$G$1000),ROW($G $2:$G$1000)),
ROWS(X$2:X3)))&INDEX($C:$C,SMALL(IF(ISBLANK($G$2:$ G$1000),
ROW($G$2:$G$1000)),ROWS(X$2:X3)))&INDEX($D:$D,
SMALL(IF(ISBLANK($G$2:$G$1000),ROW($G$2:$G$1000)), ROWS(X$2:X3)))
&INDEX($E:$E,SMALL(IF(ISBLANK($G$2:$G$1000),ROW($G $2:$G$1000)),
ROWS(X$2:X3)))

and fill X3 down as far as needed. Better to use 2 columns.

X2 [array formula]:
=SMALL(IF(ISBLANK($G$2:$G$1000),ROW($G$2:$G$1000)) ,ROWS(H$2:H2))

Y2:
=INDEX($B:$B,X2)&INDEX($C:$C,X2)&INDEX($D:$D,X2)&I NDEX($E:$E,X2)

Select X2:Y2 and fill down as far as needed.

Search for & return list of values
 

"plb2862" wrote in message
...
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

Is there a non VBE/VBA solution?

TIA,
Phil
I forgot to say I'm using Office xp - Excel 2002: which is why the auto
filter doesn't ring a bell for me. Is that Excel 2003 or 2007?

Search for & return list of values
 

"Harlan Grove" wrote in message
oups.com...

X3:
=INDEX($B:$B,SMALL(IF(ISBLANK($G$2:$G$1000),ROW($G $2:$G$1000)),
ROWS(X$2:X3)))&INDEX($C:$C,SMALL(IF(ISBLANK($G$2:$ G$1000),
ROW($G$2:$G$1000)),ROWS(X$2:X3)))&INDEX($D:$D,
SMALL(IF(ISBLANK($G$2:$G$1000),ROW($G$2:$G$1000)), ROWS(X$2:X3)))
&INDEX($E:$E,SMALL(IF(ISBLANK($G$2:$G$1000),ROW($G $2:$G$1000)),
ROWS(X$2:X3)))

Thank you Harlan,

I was surprised to see working whole column references in an array formula.
I have only ever seen people saying that "you can't have whole columns in an
array formula"

mmmmm........
--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Search for & return list of values
 

"PBalmanno" wrote in message
...
I forgot to say I'm using Office xp - Excel 2002: which is why the auto
filter doesn't ring a bell for me. Is that Excel 2003 or 2007?

Data Filter AutoFilter

All version since XL95 at least

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Search for & return list of values
 

"Sandy Mann" wrote...
....
I was surprised to see working whole column references in an array
formula. I have only ever seen people saying that "you can't have
whole columns in an array formula"
....

If you access entire columns as RANGEs, no problem. If you access them
as values, they fail. For example,

=COUNTIF(A:A,{1,2,3,4})

happily returns a 4-entry array, but

=SUMPRODUCT(--(A:A={1,2,3,4}))

returns #NUM!.

Not necessarily exhaustive:
-arguments to the aggregating functions (i.e., the ones that accept 3D
references) except for the 1st argument to NPV;
-1st arg to SUMIF and COUNTIF and 3rd arg to SUMIF;
-1st arg to INDEX;
-2nd arg to MATCH, HLOOKUP and VLOOKUP.

As far as I can tell, these are the only instances in which Excel
treats ranges differently than arrays. It's when Excel treats ranges
the SAME as arrays that you can't use entire column ranges.

Search for & return list of values
 

"PBalmanno" wrote...
....
I forgot to say I'm using Office xp - Excel 2002: which is why the
auto filter doesn't ring a bell for me. Is that Excel 2003 or 2007?

Excel 97 at the latest, and it may have been part of Excel 5. Just try
running the menu command Data Filter AutoFilter. Unless you've
modified your menu bar, it'll be there.

Search for & return list of values
 

"plb2862" wrote in message
...
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

Is there a non VBE/VBA solution?

TIA,
Phil
Thanks everyone.

Search for & return list of values
 

"Harlan Grove" wrote in message
ps.com...

If you access entire columns as RANGEs, no problem. If you access them
as values, they fail. For example,
<snip

Thank you for the information Harlan. I use OE so I miss a few posts from
time to time so I must have missed it. When is that you are holding your
Master class?

<g
--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Search for & return list of values
 

= 2643F pg 2 of 3 Orange Friday

The easiest way to do this would be use Autofilter and filter on column 6 =
blank

Here's a formula to do this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(ROWS($1:1)<=COUNTBLANK($G$1:$G$10),INDEX(B$1:B $10,SMALL(IF($G$1:$G$10="",ROW(B$1:B$10)-MIN(ROW(B$1:B$10))+1),ROWS($1:1))),"")

Copy across 4 cells then down until you get blanks

Biff

"plb2862" wrote:

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

Is there a non VBE/VBA solution?

TIA,
Phil