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:

[TABLE="width: 700"]
<tbody>[TR]
[TD]Transaction Description[/TD]
[TD]Transaction Date[/TD]
[TD]Amount[/TD]
[TD]Cost Center[/TD]
[TD]Class[/TD]
[TD]Job[/TD]
[/TR]
[TR]
[TD]Home Depot[/TD]
[TD]8/1/2017[/TD]
[TD]45.17[/TD]
[TD]1140[/TD]
[TD]Masonry[/TD]
[TD]Texas Middle School[/TD]
[/TR]
[TR]
[TD]The UPS Store[/TD]
[TD]8/25/2017[/TD]
[TD]17.85[/TD]
[TD]747000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wal-Mart Supercenter[/TD]
[TD]8/14/2017[/TD]
[TD]121.32[/TD]
[TD]2104[/TD]
[TD]Drywall[/TD]
[TD]John Doe Elementary[/TD]
[/TR]
[TR]
[TD]Home Depot[/TD]
[TD]8/10/2017[/TD]
[TD]238.15[/TD]
[TD]1140[/TD]
[TD]Masonry[/TD]
[TD]Texas Middle School[/TD]
[/TR]
</tbody>[/TABLE]

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
1140, Masonry, Texas Middle School
2104, Drywall, John Doe Elementary
1140, Masonry, Texas Middle School

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Transaction Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Transaction Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Amount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Cost Center[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Class[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Job[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Key[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Home Depot[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8/1/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]45.17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1140[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Masonry[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Texas Middle School[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]The UPS Store[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8/25/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17.85[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]747000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"]747000[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Wal-Mart Supercenter[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8/14/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]121.32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2104[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Drywall[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]John Doe Elementary[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Home Depot[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8/10/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]238.15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1140[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Masonry[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Texas Middle School[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(LEN(D2)=6,D2,D2&", "&E2&", "&F2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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
Row Labels	Sum of Amount
747000
1140, Masonry, Texas Middle School
2104, Drywall, John Doe Elementary
(blank)
Grand Total

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]17.85[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]283.32[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]121.32[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]422.49[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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