Comparing one lot of data against another

[babypink2807]

Good afternoon

Hope someone can help. I have two workbooks that lists the following, one list is last week and one list is this week. I need to find out from this weeks new list, which rows are not on the previous weeks list. I have on an average 3000 rows each week. I would like the new sheet to identify those rows that are NOT on the previous weeks list if its possible. I have watched various youtube videos but cannot seem to find the answer to it. I did conditional formatting by merging them but I ended up having to delete each individual row that was a duplicate. I need to see the NEW rows because I have to then add them to another database.

Serial Number 123456
Date Of Sale 12/07/2021
Machine Model KW 6.0/8

The above data if it matches in the previous sheet workbook great. If the above machine is not on the previous weeks list then I need Excel to indicate this is NOT on the list

Many thanks in advance

 

[alansidman]

I would do it with Power Query. Bring both tables into the PQ Editor. Join the two tables with an Anti join so that it shows only what is in this weeks table and not last weeks. Close and Load to your excel sheet. If you would like a demo, upload some sample data from both sheets employing XL2BB function for this site.

 

[babypink2807]

I would do it with Power Query. Bring both tables into the PQ Editor. Join the two tables with an Anti join so that it shows only what is in this weeks table and not last weeks. Close and Load to your excel sheet. If you would like a demo, upload some sample data from both sheets employing XL2BB function for this site.

i have never used Power Query Editor at all, not sure it is within my capability being novice

 

[alansidman]

Look at the link in my signature block to learn more about PQ. Also, it would be very helpful if you updated your profile to indicate which version of Excel you are using. Newer versions have more solution options and it is helpful to know when suggesting responses.

 

[babypink2807]

Update got so far and the "join" is missing option under merge? Ihave updated my profile I am using office 2016. I have very basic knowledge of Excel hence my asking. Currently still watching videos to assist, however it appear the Join part is missing

 

[alansidman]

Ok. Built two tables

Book7
	A	B	C
1	Serial Nr	Date of Sale	Machine
2	123456	7/14/2021	ABC
3	345678	7/14/2021	XYZ
Sheet1

Book7
	A	B	C
1	Serial Nr	Date of Sale	Machine
2	123456	7/14/2021	ABC
3	234567	7/21/2021	DEF
Sheet2

Result of anti join

Book7
	A	B	C
2	Table3.Serial Nr	Table3.Date of Sale	Table3.Machine
3	234567	7/21/2021	DEF
Merge1

Here is the Mcode for joining the two tables.

let
    Source = Table.NestedJoin(Table1, {"Serial Nr"}, Table3, {"Serial Nr"}, "Table3", JoinKind.RightAnti),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Serial Nr", "Date of Sale", "Machine"}, {"Table3.Serial Nr", "Table3.Date of Sale", "Table3.Machine"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table3",{"Table3.Serial Nr", "Table3.Date of Sale", "Table3.Machine"})
in
    #"Removed Other Columns"

 

[babypink2807]

Thanks for your advice, sadly this is way beyond me, I dont even know

a) what an Mcode is
b) where to copy and paste the Mcode
c) coding is nothing I have ever done before

Will revert back to merging and deleting each line using conditional formatting this is beyond me.

 

[alansidman]

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.