Costing a Credit Card Bill

[Brack]

I have an issue that I need some help with... We try to assign all of our expenses on our American Express cards either to overhead items or we try to job cost them...

We download each statement in Excel, and I transfer the bill to a recap sheet as follows:

Transaction Description	Transaction Date	Amount	Cost Center	Class	Job
Home Depot	8/1/2017	45.17	1140	Masonry	Texas Middle School
The UPS Store	8/25/2017	17.85	747000
Wal-Mart Supercenter	8/14/2017	121.32	2104	Drywall	John Doe Elementary
Home Depot	8/10/2017	238.15	1140	Masonry	Texas Middle School

<tbody>
</tbody>

The blue is the information that gets transferred, the red gets entered by our receptionist. There will be many transactions on each statement, but I need a way to subtotal this information, by cost center, and also by class and job, if the information gets job costed. So in the above example, I would output the work the receptionist puts in to a page that we print out that summarizes the costs for the owner - so would see that 1140 - Masonry - Texas Middle School was $283.32, 747000 was $17.85, and 2104 - Drywall - John Doe Elementary was $121.32.

The complicated factor is that any six digit code won't have a class or job, and any 4 digit code will have one of 4 different classes, and it will have a job name, but the job name could be one of about 60 different jobs.

I don't want to list all possible combinations, but is there a way to summarize the information?

The cost codes, classes, and jobs don't necessarily have any relationship. (code 1140 could apply to any job, any class, and same for 2104).

Any help that you could give me would be greatly appreciated!

 

[Eric W]

I'd suggest using a Pivot Table.

First, add a column to your table called "Key":

	A	B	C	D	E	F	G
1	Transaction Description	Transaction Date	Amount	Cost Center	Class	Job	Key
2	Home Depot	8/1/2017	45.17	1140	Masonry	Texas Middle School	1140, Masonry, Texas Middle School
3	The UPS Store	8/25/2017	17.85	747000			747000
4	Wal-Mart Supercenter	8/14/2017	121.32	2104	Drywall	John Doe Elementary	2104, Drywall, John Doe Elementary
5	Home Depot	8/10/2017	238.15	1140	Masonry	Texas Middle School	1140, Masonry, Texas Middle School
6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G2 =IF(LEN(D2)=6,D2,D2&", "&E2&", "&F2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Use the formula in G2, and drag down. Now just select columns A:G, go to the Insert tab, and select Pivot Table. Click OK on the dialog box. It will open a new sheet. On the right side is another dialog box. Drag "key" to the Rows box, and Amount to the Values box. Now click on the Count of Amount, select Value Field Settings, and select Sum. You should end up with something like this:

	A	B
1
2
3	Row Labels	Sum of Amount
4	747000	17.85
5	1140, Masonry, Texas Middle School	283.32
6	2104, Drywall, John Doe Elementary	121.32
7	(blank)
8	Grand Total	422.49
9

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Let us know if this works for you.

 

[SpillerBD]

Key?
Use either Report Layout, "Tabular" or "Outline".
Not really need, just bring down the fields that give your desired detail level, in the order you want the grouping to occur. You can have subtotal at each row level, no row levels or any level you want.
IF you want the entire text list on each line, just use the "Repeat All Row Labels Option"

 

[Brack]

Eric,

That got exactly what I needed!! Thank you!

 

[Eric W]

Glad we could help!
@SpillerBD ,
I'm not an expert on Pivot Tables, I just knew they were a better option than the formulas I started with. I tried your suggestions, and definitely learned a few things to try next time. Nevertheless, I still think my original suggestion has its own merits too.

 

[SpillerBD]

Glad we could help!
@SpillerBD ,
I'm not an expert on Pivot Tables, I just knew they were a better option than the formulas I started with. I tried your suggestions, and definitely learned a few things to try next time. Nevertheless, I still think my original suggestion has its own merits too.

Actually, yes yours does have merits. Sometimes it is nice to regain the full detail of each and every transaction. The sample data presented does not list a unique transaction ID, which your Key can simulate, but doesn't guarantee that per-item unique reference. Could have added a ROW() or something similar to get any unique value.
Pivot Tables
PowerPivot