Data Transposing in XL 1

[MeGustaXL]

Hi Chaps,

I have a load of data like this:

[b]Part Number	Feature[/b]
1234567		First
1234567		Second
1234567		Fourth
7654321		Second
2468013		First
2468013		Fifth

and so on...

I'd like to show it like this:

[b]Part Number	Feature1   Feature2   Feature3[/b]....
1234567		First      Second     Fourth
7654321		Second
2468013		First      Fifth

etc...

What's the best way to grab it and transpose it like that? VLOOKUPs? Pivot Table?

Thanks in advance,


Chris

Someday I'll know what I'm donig...damn!

 

[kjv1611]

Are you just looking to do this one time, or is it something that would need to be repeated or refreshed? If just doing one time, I'd think the easiest way would be to dump the data to a new Access database, run a CrossTab Query on it using the Query Wizard, then dump back to Excel.

Otherwise, I'd have to think about this one. My initial guess is that the best method would be using MS Query, so if Skip answers, he can probably make sure you get that right. Maybe one day I'll sit down, and make myself use it enough that I can feel more confident about it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

Access would have a little more work since you would need to identify a sort order or ranking in a field that determines which is Feature1 or 2 or 3.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

hi,

Here's an approch.

1) I made your source data a Structured Table via Insert > Tables > Table -- Named Table1

2) The output table report is on a separate sheet (the numbers in row one are significant)

Part Num  1       2       3       4     5
1234567 
7654321 
2468013

here's the formula in B2 to copy across and down
[tt]
B2: =IFERROR(INDEX(OFFSET(Table1[Feature],MATCH($A2,Table1[Part Number],0)-1,0,COUNTIF(Table1[Part Number],$A2),1),B$1,1),"")
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Hi Skip, thanks for the suggestion. I don't have the option in my menus to "Insert > Tables > Table -- Named Table1" - I'm using XL 2003, so I don't think it has that option does it? or is there something I've missed?

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

You're correct. Instead, I'd name my ranges in the source table, via Insert > Names > Create names in TOP row. Then the formula becomes...
[tt]
B2: =IF(ISNA(MATCH($A2,Part_Number,0)-1,0,COUNTIF(Part_Number,$A2),1)),"",INDEX(OFFSET(Feature,MATCH($A2,Part_Number,0)-1,0,COUNTIF(Part_Number,$A2),1),B$1,1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Thanks Skip, I've tried that but get a Formula Contains Error warning, which picks up on the zero between -1 and COUNTIF in the first IF ...

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

use this and then mask the #REF! using conditional formatting to make the font the same shade as the cell interior...
[tt]
B2: =INDEX(OFFSET(Feature,MATCH($A2,Part_Number,0)-1,0,COUNTIF(Part_Number,$A2),1),B$1,1)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

okay, this one takes care of the REF error...
[tt]
=IF(ISERROR(INDEX(OFFSET(Feature,MATCH($A2,Part_Number,0)-1,0,COUNTIF(Part_Number,$A2),1),B$1,1)),"",INDEX(OFFSET(Feature,MATCH($A2,Part_Number,0)-1,0,COUNTIF(Part_Number,$A2),1),B$1,1))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MeGustaXL]

Thank you again Skip! Once again you've done my job for me, and come up with the goods! Unfortunately the only way I can reward you is with a STAR, and only one of 'em at that!

Chris

Someday I'll know what I'm donig...damn!

 

[MeGustaXL]

BTW, apologies for the looooonnnnggg delay in getting back, but I've been on a spot of much-needed holiday ;-)

Chris

Someday I'll know what I'm donig...damn!

 

[SkipVought]

Thanx! Glad you're back, bright-eyed and bushy-tailed! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}