Search multiple columns and display corresponding cells in a list?!?
 

I would like to search 15 columns on the same sheet for cells containing
numbers. For the searched columns that contain numbers, on a seperate
sheet display the value found in a list in one column and the data from
column A in another column.


--
far2rare
------------------------------------------------------------------------
far2rare's Profile: http://www.excelforum.com/member.php...o&userid=35933
View this thread: http://www.excelforum.com/showthread...hreadid=557260

Search multiple columns and display corresponding cells in a list?!?
 

Can you provide a sample of your data, which includes Column A and about
3 other corresponding columns, along with the expected results?

In article ,
far2rare
wrote:

I would like to search 15 columns on the same sheet for cells containing
numbers. For the searched columns that contain numbers, on a seperate
sheet display the value found in a list in one column and the data from
column A in another column.

Search multiple columns and display corresponding cells in a list?!?
 

I've uploaded a screenshot of the raw data and the desired results. The
top table is the raw data, the lower table shows what I need. Thanks
for your help!


+-------------------------------------------------------------------+
|Filename: sample.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4961 |
+-------------------------------------------------------------------+

--
far2rare
------------------------------------------------------------------------
far2rare's Profile: http://www.excelforum.com/member.php...o&userid=35933
View this thread: http://www.excelforum.com/showthread...hreadid=557260

Search multiple columns and display corresponding cells in a list?!?
 

Will there always be only one number for any row/set? Or you can you
have more than one number?

In article ,
far2rare
wrote:

I've uploaded a screenshot of the raw data and the desired results. The
top table is the raw data, the lower table shows what I need. Thanks
for your help!


+-------------------------------------------------------------------+
|Filename: sample.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4961 |
+-------------------------------------------------------------------+

Search multiple columns and display corresponding cells in a list?!?
 

There will only ever be one number for each row as shown, thanks!


--
far2rare
------------------------------------------------------------------------
far2rare's Profile: http://www.excelforum.com/member.php...o&userid=35933
View this thread: http://www.excelforum.com/showthread...hreadid=557260

Search multiple columns and display corresponding cells in a list?!?
 

Try...

A29, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS($A$29:A29)<=COUNT($C$5:$E$20),INDEX($A$5: $A$20,SMALL(IF($C$5:$E$
20<"",ROW($C$5:$E$20)-ROW($C$5)+1),ROWS($A$29:A29))),"")

B29, confirmed with just ENTER, and copied down:

=IF(A29<"",SUM(INDEX($C$5:$E$20,MATCH(A29,$A$5:$A $20,0),0)),"")

Hope this helps!

In article ,
far2rare
wrote:

There will only ever be one number for each row as shown, thanks!

Search multiple columns and display corresponding cells in a list?!?
 

Thanks! Great it works really well. Only one point though, I have
ommitted the columns that appear between C and D, and between D and E
to make the data easier to read. How could I modify your formulae to
pick just the Cycle columns from all the data? Thanks!


--
far2rare
------------------------------------------------------------------------
far2rare's Profile: http://www.excelforum.com/member.php...o&userid=35933
View this thread: http://www.excelforum.com/showthread...hreadid=557260

Search multiple columns and display corresponding cells in a list?!?
 

How many columns are there between the target columns? What type of
values do they contain, text or numerical?

In article ,
far2rare
wrote:

Thanks! Great it works really well. Only one point though, I have
ommitted the columns that appear between C and D, and between D and E
to make the data easier to read. How could I modify your formulae to
pick just the Cycle columns from all the data? Thanks!

Search multiple columns and display corresponding cells in a list?!?
 

there are 8 columns between the target columns, some contain dates,
others contain text. Thanks!


--
far2rare
------------------------------------------------------------------------
far2rare's Profile: http://www.excelforum.com/member.php...o&userid=35933
View this thread: http://www.excelforum.com/showthread...hreadid=557260

Search multiple columns and display corresponding cells in a list?!?
 

Assumptions:

A5:A20 contains Set 1, Set 2, Set 3, etc.

Columns C, L, and U are the target columns

There will only be one number from the target columns for each row

Formulas:

A27:

=SUM(IF(MOD(COLUMN($C$5:$U$20)-COLUMN($C$5),9)=0,IF($C$5:$U$20<"",1)))

....confirmed with CONTROL+SHIFT+ENTER.

A29, copied down:

=IF(ROWS($A$29:A29)<=$A$27,INDEX($A$5:$A$20,SMALL( IF(MOD(COLUMN($C$5:$U$2
0)-COLUMN($C$5),9)=0,IF($C$5:$U$20<"",ROW($C$5:$U$20 )-ROW($C$5)+1)),ROWS
($A$29:A29))),"")

....confirmed with CONTROL+SHIFT+ENTER

B29, copied down:

=IF(A29<"",INDEX($C$5:$U$20,MATCH(A29,$A$5:$A$20, 0),MATCH(1,IF(MOD(COLUM
N($C$5:$U$20)-COLUMN($C$5),9)=0,IF(INDEX($C$5:$U$20,MATCH(A29,$A $5:$A$20,
0),0)<"",1)),0)),"")

....confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges accordingly.

Hope this helps!

In article ,
far2rare
wrote:

there are 8 columns between the target columns, some contain dates,
others contain text. Thanks!