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]

its a great task for power query.

I could try helping out. but first

"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."

So ifI get correctly then:

those field names from both sheet must contain the same details

data:	End user, vendor-SKU
contract sheet:	End user, vendor-SKU

 

[mediumrare]

its a great task for power query.

I could try helping out. but first


So ifI get correctly then:

those field names from both sheet must contain the same details

data:	End user, vendor-SKU
contract sheet:	End user, vendor-SKU

I have not used Power Query before but am open to it. Would I need to learn or do anything first in order to do this?

The sheets would most likely contain the same details, if you mean the same spelling, etc. Unless I needed to create yet another sheet that says, essentially, "If it reads this, then it actually means this."

 

[Radoslaw Poprawski]

@mediumrare
no, its your description is kinda like double negative, instead of simple comparison.
Thats why Im asking which columns must contain the same values to compare.

If I'm getting this right, it would be simple job of loading both tables to power query. doing either:
left-outer join (all from 1st table matching from the second)
inner join (only matching records from both tables) (I might got the join name wrong here)

its all doable without any Mcode, it's simple click-to-do - all stuff is on the ribbon in the editor if I'm getting this right.

Also, a bit different way would be to use filter functin.

 

[mediumrare]

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.

those field names from both sheet must contain the same details

data:	End user, vendor-SKU
contract sheet:	End user, vendor-SKU

The data I want is for all end users with a contract for certain products at a certain price. Each contract can include multiple products. Since multiple contracts might have the same product, I would need to make sure that the product and its correct price as stated on the contract follows the contract end user--not the product.

I used "end-user" and "vendor-SKU" on each sheet to reflect that association. Those columns must contain the same values to compare.

Is that clearer?

@mediumrare
no, its your description is kinda like double negative, instead of simple comparison.
Thats why Im asking which columns must contain the same values to compare.

If I'm getting this right, it would be simple job of loading both tables to power query. doing either:
left-outer join (all from 1st table matching from the second)
inner join (only matching records from both tables) (I might got the join name wrong here)

its all doable without any Mcode, it's simple click-to-do - all stuff is on the ribbon in the editor if I'm getting this right.

Also, a bit different way would be to use filter functin.

Okay, so I'll need to try this out and see if I can follow what you're suggesting.

 

[Radoslaw Poprawski]

yes it is.
Give me some time pls. its late here in Poland

 

[Radoslaw Poprawski]

ok, ur sample data is wrong
in data: vendorproduct001
in contract: vendorprod001

they are not the same for PQ.
ill manually change prod to product to fit the merge.

ill post the solution, but you must find out if this is normal or its just hastly prepared data.


//EDIT

Here you go:

Data & contracts table with 3 join kinds.xlsx

Microsoft Excel Workbook

1drv.ms

Let me know if any of those joins suits ur needs.

 

[mediumrare]

ok, ur sample data is wrong
in data: vendorproduct001
in contract: vendorprod001

they are not the same for PQ.
ill manually change prod to product to fit the merge.

ill post the solution, but you must find out if this is normal or its just hastly prepared data.

That was a mistake on my part. I was (as you put it, hastily) de-identifying the data in order to post it publicly.

 

[Radoslaw Poprawski]

no worries.
I shared a file so do please download and let me know if that is it.

the process is simple:
ALT, A, PT - load both tables to PQ. (u must close and load first one, then do other one.

open the bar at right side.
ALT, H, I, down arrow, enter to open merging menu as new merge.
choose tables (order is important for the merge type. (i chose data first, contracts second.)
choose columns and pay close attention to numbers (there should be small grey number 1 and 2. the order of selecting columns MUST BE THE SAME for both tables.
done.
now go to far right, click the small button top right corner on the column where I'll see Table green text (don't click the text)
and choose the columns you wish to see in the comparison.

I left all columns on purpose since I did not know ur exact needs, but irrelevant columns may be deleted in PQ (but they will stay in source tables)
Then simply load the query and ur done.

 

[mediumrare]

no worries.
I shared a file so do please download and let me know if that is it.

the process is simple:
ALT, A, PT - load both tables to PQ. (u must close and load first one, then do other one.

open the bar at right side.
ALT, H, I, down arrow, enter to open merging menu as new merge.
choose tables (order is important for the merge type. (i chose data first, contracts second.)
choose columns and pay close attention to numbers (there should be small grey number 1 and 2. the order of selecting columns MUST BE THE SAME for both tables.
done.
now go to far right, click the small button top right corner on the column where I'll see Table green text (don't click the text)
and choose the columns you wish to see in the comparison.

I left all columns on purpose since I did not know ur exact needs, but irrelevant columns may be deleted in PQ (but they will stay in source tables)
Then simply load the query and ur done.

I'll give this a try in the morning!