2 files data manipulate in one click

[Vishal.jagani]

Hi Guys,
This is some twisted complicated for me (not for you guys I hope)Let me explain whole thing
In my Work I have to some process with two file everyday. for 45 different files
In past I have already post regarding little same and I got answer already from You guys(Narayan) but in that process I still need to make much better process then now.
Here :

I am getting new files everyday which data I need to process but before process new file I just need to process old file with data null.

What I expect : One Macro when I open that macro file, these both file should be open (First would be open Old file data and below that paste new file data i.e- If old files data range is A1:K2500 then new file data paste at A2501:K...) I need data from new file so don't do anything with new file data,
both files has same column fields : so compare with one specific column(ID)
and find duplicate ID and only delete rows which are duplicates in OLD files, So simple Need to delete only from Old file and Which are not duplicate please make Quantity filed value as "0"
Now result like : First all rows would be with quantity data "0" and rest data would be from new file
That's It, Its seems tricky and I explain much more so If I bother you then I am sorry.

Here I've attached sample file file A is Old file, File B is New file and File C is expected Output
Please find attached below
Thanks

 

[Vishal.jagani]

Any help would be appreciated

 

[Marc L]

Hi !

Like it's at beginner level using Excel functions, I let time to others to try …

Paste this code to output workbook with same old workbook headers :​

Sub Demo()
With ThisWorkbook:  ChDrive .Path:  ChDir .Path:  End With
   vOw = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select OLD workbook :")
If vOw = False Then Exit Sub
   vNw = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select NEW workbook :")
If vNw = False Then Exit Sub
Application.ScreenUpdating = False
   
 Set vNw = Workbooks.Open(vNw).Worksheets(1)
With vNw.Cells(1).CurrentRegion.Rows
    With .Item("2:" & .Count)
        AD = .Columns(1).Address(External:=True)
        VA = .Value
    End With
End With
   
With Workbooks.Open(vOw).Worksheets(1)
    .[K2].Formula = "=ISNA(MATCH(A2," & AD & ",0))"
    .Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[K1:K2], Sheet1.[A1:F1]
    .Parent.Close False:  vNw.Parent.Close:  Set vNw = Nothing
End With
   
With Sheet1.Cells(1).CurrentRegion.Rows
         If .Count > 1 Then .Item("2:" & .Count).Columns(3).Value = 0
    .Cells(.Count + 1, 1).Resize(UBound(VA), UBound(VA, 2)).Value = VA
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Vishal.jagani]

Hi !

Like it's at beginner level using Excel functions, I let time to others to try …

Paste this code to output workbook with same old workbook headers :​

Sub Demo()
With ThisWorkbook:  ChDrive .Path:  ChDir .Path:  End With
   vOw = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select OLD workbook :")
If vOw = False Then Exit Sub
   vNw = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select NEW workbook :")
If vNw = False Then Exit Sub
Application.ScreenUpdating = False
  
Set vNw = Workbooks.Open(vNw).Worksheets(1)
With vNw.Cells(1).CurrentRegion.Rows
    With .Item("2:" & .Count)
        AD = .Columns(1).Address(External:=True)
        VA = .Value
    End With
End With
  
With Workbooks.Open(vOw).Worksheets(1)
    .[K2].Formula = "=ISNA(MATCH(A2," & AD & ",0))"
    .Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[K1:K2], Sheet1.[A1:F1]
    .Parent.Close False:  vNw.Parent.Close:  Set vNw = Nothing
End With
  
With Sheet1.Cells(1).CurrentRegion.Rows
         If .Count > 1 Then .Item("2:" & .Count).Columns(3).Value = 0
    .Cells(.Count + 1, 1).Resize(UBound(VA), UBound(VA, 2)).Value = VA
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

Hey Marc
Its Appreciate thank you so much
sorry I really dont know that where I ahve to change path name
because its generating error

I have just change portion :
"Select OLD workbook :") to "Yesterday :")
"Select NEW workbook :") to "Today :")

Could you please tell me what to do

 

[Marc L]

Seems you don't Like my code …

GetOpenFilename method uses current path;
if you need to change it, just use ChDir statement like I did.
See in VBA inner help …

Or don't amend my code and just place output workbook
into same source files directory !
​