| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 | |||
Hybrid Mode
