Multiple column Balance Summarization

[casexcel]

I have over 6000 rows of data. I have sorted it so each person has 2 lines of data that need to be added together. Each cust has 2 rows of data that need to be combined, but I need it to return the information in each column

Row 1 (Col A - D) = Headers

A Cust No B Name C Type D Balance

1000 Jane Doe Silver $10.15
1000 Jane Doe Silver $599.07
2000 Jim Smith Gold $130.05
2000 Jim Smith Gold $379.34
3000 Joe Go Platinum $37.95
3000 Joe Go Platinum $397.03

I need the end result to look like:

A Cust No B Name C Type D Balance

1000 Jane Doe Silver $609.22
2000 Jim Smith Gold $509.39
3000 Joe Go Platinum $434.39

I can't get the consolidate to work and bring back all the original information.

What is the easiest way to do this?
Catherine

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Cust No	Name	Type	Balance
2	1000	Jane doe	Silver	10.15		1000	Jane doe	609.22
3	1000	Jane doe	Silver	599.07		2000	Jim Smith	509.39
4	2000	Jim Smith	Gold	130.05		3000	Joe Go	434.98
5	2000	Jim Smith	Gold	379.34
6	3000	Joe Go	Platinum	37.95
7	3000	Joe Go	Platinum	397.03
Output

Cell Formulas
Range	Formula
G2	=INDEX(B$2:B7,MATCH($F2,$A$2:$A$7,0))
H2	=SUMPRODUCT(--($A$2:$A$7=$F2),($D$2:$D$7))
F2	{=INDEX(A$2:A$7,MATCH(0,COUNTIF(F$1:F1,A$2:A$7),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sandy666]

another way:

with PivotTable or PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td][/td][td]PivotTable[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1000​

[/td][td=bgcolor:#DDEBF7]Jane Doe[/td][td=bgcolor:#DDEBF7]Silver[/td][td=bgcolor:#DDEBF7]

10.15​

[/td][td][/td][td=bgcolor:#DDEBF7]Column1[/td][td=bgcolor:#DDEBF7]Column2[/td][td=bgcolor:#DDEBF7]Column3[/td][td=bgcolor:#DDEBF7]Sum of Column4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1000​

[/td][td]Jane Doe[/td][td]Silver[/td][td]

599.07​

[/td][td][/td][td]

1000​

[/td][td]Jane Doe[/td][td]Silver[/td][td]

609.22​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

2000​

[/td][td=bgcolor:#DDEBF7]Jim Smith[/td][td=bgcolor:#DDEBF7]Gold[/td][td=bgcolor:#DDEBF7]

130.05​

[/td][td][/td][td]

2000​

[/td][td]Jim Smith[/td][td]Gold[/td][td]

509.39​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

2000​

[/td][td]Jim Smith[/td][td]Gold[/td][td]

379.34​

[/td][td][/td][td]

3000​

[/td][td]Joe Go[/td][td]Platinum[/td][td]

434.98​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3000​

[/td][td=bgcolor:#DDEBF7]Joe Go[/td][td=bgcolor:#DDEBF7]Platinum[/td][td=bgcolor:#DDEBF7]

37.95​

[/td][td][/td][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

1553.59​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3000​

[/td][td]Joe Go[/td][td]Platinum[/td][td]

397.03​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]PowerQuery[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column3[/td][td=bgcolor:#70AD47]Sum[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

1000​

[/td][td=bgcolor:#E2EFDA]Jane Doe[/td][td=bgcolor:#E2EFDA]Silver[/td][td=bgcolor:#E2EFDA]

609.22​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]

2000​

[/td][td]Jim Smith[/td][td]Gold[/td][td]

509.39​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

3000​

[/td][td=bgcolor:#E2EFDA]Joe Go[/td][td=bgcolor:#E2EFDA]Platinum[/td][td=bgcolor:#E2EFDA]

434.98​

[/td][/tr]
[/table]

i forgot about headers but this is cosmetic issue