Compare data between two files and shows the different values

[MKLAQ]

hello

I have two files the FILE1 representatives the stock and the FILE2 representatives the process purchasing and selling .
what I would match the columns A,B,C,D together are existed in FILE2 with the FILE1 about the values should compare COL is named NET this should the last column because this COL is a variable every month I issue a new entering then when compare the COL NET in FILE2 as in this case in my picture it should be COL J , finally the expected result also are variable should insert COLS (STOCK,NEGETIVE,POSITIVE,CASE) next to COL NET after matching between two files
keep in your mind (COL NET are a variable in location when you compare ) & also COLS (STOCK,NEGETIVE,POSITIVE,CASE) next to COL NET
note: this a simple data but my real data about 10000 rows just to understand my idea

if any body help with this complicated project I truly appreciate
FILE1

FILE1.xlsx
	A	B	C	D	E
1	CODE	DESCRIBE	MODEL	PRODUCT BY	QUANTITY
2	CAR-1000	BMW	2010	GER	200
3	CAR-1000	BMW	2012	GER	245
4	CAR-1000	BMW	2013	GER	34
5	CAR-1001	AUDI	2010	GER	234
6	CAR-1001	AUDI	2014	GER	1
7	CAR-1002	FIA	2011	IT	88
8	CAR-1002	FIA	2013	IT	24
9	CAR-1003	MER	2016	GER	56
10	CAR-1003	MER	2016	GER	77
STOCK

FILE2

FILE2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2						CA-1-2021			CA-2-2021
3	CODE	DESCRIBE	MODEL	PRODUCT BY	PURCHASE	SALE	NET	PURCHASE	SALE	NET	STOCK	NEGETIVE	POSITIVE	CASE
4	CAR-1000	BMW	2010	GER	220	20	200	200	10	190	200	-10		UNMATCHED
5		BMW	2012	GER	300	10	290	290	100	190	245	-55		UNMATCHED
6		BMW	2013	GER	34		34	34		34	34	-	-	MATCHED
7	LTT				554	30	524	524	110	414	479	-65	-
8	CAR-1001	AUDI	2010	GER	230		230	230		230	234	-4	-	UNMATCHED
9		AUDI	2014	GER	1		1	1		1	1	-	-	MATCHED
10	LTT				231	0	231	231	0	231	235	-4	-
11	CAR-1002	FIA	2011	IT	100	22	78	120	20	100	88	-	12	UNMATCHED
12		FIA	2013	IT	48	24	24	60	10	50	24	-	26	UNMATCHED
13	LTT				148	46	102	180	30	150	112		38
14	CAR-1003	MER	2016	GER	100	50	50	100	10	90	56	-	34	UNMATCHED
15		MER	2016	GER	120	3	117	117		117	77	-	40	UNMATCHED
16	LTT				220	53	167	217	10	207	133	-	74
PUR

Cell Formulas
Range	Formula
E7:J7	E7	=SUM(E4:E6)
E16:J16,E13:J13,E10:J10	E10	=SUM(E8:E9)
J14:J15,J11:J12,J8:J9,J4:J6,G14:G15,G11:G12,G8:G9,G4:G6	G4	=E4-F4

 

[Joe4]

I find that when you are trying to match two different files/lists on multiple fields (like the 4 you are trying to match on), relational database programs, such as Microsoft Access do a much better job than Excel, as that is what they were defined (what you are describing is actually a relational database problem).

You would just create a query in Access between the two tables matching on those 4 fields. Depending on what you need, you may have up to 3 queries, if you may have records in one list that do not appear in the other. The 3 queries would be:
1. An unmatched query from List A to List B (to show all 4 field combinations that appear in List A that are not found on List B).
2. An unmatched query from List B to List A (to show all 4 field combinations that appear in List B that are not found on List A).
3. A matched query between the two Lists (to show all records where there are matching records on both Lists A and B), and a calculated field showing the difference in the numeric field values between the two lists.

If you only the situation where you could have 4 field combinatioins that appear in List A that do not appear in List B, but never the other way around (i.e. you cannot have 4 field combinations on List B that do not appear on List A), then you could get away with one single query, if you do a Left Join from List A to List B.

Note that I believe you can also do this sort of thing in Excel using "Power Query". I, myself, have not used it, so I cannot advise you how to set that up. But that is a possibility for doing this sort of thing in Excel.

 

[MKLAQ]

thanks Joe for your advise actually I know my request is complicated and hard but I used the excel and I know it's possible do that by vba excel just experts can do that so I hope from the experts see my post do to that

 

[Joe4]

thanks Joe for your advise actually I know my request is complicated and hard but I used the excel and I know it's possible do that by vba excel just experts can do that so I hope from the experts see my post do to that

My suggestion would be to first consider using Power Query (which is a part of Excel), as it will probably more efficient that the VBA code it would take to do this.

 

[MKLAQ]

actually I have not used PQ before if you have idea how do that I appreciate that

 

[Joe4]

There are some informative stickies over in the Power BI forum, including this one here: What is Power BI

If you have any specific questions about how to do things in Power BI, that forum is the best place to post those questions.

 

[alansidman]

Here is how I would do it with Power Query.
1. From your data tab, select Get and Transform and then select from Table/Range and bring the first table into the PQ editor.
2. Close and Load this table to Connection only.
3. Repeat step 1 for the second table.
4. Apply this Mcode to the second table to allow you to align/merge/join it to the first table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE", type text}, {"DESCRIBE", type text}, {"MODEL", Int64.Type}, {"PRODUCT BY", type text}, {"PURCHASE", Int64.Type}, {"SALE", Int64.Type}, {"NET", Int64.Type}, {"PURCHASE2", Int64.Type}, {"SALE3", Int64.Type}, {"NET4", Int64.Type}, {"STOCK", Int64.Type}, {"NEGETIVE", type any}, {"POSITIVE ", type any}, {"CASE", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"CODE"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([CODE] <> "LTT"))
in
    #"Filtered Rows"

5. Now close and load the second table to a connection only
6. You will now join the two tables and here is the Mcode for that

let
    Source = Table.NestedJoin(Table1, {"CODE", "DESCRIBE", "MODEL", "PRODUCT BY"}, Table2, {"CODE", "DESCRIBE", "MODEL", "PRODUCT BY"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"PURCHASE", "SALE", "NET", "PURCHASE2", "SALE3", "NET4", "STOCK", "NEGETIVE", "POSITIVE ", "CASE"}, {"Table2.PURCHASE", "Table2.SALE", "Table2.NET", "Table2.PURCHASE2", "Table2.SALE3", "Table2.NET4", "Table2.STOCK", "Table2.NEGETIVE", "Table2.POSITIVE ", "Table2.CASE"})
in
    #"Expanded Table2"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Joe4]

Thanks for the assist, Alan!