two column search
 

Hey,

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

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380061

Can you explain with an example


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380061

... 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
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"thephoenix12"
wrote in message
news:thephoenix12.1qrxib_1119017105.2233@excelforu m-nospam.com...

Hey,

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

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380061

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.

thephoenix12 wrote:

Hey,

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

-Steve

--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380061

--

Dave Peterson

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" wrote:

... 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
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"thephoenix12"
wrote in message
news:thephoenix12.1qrxib_1119017105.2233@excelforu m-nospam.com...

Hey,

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

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=380061

"Terri" wrote:
... 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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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" wrote:

"Terri" wrote:
... 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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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: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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

"Max" wrote:

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: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 ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Glad it worked for you !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Terri" <robelg(at)mts(dot)net wrote in message
...
woo hoo..absolutely perfect. Thanks a bunch Max!
All you guys do a great job of helping us excel dummies.
Terri