ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Returning Multiple Matches (https://www.excelbanter.com/excel-worksheet-functions/188525-returning-multiple-matches.html)

Adam Hodge
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...


Max
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...


Max
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
---

m
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...


Max
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
---




All times are GMT +1. The time now is 08:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com