Need help with Index / Match in vba (or if there is a better way to do this)

[snuffnchess]

I am needing to match data from the ACCFEES sheet with data that is on the Combined sheet based on the "shipment_id" field.

If the description in Column H of Accfees is "freight" then I want to ignore the data all together
Fuel Surcharge from Column H of Accfees place Column I data in Column AL of Combined
Notification fee from Column H of Accfees place Column I data to go in Column AM of Combined
Resi Surchg from Column H of Accfees place Column I data to go in Column AN of Combined
Addr Correction from Column H of Accfees place Column I data to go in Column AO of Combined

And everything else from Column H of Accfees to add together column I and put in Column AP of Combined.

AQ of Combined would be the Sum of Columns AL:AP

Alternatively,Is there a way to just have the macro create it's own column names based off of the information that appears in Accfees column H, and then just puts the data in column I into that column? Not sure which is the easiest / best way to go about this?

How would I even begin to do something like this??

Here is what the "Combined" worksheet looks like

History.xlsm
	A																																			AJ	AK	AL	AM	AN	AO	AP	AQ
1	shipment_id(4)																																			Subtotal	Total	Fuel Surcharge	Notification	Residential	Address Correction	Other	Total Accessorial
2	149177149																																				$ 30
3	149191850																																			$ 487	$ 1,371
4	149178437																																			$ 146	$ 168
5	149021678																																			$ 10	$ 15
6	149011416																																			$ 148	$ 186
7	149021679																																			$ 11	$ 16
8	149021680																																			$ 31	$ 34
9	149178347																																			$ 249	$ 312
10	149021681																																			$ 11	$ 12
11	149021682																																			$ 31	$ 34
12	149021683																																			$ 29	$ 36
13	149021684																																			$ 14	$ 16
14	149021685																																			$ 34	$ 37
15	149021686																																			$ 51	$ 55
16	149021687																																			$ 11	$ 12
17	149021688																																			$ 10	$ 11
18	149021689																																			$ 9	$ 10
19	149021690																																			$ 11	$ 15
20	149021691																																			$ 12	$ 13
21	149021692																																			$ 11	$ 12
22	149180784																																			$ 217	$ 285
23	149183129																																			$ 172	$ 207
24	149021677																																			$ 31	$ 36
25	149021693																																			$ 11	$ 30
26	149021694																																			$ 11	$ 12
27	149021695																																			$ 12	$ 18
28	149021696																																			$ 11	$ 12
29	149021697																																			$ 15	$ 20
30	149021698																																			$ 10	$ 11
31	149182866																																			$ 137	$ 178
32	149011415																																			$ 157	$ 177
33	149009220																																			$ 156	$ 210
34	149021699																																			$ 40	$ 73
35	149021700																																			$ 44	$ 78
36	149186011																																			$ 202	$ 229
37	149196173																																			$ 290	$ 367
38	149180185																																			$ 130	$ 146
39	149009218																																			$ 225.48	$ 253.50
40	149021701																																			$ 12	$ 18
41	149021706																																				$ 6
42	149188784																																			$ 207	$ 277
43	149178346																																			$ 127	$ 160
44	149182864																																			$ 258	$ 326
45	149183127																																			$ 567	$ 717
Combined

Here is what the data on the "ACCFEES" worksheet looks like

History.xlsm
	D	E	F	G	H	I
1	shipment_id(4)	transaction_type(25)	transaction_id(4)	code(50)	description(50)	Rate
2	149009218			400	Freight	225.48
3	149009218			UNIFSC	Fuel Surcharge	28.02
4	149009220			400	Freight	155.86
5	149009220			UNIFSC	Fuel Surcharge	54.05
6	149011415			400	Freight	156.58
7	149011415			UNIFSC	Fuel Surcharge	20.35
8	149011416			400	Freight	147.74
9	149011416			UNIFSC	Fuel Surcharge	38.12
10	149021677			400	Freight	30.8
11	149021677			EVC	Declared Value	1.8
12	149021677			FUE	Fuel Surcharge	2.93
13	149021678			400	Freight	10.19
14	149021678			10	Del Area Fee	3.1
15	149021678			FUE	Fuel Surcharge	1.23
16	149021679			400	Freight	11.22
17	149021679			FUE	Fuel Surcharge	1.32
18	149021679			10	Del Area Fee	3.1
19	149021680			400	Freight	31.36
20	149021680			FUE	Fuel Surcharge	2.9
21	149021681			400	Freight	10.84
22	149021681			FUE	Fuel Surcharge	1
23	149021682			400	Freight	30.82
24	149021682			FUE	Fuel Surcharge	2.85
25	149021683			400	Freight	28.82
26	149021683			010E	Remote Area Chg	3.7
27	149021683			FUE	Fuel Surcharge	3.01
28	149021684			400	Freight	14.28
29	149021684			FUE	Fuel Surcharge	1.32
30	149021685			400	Freight	33.63
31	149021685			FUE	Fuel Surcharge	3.11
32	149021686			400	Freight	50.64
33	149021686			FUE	Fuel Surcharge	4.68
34	149021687			400	Freight	11.22
35	149021687			FUE	Fuel Surcharge	1.04
36	149021688			400	Freight	9.9
37	149021688			FUE	Fuel Surcharge	0.92
38	149021689			400	Freight	9.22
39	149021689			FUE	Fuel Surcharge	0.85
40	149021690			400	Freight	10.63
41	149021690			10	Del Area Fee	3.1
42	149021690			FUE	Fuel Surcharge	1.27
43	149021691			400	Freight	12.35
44	149021691			FUE	Fuel Surcharge	1.14
45	149021692			400	Freight	10.94
46	149021692			FUE	Fuel Surcharge	1.01
47	149021693			400	Freight	10.84
48	149021693			AAJ-00	Addr Correction	18
49	149021693			FUE	Fuel Surcharge	1.03
50	149021694			400	Freight	11.22
51	149021694			FUE	Fuel Surcharge	1.07
52	149021695			400	Freight	11.62
53	149021695			REP	Resi Surchg	4.45
54	149021695			FUE	Fuel Surcharge	1.53
55	149021696			400	Freight	11.22
56	149021696			FUE	Fuel Surcharge	1.07
57	149021697			400	Freight	14.87
58	149021697			10	Del Area Fee	3.1
59	149021697			FUE	Fuel Surcharge	1.71
60	149021698			400	Freight	10.19
61	149021698			FUE	Fuel Surcharge	0.97
62	149021699			400	Freight	39.86
63	149021699			690-AH	Addl Handling Weig	27
64	149021699			FUE	Fuel Surcharge	6.35
65	149021700			400	Freight	44.05
66	149021700			690-AH	Addl Handling Weig	27
ACCFEES

 

[RasGhul]

Hello

What version of Excel do you have?

 

[snuffnchess]

Office 365

 

[RasGhul]

Here's a formula solution if you can use it, I converted ACCFEES data to a table so that Combined can calculate as you add more data.

The Combined Sheet has all the actual Descriptions from the descriptions column except freight. Note that for the Index formula to work some of the ACCFEES header names had spaces before and after their names which I removed for proper matches.

The Combined header names are Data Validated so you can change the column order as required.

Need help with IndexMatch in vba_History.xlsx
	A																																			AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1	shipment_id(4)																																			Subtotal	Total	Fuel Surcharge	Declared Value	Del Area Fee	Remote Area Chg	Resi Surchg	Addr Correction	Addl Handling Weig	Other	Total		Descriptions
2	149177149																																				$30.00											Fuel Surcharge
3	149191850																																			$487.46	$1,371.12											Declared Value
4	149178437																																			$145.63	$167.95											Del Area Fee
5	149021678																																			$10.19	$14.52	$1.23		$3.10					$14.52	$18.85		Remote Area Chg
6	149011416																																			$147.74	$185.86	$38.12							$185.86	$223.98		Addr Correction
7	149021679																																			$11.22	$15.64	$1.32		$3.10					$15.64	$20.06		Resi Surchg
8	149021680																																			$31.36	$34.26	$2.90							$34.26	$37.16		Addl Handling Weig
9	149178347																																			$249.18	$312.35
10	149021681																																			$10.84	$11.84	$1.00							$11.84	$12.84
11	149021682																																			$30.82	$33.67	$2.85							$33.67	$36.52
12	149021683																																			$28.82	$35.53	$3.01			$3.70				$35.53	$42.24
13	149021684																																			$14.28	$15.60	$1.32							$15.60	$16.92
14	149021685																																			$33.63	$36.74	$3.11							$36.74	$39.85
15	149021686																																			$50.64	$55.32	$4.68							$55.32	$60.00
16	149021687																																			$11.22	$12.26	$1.04							$12.26	$13.30
17	149021688																																			$9.90	$10.82	$0.92							$10.82	$11.74
18	149021689																																			$9.22	$10.07	$0.85							$10.07	$10.92
19	149021690																																			$10.63	$15.00	$1.27		$3.10					$15.00	$19.37
Combined

Cell Formulas
Range	Formula
AL2:AR19	AL2	=IFNA(INDEX(ACCFEESTBL[Rate],MATCH($A2&AL$1,INDEX(ACCFEESTBL[shipment_id(4)]&ACCFEESTBL[description(50)],),0)),"")
AS2:AS19	AS2	=SUMIFS(ACCFEESTBL[Rate],ACCFEESTBL[shipment_id(4)],A2)
AT2:AT19	AT2	=SUM(Combined!$AL2:$AS2)

Cells with Data Validation
Cell	Allow	Criteria
AL1:AR1	List	=$AV$2:$AV$8

 

[snuffnchess]

Thank you VERY MUCH!!!!!

 

[RasGhul]

Glad we could help

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with Index/Match
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.