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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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?
 
Upvote 0
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
1316.72.1X2.3Y2.3Z
1420
1531.91.1A0.8B
16
Sheet1
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,218,625
Messages
6,143,570
Members
450,493
Latest member
Woejeber

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top