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
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1