Return Matched Numeric Values across Rows

[Sam via OfficeKB.com]

Hi All,

Happy New Year.

I have two Worksheets, Sheet1 and Sheet2 within the same Workbook using the
same data layout.

Each of the Worksheets contains a column of Numeric Labels from 0-25 spanning
B61:B86 and 20 columns of Numeric Values spanning 26 rows C61:V86

Numeric Label "0" spans C61:V61 contains Numeric Values
Numeric Label "1" spans C62:V62 contains Numeric Values
Numeric Label "2" spans C63:V63 contains Numeric Values
Numeric Label "3" spans C64:V64 contains Numeric Values
Numeric Label "4" spans C65:V65 contains Numeric Values
Numeric Label "5" spans C66:V66 contains Numeric Values
Numeric Label "6" spans C67:V67 contains Numeric Values
Numeric Label "25" spans C86:V86 contains Numeric Values

Scenario:
MATCH Numeric Label on Sheet1 with Numeric Label on Sheet2 and Return MATCH
of Numeric Values found on each Sheet corresponding to the same Numeric Label
on Sheet1 and Sheet2.

The individual MATCHES for Numeric Labels "0" to "3" should be returned
together across a single row in ascending order.
MATCHES for Numeric Label "4" should be returned across a single row in
ascending order.
MATCHES for Numeric Labes "5" to "25" should be returned together across a
single row in ascending order.

Example:
1. If any of Sheet1 Numeric Label "0" Numeric Values MATCH with Sheet2
Numeric Label "0" Numeric Values then Return Numeric Value.
2. If any of Sheet1 Numeric Label "1" Numeric Values MATCH with Sheet2
Numeric Label "1" Numeric Values then Return Numeric Value.
3. If any of Sheet1 Numeric Label "2" Numeric Values MATCH with Sheet2
Numeric Label "2" Numeric Values then Return Numeric Value.
4. If any of Sheet1 Numeric Label "3" Numeric Values MATCH with Sheet2
Numeric Label "3" Numeric Values then Return Numeric Value.
5. If any of Sheet1 Numeric Label "4" Numeric Values MATCH with Sheet2
Numeric Label "4" Numeric Values then Return Numeric Value.
6. If any of Sheet1 Numeric Label "5" Numeric Values MATCH with Sheet2
Numeric Label "5" Numeric Values then Return Numeric Value.
7. If any of Sheet1 Numeric Label "6" Numeric Values MATCH with Sheet2
Numeric Label "6" Numeric Values then Return Numeric Value.
8. If any of Sheet1 Numeric Label "7" Numeric Values MATCH with Sheet2
Numeric Label "7" Numeric Values then Return Numeric Value.
9. If any of Sheet1 Numeric Label "8" Numeric Values MATCH with Sheet2
Numeric Label "8" Numeric Values then Return Numeric Value.
10. If any of Sheet1 Numeric Label "9" Numeric Values MATCH with Sheet2
Numeric Label "9" Numeric Values then Return Numeric Value.
11. If any of Sheet1 Numeric Label "10" Numeric Values MATCH with Sheet2
Numeric Label "10" Numeric Values then Return Numeric Value.
etc
12. If any of Sheet1 Numeric Label "25" Numeric Values MATCH with Sheet2
Numeric Label "25" Numeric Values then Return Numeric Value.

NB. I've used a leading zero with single-digit Numeric Values for alignment
purposes only.

Sample Data Layout: Sheet1
Labels Numeric Values
0
1 01 47
2 16 25 28 36
3 06 07 09 11 21
4 02 03 04 08 15
5 26 31 34 38 44
6 05 12 18 19 20


Sample Data Layout: Sheet2
Labels Numeric Values
0 01 30 36
1 10 13 32 35 44
2 05 07 11 25 28
3 03 09 22 23 27
4 15 17 18 33 43
5 02 08 19 24 26
6 14 21 37


Expected Results:
Return Numeric Values across a single row (per Numeric Labels "0" to "3")
9, 25, 28
Numeric Value 9 MATCHES to Sheet1 and Sheet2 Numeric Label "3"
Numeric Value 25 MATCHES to Sheet1 and Sheet2 Numeric Label "2"
Numeric Value 28 MATCHES to Sheet1 and Sheet2 Numeric Label "2"

Return Numeric Values across a single row (per Numeric Labels "4")
15

Return Numeric Values across a single row (per Numeric Labels "5" to "25")
26


Thanks
Sam

 

[Bernard Liengme]

Here is a first-approximation to an answer.
I have put you two blocks of data in A1:F7 of Sheet1 and Sheet2
In Sheet3 I have in A the labels 0,,1,2,3,4,5, 6
In B1 I have the formula
=IF(ISNA(MATCH(Sheet1!B1,Sheet2!$B1:$F1,0)),"",INDEX(Sheet2!$B1:$F1,1,MATCH(Sheet1!B1,Sheet2!$B1:$F1,0)))
This I copied down and across to fill B2:F7
The result is
0 -- -- -- -- --
1 -- -- -- -- --
2 -- 25 28 -- --
3 -- -- 9 -- --
4 -- -- -- -- 15
5 26 -- -- -- --
6 -- -- -- -- --
where -- means a cell displaying as blank
best wishes

 

[Sam via OfficeKB.com]

Hi Bernard,

Thank you very much for your time and assistance. Your Solution and Formula
provides me with a very useful summary of my "Expected Results" - Numeric
Values that MATCH the same Numeric Label on both Sheet1 and Sheet2. Great!

I then used this Array Formula provided by Biff to display the Numeric Values
grouped by their Numeric Labels ("0" to "3") across a single row in ascending
order:

=IF(COLUMNS($A:A)<=COUNTIF($B1:$F4,">0"),SMALL(IF($B1:$F4>0,$B1:$F4),COLUMNS(
$A:A)),"")

Final Results - Numeric Labels "0" to "3"
9, 25, 28

Thank you once again.

Cheers,
Sam

Here is a first-approximation to an answer.

I have put you two blocks of data in A1:F7 of Sheet1 and Sheet2
In Sheet3 I have in A the labels 0,,1,2,3,4,5, 6
In B1 I have the formula
=IF(ISNA(MATCH(Sheet1!B1,Sheet2!$B1:$F1,0)),"",INDEX(Sheet2!$B1:$F1,1,MATCH
(Sheet1!B1,Sheet2!$B1:$F1,0)))
This I copied down and across to fill B2:F7
The result is
0 -- -- -- -- --
1 -- -- -- -- --
2 -- 25 28 -- --
3 -- -- 9 -- --
4 -- -- -- -- 15
5 26 -- -- -- --
6 -- -- -- -- --
where -- means a cell displaying as blank