Scan for match and add to total.

[RichKat]

We are assigning accounts and departments to expenses on a monthly credit card statement. Each Row/Record has a debit column (G) and a Credit column (H) and the row also has an Account# Column (K) and a Department column (L). This looks something like this:

User​	Transaction Date​	Description	Debit	Credit	Category		Account	Department
Brian​	8/18/2023​	MOUNTAINS EDGE	26​		Repairs/Maintenance		66200	100
Gabe​	8/19/2023​	RBT MARATHON PETRO1014		1​				200
Gabe​	8/19/2023​	RBT CIRCLE K # 23710		0.32​				200
Gabe​	8/22/2023​	CIRCLE K # 23706	50.02​					200
Gabe​	8/22/2023​	WALMART.COM	39.47​					200

Then I have a separate table that looks like this:

Department 100​		Department 200​
Account	Amount​		Account	Amount​
50001-100			50001-200
65000-100			65000-200
65500-100			65500-200
65600-100			65600-200
66200-100			66200-200
66425-100			66425-200
67150-100			67150-200
67400-100			67400-200
67575-100			67575-200
Statement Total​	$ -​

In this table I want to scan down the account and department columns and when the account and department match one of the cells in my table, I want to subtract H from G and add it to the total in that amount cell.

So for the first line in the first table, G-0 is 26 and it would be placed in the total next to the fifth cell from the top’s amount.

Any help would be much appreciated.

Thank you

 

[awoohaw]

Try this:

Mr excel questions 63.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2				User	Transaction Date	Description	Debit	Credit	Category		Account	Department
3				Brian	2023-08-18	MOUNTAINS EDGE	26	0	Repairs/Maintenance		66200	100
4				Gabe	2023-08-19	RBT MARATHON PETRO1014	0	1				200
5				Gabe	2023-08-19	RBT CIRCLE K # 23710	0	0.32				200
6				Gabe	2023-08-22	CIRCLE K # 23706	50.02	0				200
7				Gabe	2023-08-22	WALMART.COM	39.47	0				200
8
9
10
11
12	Department 100		Department 200
13	Account	Amount		Account	Amount
14	50001-100	#N/A		50001-200
15	65000-100	#N/A		65000-200
16	65500-100	#N/A		65500-200
17	65600-100	#N/A		65600-200
18	66200-100	26		66200-200
19	66425-100	#N/A		66425-200
20	67150-100	#N/A		67150-200
21	67400-100	#N/A		67400-200
22	67575-100	#N/A		67575-200
23
24	Statement Total	26
25
RichKat

Cell Formulas
Range	Formula
B14:B22	B14	=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0))
B24	B24	=AGGREGATE(9,6,B14:B22)

 

[awoohaw]

I just noticed in your footer that you use 2007. The formulas I have above will not work.
I think the section to the right below will work for you.
(But, it may be a good idea to put your excel version in your profile so it shows up on your button where forum members can more easily see it.)

Mr excel questions 63.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2				User	Transaction Date	Description	Debit	Credit	Category		Account	Department
3				Brian	2023-08-18	MOUNTAINS EDGE	26	0	Repairs/Maintenance		66200	100
4				Gabe	2023-08-19	RBT MARATHON PETRO1014	0	1				200
5				Gabe	2023-08-19	RBT CIRCLE K # 23710	0	0.32				200
6				Gabe	2023-08-22	CIRCLE K # 23706	50.02	0				200
7				Gabe	2023-08-22	WALMART.COM	39.47	0				200
8
9
10
11
12	Department 100		Department 200				Department 100		Department 200
13	Account	Amount		Account	Amount		Account	Amount		Account	Amount
14	50001-100	#N/A		50001-200			50001-100	0		50001-200
15	65000-100	#N/A		65000-200			65000-100	0		65000-200
16	65500-100	#N/A		65500-200			65500-100	0		65500-200
17	65600-100	#N/A		65600-200			65600-100	0		65600-200
18	66200-100	26		66200-200			66200-100	26		66200-200
19	66425-100	#N/A		66425-200			66425-100	0		66425-200
20	67150-100	#N/A		67150-200			67150-100	0		67150-200
21	67400-100	#N/A		67400-200			67400-100	0		67400-200
22	67575-100	#N/A		67575-200			67575-100	0		67575-200
23
24	Statement Total	26					Statement Total	26
RichKat

Cell Formulas
Range	Formula
B14:B22	B14	=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0))
H14:H22	H14	=IF(ISNA( INDEX($G$3:$G$7,MATCH(LEFT(G14,FIND("-",G14)-1)&SUBSTITUTE(G14,LEFT(G14,FIND("-",G14)),""),($K$3:$K$7)&($L$3:$L$7),0))),0, INDEX($G$3:$G$7,MATCH(LEFT(G14,FIND("-",G14)-1)&SUBSTITUTE(G14,LEFT(G14,FIND("-",G14)),""),($K$3:$K$7)&($L$3:$L$7),0)))
B24	B24	=AGGREGATE(9,6,B14:B22)
H24	H24	=SUM(H14:H22)

 

[RichKat]

Try this:

Mr excel questions 63.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2				User	Transaction Date	Description	Debit	Credit	Category		Account	Department
3				Brian	2023-08-18	MOUNTAINS EDGE	26	0	Repairs/Maintenance		66200	100
4				Gabe	2023-08-19	RBT MARATHON PETRO1014	0	1				200
5				Gabe	2023-08-19	RBT CIRCLE K # 23710	0	0.32				200
6				Gabe	2023-08-22	CIRCLE K # 23706	50.02	0				200
7				Gabe	2023-08-22	WALMART.COM	39.47	0				200
8
9
10
11
12	Department 100		Department 200
13	Account	Amount		Account	Amount
14	50001-100	#N/A		50001-200
15	65000-100	#N/A		65000-200
16	65500-100	#N/A		65500-200
17	65600-100	#N/A		65600-200
18	66200-100	26		66200-200
19	66425-100	#N/A		66425-200
20	67150-100	#N/A		67150-200
21	67400-100	#N/A		67400-200
22	67575-100	#N/A		67575-200
23
24	Statement Total	26
25
RichKat

Cell Formulas
Range	Formula
B14:B22	B14	=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0))
B24	B24	=AGGREGATE(9,6,B14:B22)

Hey! Thanks so much. I'm sorry, I haven't updated my profile for a long while! I'm on 365 so that worked fine with slight adjustments for cell addresses. The exception is that the formula results in #N/A if all of the data isn't there. I would like for it to result in 0 if that's the case but I don't know how to test for #N/A. I tried "=IF(INDEX($G$2:$G$150,MATCH(TEXTBEFORE(Q3,"-")&TEXTAFTER(Q3,"-"),($K$2:$K$150)&($L$2:$L$150),0))="#N/A", 0, INDEX($G$2:$G$150,MATCH(TEXTBEFORE(Q3,"-")&TEXTAFTER(Q3,"-"),($K$2:$K$150)&($L$2:$L$150),0)))" but that still resulted in the #N/A.

 

[awoohaw]

wrap it with IFERROR

=IFERROR(=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0)),0)

 

[RichKat]

wrap it with IFERROR

=IFERROR(=INDEX($G$3:$G$7,MATCH(TEXTBEFORE(A14,"-")&TEXTAFTER(A14,"-"),($K$3:$K$7)&($L$3:$L$7),0)),0)

Oh, great! Wow, so simple. I'm learning a LOT! It seems like that's the only way I learn is to beat my head against a problem, ask for help and then compare what works with what didn't. Thanks so much!

 

[awoohaw]

You're welcome. I am happy you found a solution.

Best Wishes!

 

[RichKat]

You're welcome. I am happy you found a solution.

Best Wishes!

The sample that I first sent you leaves out one important thing... The items in the K and L columns may have duplicates and I need any row that meets the criteria to have the debit column subtracted from the credit column, from that row and THEN added to the totals in the small table. Does that make sense?

 

[awoohaw]

The sample that I first sent you leaves out one important thing... The items in the K and L columns may have duplicates and I need any row that meets the criteria to have the debit column subtracted from the credit column, from that row and THEN added to the totals in the small table. Does that make sense?

can you post some examples and what are the expected results.

 

[RichKat]

A

B

E

G

H

I

J

K

L

M

N

O

P

Q

R

1

User

Transaction Date

Description

Debit

Credit

Category

Account

Department

Department 100

Department 200

2

Brian

8/18/2023

MOUNTAINS EDGE

26​

3

Repairs/Maintenance

66200

100

Account

Amount

Account

Amount

3

Gabe

8/19/2023

RBT MARATHON PETRO1014

1​

200

50001-100

28.37​

50001-200

0​

4

Gabe

8/19/2023

RBT CIRCLE K # 23710

0.32​

200

65000-100

63.95​

65000-200

0​

5

Gabe

8/22/2023

CIRCLE K # 23706

50.02​

200

65500-100

617.45​

65500-200

0​

6

Gabe

8/22/2023

WALMART.COM

39.47​

200

65600-100

172.46​

65600-200

53.25​

7

Brian

8/23/2023

SONIC DRIVE IN #3236

50​

Business Meals

67150

100

66200-100

26​

66200-200

52.05​

8

Darius

8/23/2023

BOXLEY READY MIX

617.45​

Advertising

65500

100

66425-100

289​

66425-200

0​

9

Gabe

8/23/2023

WYTHEVILLE RURAL KING

459.06​

200

67150-100

50​

67150-200

0​

10

Gabe

8/23/2023

CIRCLE K # 23705

86​

200

67400-100

28.42​

67400-200

0​

11

Brian

8/24/2023

LOWES #01164*

28.37​

Cost of Goods Sold

50001

100

67575-100

608.75​

67575-200

0​

12

Nolan

8/24/2023

MARATHON PETRO213082

53.25​

Fuel

65600

200

13

Gabe

8/24/2023

CIRCLE K # 23705

105.07​

200

Statement Total

$ 1,989.70

14

Darius

8/25/2023

******* Barrel

100​

Business Meals

67150

100

15

Gabe

8/25/2023

RBT CIRCLE K # 23706

0.5​

200

16

Gabe

8/26/2023

RBT CIRCLE K # 23705

0.86​

200

17

Gabe

8/27/2023

AMAZON.COM*T317H4PH0 A

21.03​

200

18

Gabe

8/27/2023

RBT CIRCLE K # 23705

1​

200

19

Gabe

8/27/2023

AMZN Mktp US*T38BE4XB2

223.02​

200

20

Rich L.

8/28/2023

CASEYS #2840

84.76​

200

21

Rich L.

8/28/2023

SHEETZ 0407 00004077

75.78​

200

22

Rich L.

8/28/2023

SHELL OIL 10003719001

63.04​

200

23

Darius

8/28/2023

DOMAIN NETWORKS

289​

IT

66425

100

24

Daniel

8/28/2023

DUBLIN LIBERTY

172.46​

Fuel

65600

100

25

Daniel

8/28/2023

DUBLIN LIBERTY

101.52​

Fuel

65600

100

26

Gabe

8/28/2023

USPS PO 5199301382

17.95​

200

27

Gabe

8/28/2023

CIRCLE K # 23708

79​

200

28

Gabe

8/28/2023

LOWES #01164*

68.66​

200

29

Rich L.

8/29/2023

SCHEELS COLORADO SPRIN

720.96​

200

30

Rich L.

8/29/2023

CENEX JJ TRAVE09913559

82.24​

200

31

Darius

8/29/2023

VA STONE AND BRICK DES

2422.65​

Advertising

65500

100

32

Nolan

8/29/2023

WAL-MART #3270

52.05​

Repairs/Maintenance

66200

200

33

Daniel

8/29/2023

AMAZON.COM*T38037SJ0 A

37.9​

Repairs/Maintenance

66200

100

34

Gabe

8/29/2023

PARTSWH COM

74.81​

200

35

Rich L.

8/30/2023

LOVE'S #0873 OUTSIDE

95.28​

200

36

Darius

8/30/2023

Amazon.com*T35UU9I50

63.95​

Office Supplies

65000

100

37

Daniel

8/31/2023

WYTHEVILLE RURAL KING

23.35​

Repairs/Maintenance

66200

100

38

Daniel

8/31/2023

SQ *MEREDITHS GARAGE

20​

Repairs/Maintenance

66200

100

Ok, rows 2, 33, 37, and 38 all match a criteria for N7 so the total for O7 should subtract the corresponding H cells and add the corresponding G cells to return the total of 104.25