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

[far2rare]

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.

 

[Domenic]

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

 

[far2rare]

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

 

[Domenic]

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

 

[far2rare]

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

 

[Domenic]

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!

 

[far2rare]

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

 

[Domenic]

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

 

[far2rare]

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

 

[Domenic]

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!