filtering data from monthly report

[mediumrare]

Hello!
Each month I pull a sales report for a certain manufacturer. I would like to have a template sheet that searches and filters the raw data based on another sheet. So, three sheets total. The template, the reference, and the raw data.

I don't know the best way to show this, so I'm including three separate Mini Sheets. I feel embarrassed for asking, knowing that it's probably just a modification of things I've already been shown here. Nevertheless... here goes...

The raw data ("data") is in the same arrangement every month. Not all data is relevant for my report. I've tried to match the column headers to match every sheet for the sake of clarity.

_template_b0.1 deID.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	end-user-number	end-user	inv-number	vendor-SKU	product-desc	Vendor	UOM	inv-date	qty	product-price	inv-ext	ST-name	ST-add1	address	ST-ST	ST-ZIP	Last PO Cost
2	9174	end-user9174	P164041	vendorproduct001	desc001	vendorABC	CS	05/04/21	2	45.75	91.50	text	text	text	text	text	27.7300
3	9174	end-user9174	P164041	vendorproduct002	desc002	vendorABC	CS	05/04/21	2	29.00	58.00	text	text	text	text	text	18.3700
4	9174	end-user9174	461312	vendorproduct002	desc002	vendorABC	CS	05/10/21	4	23.32	93.28	text	text	text	text	text	18.3700
5	9174	end-user9174	P164323	vendorproduct003	desc003	vendorABC	CS	05/10/21	1	26.40	26.40	text	text	text	text	text	14.5500
6	9174	end-user9174	P164134	vendorproduct004	desc004	vendorABC	CS	05/06/21	1	85.88	85.88	text	text	text	text	text	52.0500
7	9174	end-user9174	462228	vendorproduct002	desc002	vendorABC	CS	05/18/21	6	29.00	174.00	text	text	text	text	text	18.3700
8	9174	end-user9174	462344	vendorproduct002	desc002	vendorABC	CS	05/19/21	5	27.50	137.50	text	text	text	text	text	18.3700
9	9174	end-user9174	462228	vendorproduct005	desc005	vendorABC	CS	05/18/21	12	22.64	271.68	text	text	text	text	text	14.1500
10	9174	end-user9174	460581	vendorproduct003	desc003	vendorABC	CS	05/03/21	8	22.15	177.20	text	text	text	text	text	14.5500
11	9174	end-user9174	462228	vendorproduct003	desc003	vendorABC	CS	05/18/21	8	26.40	211.20	text	text	text	text	text	14.5500
12	9174	end-user9174	462344	vendorproduct003	desc003	vendorABC	CS	05/19/21	10	22.15	221.50	text	text	text	text	text	14.5500
13	9174	end-user9174	461174	vendorproduct001	desc001	vendorABC	CS	05/06/21	2	45.75	91.50	text	text	text	text	text	27.7300
14	9174	end-user9174	458126	vendorproduct002	desc002	vendorABC	CS	03/29/21	5	29.00	145.00	text	text	text	text	text	18.3700
15	9174	end-user9174	458648	vendorproduct002	desc002	vendorABC	CS	04/05/21	5	29.00	145.00	text	text	text	text	text	18.3700
16	9174	end-user9174	459775	vendorproduct002	desc002	vendorABC	CS	04/19/21	4	29.00	116.00	text	text	text	text	text	18.3700
17	9174	end-user9174	460336	vendorproduct002	desc002	vendorABC	CS	04/27/21	4	29.00	116.00	text	text	text	text	text	18.3700
18	9174	end-user9174	461488	vendorproduct002	desc002	vendorABC	CS	05/14/21	8	29.00	232.00	text	text	text	text	text	18.3700
19	9174	end-user9174	462611	vendorproduct002	desc002	vendorABC	CS	05/21/21	2	29.00	58.00	text	text	text	text	text	18.3700
20	9174	end-user9174	463145	vendorproduct002	desc002	vendorABC	CS	05/28/21	4	29.00	116.00	text	text	text	text	text	18.3700
21	9174	end-user9174	459772	vendorproduct001	desc001	vendorABC	CS	04/19/21	10	33.53	335.30	text	text	text	text	text	27.7300
22	9174	end-user9174	459777	vendorproduct001	desc001	vendorABC	CS	04/19/21	15	33.53	502.95	text	text	text	text	text	27.7300
23	9174	end-user9174	459778	vendorproduct001	desc001	vendorABC	CS	04/19/21	10	33.53	335.30	text	text	text	text	text	27.7300
24	9174	end-user9174	460160	vendorproduct001	desc001	vendorABC	CS	04/22/21	3	33.53	100.59	text	text	text	text	text	27.7300
25	9174	end-user9174	461573	vendorproduct001	desc001	vendorABC	CS	05/12/21	1	33.53	33.53	text	text	text	text	text	27.7300
26	1325	end-user1325	459770	vendorproduct002	desc002	vendorABC	CS	04/19/21	40	24.50	980.00	text	text	text	text	text	18.3700
data

I'd like to take the raw data and check it against this sheet ("contracts"). It would have the applicable, relevant data that I'm trying to extract. The relevant data needs to be from the same end-user and vendor-SKU. So if the end user does not match, it is irrelevant. If the end user matches but the vendor-SKU does not, it also is irrelevant.

_template_b0.1 deID.xlsx
	A	B	C	D	E	F	G	H	I
1	contract-number	end-user-number	end-user	address	vendor-SKU	contract-price	contract-qty	eff-date	exp-date
2	0380	9174	end-user9174	end-user9174 address	vendorprod001	$ 35.00	500	03/01/21	02/28/22
3	0380	9174	end-user9174	end-user9174 address	vendorprod002	$ 35.00	500	03/01/21	02/28/22
4	0380	9174	end-user9174	end-user9174 address	vendorprod003	$ 26.50	1,000	03/01/21	02/28/22
5	0380	9174	end-user9174	end-user9174 address	vendorprod004	$ 23.00	500	03/01/21	02/28/22
6	0380	9174	end-user9174	end-user9174 address	vendorprod005	$ 47.00	100	03/01/21	02/28/22
7	0380	9174	end-user9174	end-user9174 address	vendorprod006	$ 31.95	2,500	03/01/21	02/28/22
8	0380	9174	end-user9174	end-user9174 address	vendorprod007	$ 23.57	1,500	03/01/21	02/28/22
9	0380	9174	end-user9174	end-user9174 address	vendorprod008	$ 28.50	1,500	03/01/21	02/28/22
10	0380	9174	end-user9174	end-user9174 address	vendorprod009	$ 32.50	250	03/01/21	02/28/22
11	0380	9174	end-user9174	end-user9174 address	vendorprod010	$ 39.50	500	03/01/21	02/28/22
12	0380	9174	end-user9174	end-user9174 address	vendorprod011	$ 37.75	2,000	03/01/21	02/28/22
13	0380	9174	end-user9174	end-user9174 address	vendorprod012	$ 39.75	150	03/01/21	02/28/22
14	0380	9174	end-user9174	end-user9174 address	vendorprod013	$ 29.64	100	03/01/21	02/28/22
15	0380	9174	end-user9174	end-user9174 address	vendorprod014	$ 30.18	100	03/01/21	02/28/22
16	0380	9174	end-user9174	end-user9174 address	vendorprod015	$ 37.53	100	03/01/21	02/28/22
17	0380	9174	end-user9174	end-user9174 address	vendorprod016	$ 56.68	100	03/01/21	02/28/22
18	0380	9174	end-user9174	end-user9174 address	vendorprod017	$ 40.39	100	03/01/21	02/28/22
19	0380	9174	end-user9174	end-user9174 address	vendorprod018	$ 54.47	100	03/01/21	02/28/22
20	0380	9174	end-user9174	end-user9174 address	vendorprod019	$ 20.61	1,500	03/01/21	02/28/22
21	0380	9174	end-user9174	end-user9174 address	vendorprod020	$ 32.50	500	03/01/21	02/28/22
22	0380	9174	end-user9174	end-user9174 address	vendorprod021	$ 36.50	250	03/01/21	02/28/22
23	0380	9174	end-user9174	end-user9174 address	vendorprod022	$ 36.00	500	03/01/21	02/28/22
24	0380	9174	end-user9174	end-user9174 address	vendorprod023	$ 20.00	500	03/01/21	02/28/22
25	0380	9174	end-user9174	end-user9174 address	vendorprod024	$ 43.50	250	03/01/21	02/28/22
26	1985	1325	end-user1325	end-user1325 address	vendorprod025	$ 33.54	50	04/01/21	09/30/21
27	1985	1325	end-user1325	end-user1325 address	vendorprod026	$ 29.41	50	04/01/21	09/30/21
28	1985	1325	end-user1325	end-user1325 address	vendorprod011	$ 37.75	50	04/01/21	09/30/21
29	5573	8915	end-user8915	end-user8915 address	vendorprod027	$ 30.50	400	09/01/21	08/31/22
30	5573	8915	end-user8915	end-user8915 address	vendorprod028	$ 27.50	300	09/01/21	08/31/22
contracts

If the end user matches and the vendor-SKU matches, then the Template should be filled.

_template_b0.1 deID.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	contract-number	end-user	address	inv-date	inv-number	vendor-SKU	total-qty	uom	total-rebate	inv-price	contract-price
2									$ -
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
template

Cell Formulas
Range	Formula
I2	I2	=(J2-K2)*G2

I hope I am being clear and making sense for what I am trying to achieve.

 

[Radoslaw Poprawski]

I'll give this a try in the morning!

Gr8
go back up and download the file I shared.

 

[mediumrare]

Gr8
go back up and download the file I shared.

Finally had some time to go over this, and wanted to follow up and thank you for your help with this!