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