# Consolidate without shifting location

#### excelhobo

##### New Member
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>

 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!

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### AliGW

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

Excel 2016 (Windows) 32 bit
ABCDEFG
1Current Data
2Receipt NumberTotalItem NumberSummary
3123456\$ 10.0017453123456\$ 35.0017453
4123456\$ 15.007166412345671664
5123456\$ 10.003748912345637489
6111111\$100.0076514111111\$ 100.0076514
7222222\$ 25.0066964222222\$ 25.0066964
8333333\$100.0034686333333\$ 195.0034686
9333333\$ 50.004863333333348633
10333333\$ 25.007543433333375434
11333333\$ 20.006683433333366834
Sheet4
Cell Formulas
RangeFormula
E3=A3
F3=IF(E3=E2,"",SUMIF(\$A\$3:\$A\$11,E3,\$B\$3:\$B\$11))
G3=C3

#### KenDue

##### New Member
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

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

#### AliGW

##### Banned
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

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

You're welcome!

Replies
7
Views
529
Replies
0
Views
584
Replies
1
Views
311
Replies
6
Views
625
Replies
3
Views
6K

1,191,190
Messages
5,985,201
Members
439,947
Latest member
fabiannic

### 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.

### Which adblocker are you using?

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

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