INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
- Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here
|
Best of Excel
|
NORMALIZE Your Table using the PivotTable Wizard by SkipVought
Posted: 7 Jul 04 (Edited 28 Jun 17)
|
The Dilema
You have a sheet set up something like this...
Acct Jan Feb Mar
Acct01 234 123 567
Acct02 345 234 678
Acct03 456 345 789
and you're having problems doing the stuff to it that you'd like to do.
That's because your data is NOT NORMALIZED. You might want to check out this EXCELLENT link 'Relational Database Design'
Excel's PivotTable Wizard to the RESCUE!
1) Start the wizard -- Data/PivotTable & PivotChart Report...
If you have Excel Version 2007+ use alt+D P to activate the PT Wisard
2) Step 1 of 3 - Select Option Button: Multiple Consolidation Ranges -- [Next]
3) Step 2a of 3 - Select Option Button: I will create the page fields -- [Next]
4) Step 2b of 3 - With your cursor in the Range Textbox, select the data range on your sheet that you want to normalize -- [Add] -- [Next]
5) Step 3 of 3 - [Finish]
6) Drag the Row and Column buttons OFF the Layout -- [OK] -- [Finish]
7) Mysteriously, you are on another sheet that has a 4-cell pivot table. Double click the BOTTOM RIGHT CELL
8) Again, on another sheet -- This is you data normalized, or at least closer to it. You'll need to change headings at least.
Row Column Value
Acct01 Jan 234
Acct01 Feb 123
Acct01 Mar 567
Acct02 Jan 345
Acct02 Feb 234
Acct02 Mar 678
Acct03 Jan 456
Acct03 Feb 345
Acct03 Mar 789
By the way, my example had only one column, Acct, of "Row" data. If your instance has more than one column, you will need to concatenate the "Row" data, separated by a delimiter such as |, into a single column before starting this process. So this...
A B C D E
Acct Sub Jan Feb Mar
Acct01 A01A 234 123 567
Acct02 A02B 345 234 678
Acct03 A03A 456 345 789
...would become this using the formula C1: =A1&"|"&B1...
A B C D E F
Acct Sub Acct|Sub Jan Feb Mar
Acct01 A01A Acct01|A01A 234 123 567
Acct02 A02B Acct02|A02B 345 234 678
Acct03 A03A Acct03|A03A 456 345 789
The normalization process will not use the first two columns.
Then, when the proceed is completed, use the Data > Data tools > Text to columns feature to complete the table, returning Acct and Sub in this case to separate columns, naturally shifting the "Column" and "Value" columns to the right appropriately.
|
Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum
|
|
|
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
