| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Multiple Matches
I have an worksheet that has data that is sorted like the following
Bar NumberProperty Node Orientation OD ID Wall 1 1622 3 5 16 13.25 1.375 2 1622 4 5 16 13.25 1.375 85 1212 5 6 12.75 11.25 0.75 86 1212 6 5 12.75 11.25 0.75 43 1628 7 8 16 12.5 1.75 44 1628 8 7 16 12.5 1.75 3 1622 9 5 16 13.25 1.375 1 1622 9 5 16 13.25 1.375 241 808 9 3 8.625 7.625 0.5 141 805 9 5 8.625 7.981 0.322 4 1622 10 5 16 13.25 1.375 2 1622 10 5 16 13.25 1.375 242 808 10 4 8.625 7.625 0.5 142 805 10 5 8.625 7.981 0.322 87 1212 11 6 12.75 11.25 0.75 85 1212 11 6 12.75 11.25 0.75 101 808 11 24 8.625 7.625 0.5 311 805 11 5 8.625 7.981 0.322 141 805 11 5 8.625 7.981 0.322 301 604 11 5 6.625 6.065 0.28 88 1212 12 5 12.75 11.25 0.75 86 1212 12 5 12.75 11.25 0.75 102 808 12 23 8.625 7.625 0.5 312 805 12 6 8.625 7.981 0.322 142 805 12 5 8.625 7.981 0.322 302 604 12 6 6.625 6.065 0.28 And i would like a function or something to use the match feature to return Data like Node Bar1 Bar2 Bar3 Bar4 Bar5 Bar6 Bar7 3 1 4 2 5 85 6 86 7 43 8 44 9 3 1 241 141 10 4 2 242 142 11 87 85 101 311 141 301 12 88 86 102 312 142 302 And so on, how can i do this? when i do a match, it only returns the first value, same with VLOOKUP. Any advice or code would be appreciated... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning Multiple Matches
Assume source data in Sheet1's cols A to G data from row2 down,
where the key cols are col A (Bar#) and col C (Node) Set this up in an adjacent area to the right In I2: =IF(C2="","",IF(COUNTIF(C$2:C2,C2)1,"",ROW())) Leave I2 blank In J2: =IF(ROWS($1:1)COUNT(I:I),"",INDEX(C:C,SMALL(I:I,R OWS($1:1)))) Select I2:J2 fill down to cover the max expected extent of source data In K2: =IF($C2="","",IF($C2=INDEX($J:$J,COLUMNS($A:A)+1), ROW(),"")) Copy K2 across as far as required, fill down Leave K1 across blank Then in another sheet, In A1 across are your headers: Node, Bar1, Bar2 ... In A2: =Sheet1!J2 In B2: =IF(ISERROR(SMALL(OFFSET(Sheet1!$J:$J,,MATCH($A2,S heet1!$J:$J,0)-1),COLUMNS($A:A))),"",INDEX(Sheet1!$A:$A,SMALL(OFF SET(Sheet1!$J:$J,,MATCH($A2,Sheet1!$J:$J,0)-1),COLUMNS($A:A)))) Copy B2 across as far as required, say to K2. Select A2:K2, fill down to cover the the max expected extent of source data in Sheet1. And that should return the exact results that you seek. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Adam Hodge" wrote: I have an worksheet that has data that is sorted like the following Bar NumberProperty Node Orientation OD ID Wall 1 1622 3 5 16 13.25 1.375 2 1622 4 5 16 13.25 1.375 85 1212 5 6 12.75 11.25 0.75 86 1212 6 5 12.75 11.25 0.75 43 1628 7 8 16 12.5 1.75 44 1628 8 7 16 12.5 1.75 3 1622 9 5 16 13.25 1.375 1 1622 9 5 16 13.25 1.375 241 808 9 3 8.625 7.625 0.5 141 805 9 5 8.625 7.981 0.322 4 1622 10 5 16 13.25 1.375 2 1622 10 5 16 13.25 1.375 242 808 10 4 8.625 7.625 0.5 142 805 10 5 8.625 7.981 0.322 87 1212 11 6 12.75 11.25 0.75 85 1212 11 6 12.75 11.25 0.75 101 808 11 24 8.625 7.625 0.5 311 805 11 5 8.625 7.981 0.322 141 805 11 5 8.625 7.981 0.322 301 604 11 5 6.625 6.065 0.28 88 1212 12 5 12.75 11.25 0.75 86 1212 12 5 12.75 11.25 0.75 102 808 12 23 8.625 7.625 0.5 312 805 12 6 8.625 7.981 0.322 142 805 12 5 8.625 7.981 0.322 302 604 12 6 6.625 6.065 0.28 And i would like a function or something to use the match feature to return Data like Node Bar1 Bar2 Bar3 Bar4 Bar5 Bar6 Bar7 3 1 4 2 5 85 6 86 7 43 8 44 9 3 1 241 141 10 4 2 242 142 11 87 85 101 311 141 301 12 88 86 102 312 142 302 And so on, how can i do this? when i do a match, it only returns the first value, same with VLOOKUP. Any advice or code would be appreciated... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning Multiple Matches
Here's an illustrative sample for the earlier suggestion:
http://www.freefilehosting.net/download/3hfgj Returning multiple matches transposed.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning Multiple Matches
Hi Max,
This does not appear to work when the list of "nodes" is longer, say closer to 2000. Any suggestions? /m "Max" wrote: Assume source data in Sheet1's cols A to G data from row2 down, where the key cols are col A (Bar#) and col C (Node) Set this up in an adjacent area to the right In I2: =IF(C2="","",IF(COUNTIF(C$2:C2,C2)1,"",ROW())) Leave I2 blank In J2: =IF(ROWS($1:1)COUNT(I:I),"",INDEX(C:C,SMALL(I:I,R OWS($1:1)))) Select I2:J2 fill down to cover the max expected extent of source data In K2: =IF($C2="","",IF($C2=INDEX($J:$J,COLUMNS($A:A)+1), ROW(),"")) Copy K2 across as far as required, fill down Leave K1 across blank Then in another sheet, In A1 across are your headers: Node, Bar1, Bar2 ... In A2: =Sheet1!J2 In B2: =IF(ISERROR(SMALL(OFFSET(Sheet1!$J:$J,,MATCH($A2,S heet1!$J:$J,0)-1),COLUMNS($A:A))),"",INDEX(Sheet1!$A:$A,SMALL(OFF SET(Sheet1!$J:$J,,MATCH($A2,Sheet1!$J:$J,0)-1),COLUMNS($A:A)))) Copy B2 across as far as required, say to K2. Select A2:K2, fill down to cover the the max expected extent of source data in Sheet1. And that should return the exact results that you seek. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Adam Hodge" wrote: I have an worksheet that has data that is sorted like the following Bar NumberProperty Node Orientation OD ID Wall 1 1622 3 5 16 13.25 1.375 2 1622 4 5 16 13.25 1.375 85 1212 5 6 12.75 11.25 0.75 86 1212 6 5 12.75 11.25 0.75 43 1628 7 8 16 12.5 1.75 44 1628 8 7 16 12.5 1.75 3 1622 9 5 16 13.25 1.375 1 1622 9 5 16 13.25 1.375 241 808 9 3 8.625 7.625 0.5 141 805 9 5 8.625 7.981 0.322 4 1622 10 5 16 13.25 1.375 2 1622 10 5 16 13.25 1.375 242 808 10 4 8.625 7.625 0.5 142 805 10 5 8.625 7.981 0.322 87 1212 11 6 12.75 11.25 0.75 85 1212 11 6 12.75 11.25 0.75 101 808 11 24 8.625 7.625 0.5 311 805 11 5 8.625 7.981 0.322 141 805 11 5 8.625 7.981 0.322 301 604 11 5 6.625 6.065 0.28 88 1212 12 5 12.75 11.25 0.75 86 1212 12 5 12.75 11.25 0.75 102 808 12 23 8.625 7.625 0.5 312 805 12 6 8.625 7.981 0.322 142 805 12 5 8.625 7.981 0.322 302 604 12 6 6.625 6.065 0.28 And i would like a function or something to use the match feature to return Data like Node Bar1 Bar2 Bar3 Bar4 Bar5 Bar6 Bar7 3 1 4 2 5 85 6 86 7 43 8 44 9 3 1 241 141 10 4 2 242 142 11 87 85 101 311 141 301 12 88 86 102 312 142 302 And so on, how can i do this? when i do a match, it only returns the first value, same with VLOOKUP. Any advice or code would be appreciated... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning Multiple Matches
This does not appear to work when the list of "nodes" is longer,
say closer to 2000. Correct. Its constrained by the max number of cols available in the sheet as you copy the formula in K2 across/fill down (ie 256* - 11 = 245) *xl2003's max cols You could try posting in .programming. Or, use xl2007, which I heard has in excess of 2000 cols -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:362 Subscribers:64 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum Multiple Matches | Excel Worksheet Functions | |||
| Returning matches from mutiple rows | Excel Worksheet Functions | |||
| VLOOKUP and multiple matches | Excel Discussion (Misc queries) | |||
| Displaying multiple matches | Excel Worksheet Functions | |||
| Multiple Matches | Excel Worksheet Functions | |||
Linear Mode
