Categorizing formula

J

jwillits

New Member
Joined
Jul 10, 2018
Messages
38
Office Version
  1. 2010
Platform
  1. Windows
So we have bank statements that come in and we are trying to organize everything from example A to look like example B. To do this, we need to have a formula that takes into account the date, to put it in the correct month, as well as listing it under the correct subcategory.

What would the formula look like if I wanted to list the all Paymentech Deposits from example A under the Bank Fees subcategory in example B in September.

Also, for the chargeback, what would the formula look like if I wanted to list all Paymentech Chargebacks from Example A into McKesson from example B in September?

Take into account that one is negative and one is positive and it will have to be able to include all deposits/chargebacks from the list of data.


Example A

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 113px"><col width="100"><col width="443"><col width="115"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]DEBIT[/TD]
[TD="align: right"]09/27/2018[/TD]
[TD]PAYMENTECH CHARGEBACK 234234 CCD ID: 124234213[/TD]
[TD="align: right"]-2000[/TD]
[TD]DEBIT_CARD[/TD]
[TD="align: right"]112093.06[/TD]
[/TR]
[TR]
[TD]CREDIT[/TD]
[TD="align: right"]09/26/2018[/TD]
[TD]PAYMENTECH DEPOSIT 2524323 CCD ID: 1461346512[/TD]
[TD="align: right"]2745.32[/TD]
[TD]ACH_CREDIT[/TD]
[TD="align: right"]110430.76[/TD]
[/TR]
[TR]
[TD]DEBIT[/TD]
[TD="align: right"]09/24/2018[/TD]
[TD]PAYMENTECH CHARGEBACK 25435234 CCD ID: 12512451[/TD]
[TD="align: right"]-747[/TD]
[TD]ACH_DEBIT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CREDIT[/TD]
[TD="align: right"]09/05/2018[/TD]
[TD]PAYMENTECH DEPOSIT 221342343 CCD ID: 12343214[/TD]
[TD="align: right"]257.76[/TD]
[TD]ACH_DEBIT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example B

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 33px"><col width="34"><col width="34"><col width="34"><col width="36"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]EXPENSE

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]JULY[/TD]
[TD="align: center"]AUG[/TD]
[TD="align: center"]SEP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]COGS

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]RETAIL[/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SKINCARE

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DERMALOGICA

[/TD]
[TD][/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SKINCEUTICALS

[/TD]
[TD][/TD]
[TD="align: right"]1,500[/TD]
[TD="align: right"]2,000[/TD]
[TD="align: right"]1,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MAKEUP

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JANE IREDALE

[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]1,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MEDICAL SUPPLIES

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MCKESSON

[/TD]
[TD][/TD]
[TD="align: right"]1,500[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]5,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MEDICINE

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COMPOUNDED

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]OLYMPIA PHARMACY

[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]BRAND NAME

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MCKESSON

[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4,000[/TD]
[TD="align: right"]3,150[/TD]
[TD="align: right"]7,500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SGA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TRAVEL

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SOUTHWEST

[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5,500[/TD]
[TD="align: right"]4,550[/TD]
[TD="align: right"]13,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FINANCING

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]BANK FEES

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]TOTAL

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9,650[/TD]
[TD="align: right"]9,100[/TD]
[TD="align: right"]20,600[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]GRAND TOTAL

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19,150[/TD]
[TD="align: right"]16,800[/TD]
[TD="align: right"]41,100[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Date might use =Text(cell,"mm-yy")
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
M
  • Question Question
Please help!! Need help with specialized formula.
Replies
3
Views
964
Eric W
Eric W
S
  • Question Question
Search a table and return rows based on criteria of 2 cells
Replies
1
Views
721
Fluff
Fluff
R
  • Question Question
Dynamic Table in Mail Merge with different page for key attribute
Replies
0
Views
1K
raushan316
R

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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