Excel: Convert Summary table to raw data 1

[MooSki]

Hi guys,
I have been given a spreadsheet which is a summary table of sales over a couple of years. On the left are three columns (Year, Quarter, Week) then the salesperson list starts, of which there are over 50, with the scope of having further people added and existing people not working here anymore, but we need to keep the figures.

The data isn't ideal - it's a bit messy, I know, but what I am wanting to do is convert this:

Year	Quarter	Week	Matthew Lincoln	Gordon Smith	Colin Bird	Alan Wood	Michael Armstrong
2008/09	3		11		100		70		60		10		20
2008/09	3		12		50		10		30		40		70

into someting like this:

Year	Quarter	Week	Name
2008/09	3	11	Matthew Lincoln	100
2008/09	3	11	Gordon Smith	70
2008/09	3	11	Colin Bird	60
2008/09	3	11	Alan Wood	10
2008/09	3	11	Michael Armstrong	20
2008/09	3	12	Matthew Lincoln	50
2008/09	3	12	Gordon Smith	10

Apologies for the formatting here, but I hope you get the idea.

Is there a clean way of doing this? The idea is to then be able to produce a load of pivot tables and graphs off the back of this, it would be just a lot easier if the data were more manageable.


If there's any further info you need, just ask.

Cheers,
Moo.

 

[SkipVought]

faq68-5287.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Note: If you have 2007 or later, Multiple Consolidation Ranges is not an option.

But fear not: The old 2003 keyboard shortcuts still work. Just use [Alt], [D], [P] (in succession, not all at once) to bring up the 2003 Pivot Table Wizard, then follow Skip's (amazing) FAQ.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

BTW, You'll have to begin by concatenating the ROW data (YEAR&"|"&Quarter&"|"&Week) and use THAT column of data FIRST, followed by the COLUMN (Pivoted) columns, so it looks like...
[tt]
YQW Matthew Lincoln Gordon Smith Colin Bird Alan Wood Michael Armstrong
2008/09|3|11 100 70 60 10 20
2008/09|3|12 50 10 30 40 70
[/tt]
Then AFTER the process, insert 2 columns and parse the ROW column into 3 values, using Data > text to columns

The entire process takes maybe 3 minutes, if you are familiar with the normalizing process.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MooSki]

Superb, Skip, spot on!

And thanks to Higgins for assuming I was on Excel 2007, which I was, but like a doofus forgot to mention.

 

[SkipVought]

Wonderful!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]