Pivot data to add columns

[ndbhatt]

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.

-Nick

 

[oliver_penney]

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]

Let me re-phrase my query with more realistic data

Let me re-phrase my issue with more realistic data

frame.xls
	A	B	C	D	E	F	G	H	I	J
1
2		Current form of data
3		LoanID	Rate Diff	Rate Diff Type
4		1	2.1	X
5		1	2.3	Y
6		1	2.3	Z
7		2	0
8		3	1.1	A
9		3	0.8	B
10
11		Expected form of data
12		LoanID	Total RateDiff	Rate1	Rate Diff Type 1	Rate2	Rate Diff Type2	Rate3	Rate Diff Type 3
13		1	6.7	2.1	X	2.3	Y	2.3	Z
14		2	0
15		3	1.9	1.1	A	0.8	B
16
Sheet1

 

[oliver_penney]

in c13, how about:-

=SUMPRODUCT(($B$4:$B$9=$B13)*($D$4:$D$9=$E13)*($C$4:$C$9))

?

 

[ndbhatt]

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.