Comparing one lot of data against another

babypink2807

New Member
Joined
Jul 13, 2015
Messages
19
Office Version
  1. 2016
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,457
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 13, 2015
Messages
19
Office Version
  1. 2016
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,457
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 13, 2015
Messages
19
Office Version
  1. 2016

ADVERTISEMENT

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

Well-known Member
Joined
Feb 26, 2007
Messages
6,457
Office Version
  1. 365
Platform
  1. Windows
Ok. Built two tables
Book7
ABC
1Serial NrDate of SaleMachine
21234567/14/2021ABC
33456787/14/2021XYZ
Sheet1


Book7
ABC
1Serial NrDate of SaleMachine
21234567/14/2021ABC
32345677/21/2021DEF
Sheet2


Result of anti join

Book7
ABC
2Table3.Serial NrTable3.Date of SaleTable3.Machine
32345677/21/2021DEF
Merge1


Here is the Mcode for joining the two tables.

Power Query:
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

New Member
Joined
Jul 13, 2015
Messages
19
Office Version
  1. 2016
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,457
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,075
Messages
5,704,156
Members
421,330
Latest member
imdumb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top