two column search

[thephoenix12]

Hey,

I'm looking for a way to search through two columns, the first colum
contains cells that are either blank or contain numbers in them, an
the second column contains text. I want to have a search that look
through the first row, and for every cell that isn't blank, it display
the text to the right of the cell. I guess the best option would be t
use VBA to do this, but I dont know much about it right now. Any hel
would be very useful!

-Stev

 

[anilsolipuram]

Can you explain with an exampl

 

[Max]

... looks through the first row, and for every cell that isn't blank,

it displays the text to the right of the cell...

Assuming you mean look through the first *column* (typo?), and the first and
second cols are cols A and B, with data from row1 down,
you could put in C1 and copy down:
=IF(TRIM(A1)<>"",B1,"")

Col C should return as required.
--
Rgds
Max
xl 97

 

[Dave Peterson]

Maybe just selecting the two columns and doing Data|Filter|Autofilter would work
for you.

You could filter on the first column and show only the non-blank cells.

When you're done, you can use Data|Filter|Showall to see everything.

 

[Guest]

Hi Max,
This formula is what I am looking for too. It works, but, column C has
blank cells in it. Is there a way to get column C to not have blank cells.
To have it report only the cells from column A that have a number?
Your help is greatly appreciated,
Terri

... looks through the first row, and for every cell that isn't blank,
it displays the text to the right of the cell...

Assuming you mean look through the first *column* (typo?), and the first and
second cols are cols A and B, with data from row1 down,
you could put in C1 and copy down:
=IF(TRIM(A1)<>"",B1,"")

Col C should return as required.
--
Rgds
Max
xl 97

 

[Max]

... Is there a way to get column C to not have blank cells.
To have it report only the cells from column A
that have a number?

If I've read your situation correctly, here's one non-array formulas play
you could try. Assuming you have in col A, from A1 down numbers (or
whatever) with blanks in-between, e.g.:

23

12

11
13
12

45
etc

Put in B1: =IF(TRIM(A1)="","",ROW())
Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

Select B1:C1 and copy down until the last row of data in col A
Col C will return all the numbers from col A neatly bunched at the top, with
blanks thrown below. E.g. for the sample data above:

23
12
11
13
12
45
(blank rows below)

 

[Max]

Should col A contain a mixture of numbers, text and blanks,
for which you want only the numbers to be returned,
e.g. col A contains:

23
text1
12

11
13
12
text2
45
etc

Just change the formula in B1 to:
=IF(TRIM(A1)="","",IF(ISNUMBER(A1),ROW(),""))

(No change to C1's formula)

Select B1:C1, and copy down as before

This will yield the "same" results in col C, i.e.:

23
12
11
13
12
45
(blank rows below)

 

[Guest]

Hi Max, the formula doesn't work. Column B's text disapears when I enter the
formula. The answer I get in Column B is the 'row number'. Column C only
displays the actual formula i just put in it. I will try to explain it a
little better.

Column A is either 'blank' or has a '1' in it.
Column B is an object name
In column C - I need...for every '1' in column A, I need the "Object Name"
to the right of it (column B) to be displayed in Column C neatly bunched at
the top.
Thanks for you help,
Terri

 

[Max]

Column A is either 'blank' or has a '1' in it.

Column B is an object name
In column C - I need...for every '1' in column A,
I need the "Object Name" to the right of it (column B)
to be displayed in Column C
neatly bunched at the top.

Ok, let's use 2 empty cols to the right, say, cols C and D

Put in D1: =IF(A1=1,ROW(),"")

Put in C1:

=IF(ISERROR(SMALL(D,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(D,ROWS($A$1
:A1)),D,0)))

Select C11, copy down until the last row of data in col A

Col C should now return what you want from col B ..

 

[Guest]

woo hoo..absolutely perfect. Thanks a bunch Max! All you guys do a great
job of helping us excel dummies.
Terri

 

[Max]

Glad it worked for you !
Thanks for the feedback ..