Categorizing formula

[jwillits]

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>

DEBIT	09/27/2018	PAYMENTECH CHARGEBACK 234234 CCD ID: 124234213	-2000	DEBIT_CARD	112093.06
CREDIT	09/26/2018	PAYMENTECH DEPOSIT 2524323 CCD ID: 1461346512	2745.32	ACH_CREDIT	110430.76
DEBIT	09/24/2018	PAYMENTECH CHARGEBACK 25435234 CCD ID: 12512451	-747	ACH_DEBIT
CREDIT	09/05/2018	PAYMENTECH DEPOSIT 221342343 CCD ID: 12343214	257.76	ACH_DEBIT

<colgroup><col style="width: 113px"><col width="100"><col width="443"><col width="115"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

Example B

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

EXPENSE						JULY	AUG	SEP
	COGS
		RETAIL
			SKINCARE
				DERMALOGICA		1,000	0	0
				SKINCEUTICALS		1,500	2,000	1,000
			MAKEUP
				JANE IREDALE		0	250	1,000
		MEDICAL SUPPLIES
				MCKESSON		1,500	200	5,000
		MEDICINE
			COMPOUNDED
				OLYMPIA PHARMACY		0	0	500
			BRAND NAME
				MCKESSON		0	700	0
	TOTAL					4,000	3,150	7,500
	SGA
		TRAVEL
				SOUTHWEST		0	500	0
	TOTAL					5,500	4,550	13,000
	FINANCING
		BANK FEES				150	200	100
	TOTAL					9,650	9,100	20,600
	GRAND TOTAL					19,150	16,800	41,100

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

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

 

[mole999]

Date might use =Text(cell,"mm-yy")

 

[jwillits]

Date might use =Text(cell,"mm-yy")

OK, but what would the formula be to put all the paymentech payments from example A into a cell under the correct month?