Consolidate without shifting location

[excelhobo]

Hello all, first post here!

I could use some help with the following problem. I have a list of Customers and each purchase they have made by receipt # and item. However my employer would like to be able to find the total of each receipt while still being able to see each item bought. There is a variable number if items in each receipt.

If anyone has any suggestions that may work (either through formulas and/or VBA) it would be very appreciated!

Current Data

Receipt Number	Total	Item Number
123456	$10.00	17453
123456	$15.00	71664
123456	$10.00	37489
111111	$100.00	76514
222222	$25.00	66964
333333	$100.00	34686
333333	$50.00	48633
333333	$25.00	75434
333333	$20.00	66834

<tbody>
</tbody>

If I consolidate the rows, I will have the total for each receipt, but the item numbers would no longer match up with the receipt number.

Consolidate Result (not good)

Receipt Number	Total	Item Number
123456	$35.00	17453
111111	100.00	71664
222222	25.00	37489
333333	195.00	76514
		66964
		34686
		48633
		75434
		66834

<tbody>
</tbody>

Desired Result (return total to top of each repeated receipt number)

Receipt Number	Total	Item Number
123456	35.00	17453
123456		71664
123456		37489
111111	100.00	76514
222222	25.00	66964
333333	195.00	34686
333333		48633
333333		75434
333333		66834

<tbody>
</tbody>

Any help is very appreciated!

 

[AliGW]

First you need to make sure that your currency columns are properly formatted as such. Then this:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G
1	Current Data
2	Receipt Number	Total	Item Number		Summary
3	123456	$ 10.00	17453		123456	$ 35.00	17453
4	123456	$ 15.00	71664		123456		71664
5	123456	$ 10.00	37489		123456		37489
6	111111	$100.00	76514		111111	$ 100.00	76514
7	222222	$ 25.00	66964		222222	$ 25.00	66964
8	333333	$100.00	34686		333333	$ 195.00	34686
9	333333	$ 50.00	48633		333333		48633
10	333333	$ 25.00	75434		333333		75434
11	333333	$ 20.00	66834		333333		66834
Sheet4

Cell Formulas
Range	Formula
E3	=A3
F3	=IF(E3=E2,"",SUMIF($A$3:$A$11,E3,$B$3:$B$11))
G3	=C3

 

[KenDue]

You might try using a Pivot Table to summarize your data. Place the Receipt Number, then the Item Number under Row Labels, and place the Total under Values and specify Sum of Total as your value field setting.

 

[KenDue]

I didn't see AliGW's post until after I had posted... her solution looks EXACTLY like your request!

 

[AliGW]

I'm sure a pivot table would be fine, but they are not to everybody's taste, partly because they don't update automatically.

 

[excelhobo]

Thank for the assistance AliGW. Nice and simple but effective formula!

 

[AliGW]

You're welcome!