Pivot data to add columns

ndbhatt

New Member
Joined
Nov 18, 2005
Messages
14
I can do the pivot data with additional column using SQL but I NEED to do the following in excel:
Change this :->
Year Quarter Name Income
1900 Q1 ABC 1
1900 Q2 ABC 1
1900 Q3 ABC 1
1900 Q4 ABC 2
1900 Q1 XYZ 3
1900 Q2 XYZ 3
1900 Q3 XYZ 3
1900 Q4 XYZ 5

To :->
Year Name Income_Q1 Income Q2 Income Q3 Income Q4
1900 ABC 1 1 1 2
1900 XYZ 3 3 3 5

I hope some excel guru can guide me.
:rolleyes:
-Nick
 

oliver_penney

Board Regular
Joined
Dec 18, 2002
Messages
182
hi

welcome to the board

off the top of my head, you can do that with:-

1) a pivot table

2) using SUMPRODUCT formulae to summarise the data differently

or

3) use some fancy code using 'offset'

you familiar with pivot tables?
 

ndbhatt

New Member
Joined
Nov 18, 2005
Messages
14
Let me re-phrase my query with more realistic data

Let me re-phrase my issue with more realistic data
frame.xls
ABCDEFGHIJ
1
2Current form of data
3LoanIDRate DiffRate Diff Type
412.1X
512.3Y
612.3Z
720
831.1A
930.8B
10
11Expected form of data
12LoanIDTotal RateDiffRate1Rate Diff Type 1Rate2Rate Diff Type2Rate3Rate Diff Type 3
131 X Y Z
1420
1531.9 A B
16
Sheet1
 

ndbhatt

New Member
Joined
Nov 18, 2005
Messages
14
no. rows vary

This will not work for loanID with just one record. As you can see the number of rows for each LoanID is varying. It could be anywhere from 1 to 3.
 

Forum statistics

Threads
1,078,398
Messages
5,339,983
Members
399,345
Latest member
elvy

Some videos you may like

This Week's Hot Topics

Top