two column search

  • Thread starter Thread starter thephoenix12
  • Start date Start date
T

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
 
... looks through the first row, and for every cell that isn't blank,
it displays the text to the right of the cell...
Click to expand...

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
 
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.
 
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

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

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
Click to expand...
 
Terri said:
... 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?
Click to expand...

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)
 
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)
 
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
 
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.
Click to expand...

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:D,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(D:D,ROWS($A$1
:A1)),D:D,0)))

Select C1:D1, copy down until the last row of data in col A

Col C should now return what you want from col B ..
 
woo hoo..absolutely perfect. Thanks a bunch Max! All you guys do a great
job of helping us excel dummies.
Terri
 
Back
Top