| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Matched Numeric Values across Rows
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Matched Numeric Values across Rows
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)),"",IND EX(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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sam via OfficeKB.com" <u4102@uwe wrote in message news:6bb14253b5fcc@uwe... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return Matched Numeric Values across Rows
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:$F40,$B1:$F4),COLUMNS( $A:A)),"") Final Results - Numeric Labels "0" to "3" 9, 25, 28 Thank you once again. Cheers, Sam Bernard Liengme wrote: 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)),"",IND EX(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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Numeric Labels that have Values =4 across Single Row | Excel Worksheet Functions | |||
| Find and Count Frequency of Numeric Value in Non-Contiguous Rows | Excel Worksheet Functions | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| AVERAGE Row of Numbers and Return Corresponding Numeric Label | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
