Costing a Credit Card Bill

B

Brack

New Member
Joined
Mar 13, 2013
Messages
15
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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
I'd suggest using a Pivot Table.

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

ABCDEFG
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:

AB
Row LabelsSum 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.
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
Eric W said:
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.
Click to expand...

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:cool:
PowerPivot :cool::cool:
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top