Array problem, I think..

S

Steve

Hi,

I have a spreadsheet with 3 columns of data. The first column contains
a ranking for a vendor. The second and third columns provide the
vendor part numbers and names, respectively. I need to spread this
ranking information across the spreadsheet into separate columns. That
is, I need vendor part numbers and names that correspond to the first
ranking to appear under columns called Vendor #1 Part Number and
Vendor #1 Name. Then the next two columns would apply to the second
Vendor and the next two the third...up to 5 Vendors.

Any suggestions would be much appreciated.


Thanks,

Steve

I hope this doesn't get to skewed when I post...

Rank Vndr #1 Item Vndr #1 Name Vndr #2 Item Vndr #2 Name Vndr #3
1 123-3333 Apple Connectors
1 BPD-7732 Tru Connectors
1 YTR-33BD Strait Conn Co.
2 9999RT22 Sly RF Conn Co.
3 4444PL Slick Components
1 UV-HY321 Tech Conn Corp
1 8-1VMX07 Tokyo Conn Co.
2 XY-W-53 Roger Roger RF
1 MX-25321 WiFi Comp Co.
2 BPD-7982 Tru Connectors
3 KW-060908-1 Shore-Footed Conn.
4 129-3369 Apple Connectors
5 4550PL Slick Components
1 5-1XMX09 Tokyo Conn Co.
1 9999RL55 Sly RF Conn Co.
1 APD-7112 Tru Connectors
1 KW-060908-2 Shore-Footed Conn.
2 UV-GY877 Tech Conn Corp
3 MR-35001 WiFi Comp Co.
4 4331PL Slick Components
1 3-1VDX52 Tokyo Conn Co.
2 UV-TY991 Tech Conn Corp
3 KW-060908-3 Shore-Footed Conn.
1 CPD-7799 Tru Connectors
1 YXR-35CD Strait Conn Co.
2 XY-T-57 Roger Roger RF
1 139-4469 Apple Connectors
1 MS-26529 WiFi Comp Co.
 
M

Max

One simple, fast non-array set-up to dynamically deliver it here

Assume source data in cols A to C, from row2 down

In E2:
=IF($A2="","",IF($A2=INDEX({1;2;3;4;5},COLUMNS($A:A)),ROW(),""))
Copy E2 across by 5 cols to I2, fill down to cover the max expected extent
of source data. Leave E1:I1 blank. Cols E to I are the criteria cols
corresponding to ranks 1 to 5.

Then to eke out all rank 1's automatically (results will be neatly bunched
at the top)
Place in say, K2:
=IF(ROWS($1:1)>COUNT($E:$E),"",INDEX(B:B,SMALL($E:$E,ROWS($1:1))))
Copy K2 to L2, fill down

For all rank 2's,
Place in N2:
=IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(B:B,SMALL($F:$F,ROWS($1:1))))
Copy N2 to O2, fill down

N2's the same point formula as K2, except pointing to col F instead of E
Repeat to set-up for ranks 3 to 5
 
S

Steve

One simple, fast non-array set-up to dynamically deliver it here

Assume source data in cols A to C, from row2 down

In E2:
=IF($A2="","",IF($A2=INDEX({1;2;3;4;5},COLUMNS($A:A)),ROW(),""))
Copy E2 across by 5 cols to I2, fill down to cover the max expected extent
of source data. Leave E1:I1 blank. Cols E to I are the criteria cols
corresponding to ranks 1 to 5.

Then to eke out all rank 1's automatically (results will be neatly bunched
at the top)
Place in say, K2:
=IF(ROWS($1:1)>COUNT($E:$E),"",INDEX(B:B,SMALL($E:$E,ROWS($1:1))))
Copy K2 to L2, fill down

For all rank 2's,
Place in N2:
=IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(B:B,SMALL($F:$F,ROWS($1:1))))
Copy N2 to O2, fill down

N2's the same point formula as K2, except pointing to col F instead of E
Repeat to set-up for ranks 3 to 5
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik







- Show quoted text -
Click to expand...

that did, thanks so much for the help....Steve
 
Top