Compare 2 columns in different tabs and add new values
In the attached file, there is the Wave 1 List tab and the New Data tab. Everyday, I extract a new report and will compare the IDs against the data in the Wave 1 List. If there are new IDs, I will add it by inserting a new row exactly where the number ID falls as its sorted by the ID column from smallest to largest.
1. In some cases, no new IDs to add; do nothing
2. If new IDs in the new data, add to the Wave 1 List based on how it's sorted with out deleting any of the existing ID data, format, color..
3. Highlight the newly added IDs row (from B to AU) so that I can fill in the other "data" details afterwards
Since I do this daily, I'm trying to see if this can be automated and take down from 2 hours to potentially 30 mins... thx
Re: Compare 2 columns in different tabs and add new values
In the attached file I loaded both tables into Power Query. Ran an anti join to determine which items were in the new file and not in the old. Mcode for this:
File attached for your analysis. Run time for this exercise. Less than 2 minutes. To run this daily. Save the file and update the changes to the file by replacing the existing tables with new ones but maintaining the existing table names. Run the Refresh button twice to update the output. To learn more about PQ, click on the links in my signature. Time savings will be enormous
Last edited by alansidman; 03-31-2020 at 11:57 AM.
Re: Compare 2 columns in different tabs and add new values
Not sure how this works. I wanted to keep the data as is in the Wave 1 List tab and only insert the new IDs from the "new data" into new rows in the Wave 1 List tab but sorted by ID... Once it inserts then new ID, i need it to highlight that row so I can fill in the details in that row...
Re: Compare 2 columns in different tabs and add new values
What I have provided you is what is new in your report that is not in the old report. Your next step would be to copy and paste the new items to the old ( a quick manual action) and then do what you need to add. My understanding of your issue was to find the new items and isolate them. This is what the PQ merger (anti join) does. There is no change to the original data. This extracts the data to a new file.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Sub Demo1() Dim C%, R&, S& Application.ScreenUpdating = False With Sheet1.[B3].CurrentRegion C = .Columns.Count R = .Rows(.Rows.Count).Row + 1 Sheet2.[D2].Formula = "=ISNA(MATCH(B4," & .Columns(1).Address(External:=True) & ",0))" End With With Sheet2.[B3].CurrentRegion .AdvancedFilter xlFilterInPlace, .Parent.[D1:D2], , True .Parent.[D2].Clear S = Application.Subtotal(102, .Columns(1)) If S Then .Offset(1).Copy Sheet1.Cells(R, 2) With Sheet1.Cells(R, 2).Resize(S, C) .Columns(1).HorizontalAlignment = xlCenter .Interior.ColorIndex = 19 End With Sheet1.[B3].CurrentRegion.Sort Sheet1.[B3], xlAscending, Header:=xlYes End If If .Parent.FilterMode Then .Parent.ShowAllData End With Application.ScreenUpdating = True End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 03-31-2020 at 02:13 PM.
Reason: optimization …
Re: Compare 2 columns in different tabs and add new values
Hi - I'm using the jindon vba code he provided. It works fine except that I forgot to mention that every time that I have to add a new column right next to the N column to add the new data to compare with previous dates. So when I tried the code, it's not working... probably cause it has more columns? I couldn't figure it out.
LinkBack URL
About LinkBacks
Register To Reply
Hi ! Try this …
Bookmarks