Import Data to check if it exists and if not add to existing data

Ti1164

New Member
Joined
Jul 3, 2023
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi

Each week my company receives an exported orders spreadsheet from SAP. Basically I want to run a formula that will check if data exists in the existing orders spreadsheet, but i need the entire row to match, if one cell changes I would like it to change in the existing data. If data does not exist I would like it to add to the bottom.

Each customer has multiple orders and multiple products within, so with the code below from another forum, it would only return 1 line per customer instead of each order & material per customer.
I tried changing For Each Cl In Sws.Range("A2", Sws.Range("A" & Rows.Count).End(xlUp)) to say ("A:P" ... and also ("A" & "D" & "I" ... but neither of these alternatives worked.

FYI all blanks have been removed from the new data, and each customer is listed alongside it's corresponding orders and materials.

Sub UpdateData()

Dim Cl As Range
Dim Itm As Variant
Dim Dws As Worksheet
Dim Sws As Worksheet

Set Dws = Sheets("Data")
Set Sws = Sheets("New Data")
With CreateObject("scripting.dictionary")
For Each Cl In Sws.Range("A2", Sws.Range("A" & Rows.Count).End(xlUp))
If Not .exists(Cl.Value) Then .Add Cl.Value, Array(Cl.Offset(, 17).Value, Cl.Resize(, 18))
Next Cl
For Each Cl In Dws.Range("A2", Dws.Range("A" & Rows.Count).End(xlUp))
If .exists(Cl.Value) Then
If Not Cl.Offset(, 17).Value = .Item(Cl.Value)(0) Then
Cl.Resize(, 18).Value = .Item(Cl.Value)(1).Value
End If
.Remove Cl.Value
End If
Next Cl
If Not .Count > 0 Then Exit Sub
For Each Itm In .items
Dws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 18).Value = Itm(1).Value
Next Itm
End With

End Sub



Thank you for your help in advance!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi

Each week my company receives an exported orders spreadsheet from SAP. Basically I want to run a formula that will check if data exists in the existing orders spreadsheet, but i need the entire row to match, if one cell changes I would like it to change in the existing data. If data does not exist I would like it to add to the bottom.

Each customer has multiple orders and multiple products within, so with the code below from another forum, it would only return 1 line per customer instead of each order & material per customer.
I tried changing For Each Cl In Sws.Range("A2", Sws.Range("A" & Rows.Count).End(xlUp)) to say ("A:P" ... and also ("A" & "D" & "I" ... but neither of these alternatives worked.

FYI all blanks have been removed from the new data, and each customer is listed alongside it's corresponding orders and materials.

Sub UpdateData()

Dim Cl As Range
Dim Itm As Variant
Dim Dws As Worksheet
Dim Sws As Worksheet

Set Dws = Sheets("Data")
Set Sws = Sheets("New Data")
With CreateObject("scripting.dictionary")
For Each Cl In Sws.Range("A2", Sws.Range("A" & Rows.Count).End(xlUp))
If Not .exists(Cl.Value) Then .Add Cl.Value, Array(Cl.Offset(, 17).Value, Cl.Resize(, 18))
Next Cl
For Each Cl In Dws.Range("A2", Dws.Range("A" & Rows.Count).End(xlUp))
If .exists(Cl.Value) Then
If Not Cl.Offset(, 17).Value = .Item(Cl.Value)(0) Then
Cl.Resize(, 18).Value = .Item(Cl.Value)(1).Value
End If
.Remove Cl.Value
End If
Next Cl
If Not .Count > 0 Then Exit Sub
For Each Itm In .items
Dws.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 18).Value = Itm(1).Value
Next Itm
End With

End Sub



Thank you for your help in advance!


Hi,

I can try to help you build a script for this. I think it would be more efficient to use the Application.Match function in order to check if the row exists or not.
If you give me some more info on how your data looks (how many columns in each row) - and what you want to happen if there are no match, I can try to write a script :giggle:

Best regards,
Kasper
 
Upvote 0
Hi,

I can try to help you build a script for this. I think it would be more efficient to use the Application.Match function in order to check if the row exists or not.
If you give me some more info on how your data looks (how many columns in each row) - and what you want to happen if there are no match, I can try to write a script :giggle:

Best regards,
Kasper
Hi Kasper

Thank you that would be awesome if you can!

There are 16 columns (A:P) which are the headings - Customer name, PO, material, Date req, etc.

If there is no match, the data can be added to the bottom of the existing orders spreadsheet. If possible it would also be great if the cells could be highlighted just so staff can manually double check new and existing orders.

The tricky part is these orders may change, i.e date could be pushed back for delivery, product change etc. I'm thinking this might just be something we will have to manually cross check, i'm just worried it leaves room for error. If there's a way around this you can think of, that would be awesome, but if not it's no big deal.

Thanks again! I extremely appreciate it
 
Upvote 0
No worries.

If I understand you correctly, the order of the columns can change from dataset to dataset?

If so, can you give me all the header-names, in your preffered order - I'll make the script locate and sort them for you :giggle:

/Kasper
 
Upvote 0
No worries.

If I understand you correctly, the order of the columns can change from dataset to dataset?

If so, can you give me all the header-names, in your preffered order - I'll make the script locate and sort them for you :giggle:

/Kasper

/Edit
I won't need this info afterall - I'll assume in the script that the headers in your "Data" sheet are in the preferred order, and sort the "New Data" headers thereafter.
 
Upvote 0
The headings are the same in both sheets and won't change, it's more so the data that could change, for example:
Existing Data:
Customer NamePO NumberMaterialDate ReceivedDate Required
Frank0001Coca Cola12/05/202323/07/2023
Frank0001Sprite12/05/202323/07/2023

New Data to update existing data with material change
Customer NamePO NumberMaterialDate ReceivedDate Required
Frank0001Fanta12/05/202323/07/2023
Frank0001Sprite12/05/202323/07/2023

So multiple products will be within the 1 PO number, but the product could change or even the date required could be pushed back.

Would I maybe need excel to use the PO number and return a reference number per PO & Customer Name?

I.e
Customer NamePO NumberRef.MaterialDate receivedDate Required
Frank00011Coca Cola12/05/202323/07/2023
Frank00012Sprite12/05/202323/07/2023
Bob23451?Mars bar20/06/202314/07/2023

That way when I import the new data spreadsheet it can then match by finding PO number with ref. in existing data then return/update data

I hope im making sense, sorry it's been a few years since i've made a vba spreadsheet!!
 
Upvote 0
The headings are the same in both sheets and won't change, it's more so the data that could change, for example:
Existing Data:
Customer NamePO NumberMaterialDate ReceivedDate Required
Frank0001Coca Cola12/05/202323/07/2023
Frank0001Sprite12/05/202323/07/2023

New Data to update existing data with material change
Customer NamePO NumberMaterialDate ReceivedDate Required
Frank0001Fanta12/05/202323/07/2023
Frank0001Sprite12/05/202323/07/2023

So multiple products will be within the 1 PO number, but the product could change or even the date required could be pushed back.

Would I maybe need excel to use the PO number and return a reference number per PO & Customer Name?

I.e
Customer NamePO NumberRef.MaterialDate receivedDate Required
Frank00011Coca Cola12/05/202323/07/2023
Frank00012Sprite12/05/202323/07/2023
Bob23451?Mars bar20/06/202314/07/2023

That way when I import the new data spreadsheet it can then match by finding PO number with ref. in existing data then return/update data

I hope im making sense, sorry it's been a few years since i've made a vba spreadsheet!!

Just a quick update
I have added the reference column now and I used =COUNTIF(B$2:B2,B2) to achieve a sequence based on the order numbers per my example above.

So now would we be able to update all other cells in existing data based on the PO and ref number (B & C)?
 
Upvote 0
Just a quick update
I have added the reference column now and I used =COUNTIF(B$2:B2,B2) to achieve a sequence based on the order numbers per my example above.

So now would we be able to update all other cells in existing data based on the PO and ref number (B & C)?

You don't need to add a reference in the existing spreadsheet - I think we have to use an array (a collection of datapoints), and in there we can create a "ref. no" through vba.

I think I understand a bit more now.

So I'll have to ask a few q's:
- So what you really want to know is if the "new data" set contains the same customer and PO number as the "data" set - but with a change in any of the other columns?

- What would you like to happen if it finds the same customername + po, do you want the original line to be deleted and replaced, or just printed as a new line?
- Or would you like to collect these together in a new spreadsheet with old/new-lines linked together for followup purposes?

Hope that made sense. Sorry for taking a while to answer.
 
Upvote 0
So I'll have to ask a few q's:
- So what you really want to know is if the "new data" set contains the same customer and PO number as the "data" set - but with a change in any of the other columns?

Yes that's correct


- Or would you like to collect these together in a new spreadsheet with old/new-lines linked together for followup purposes?

Thank you for this idea, I'm thinking it would be a good idea to link and move to new worksheet only If data in other columns have changed. From there staff can manually delete the row we do not want to keep and I will add a button on that spreadsheet to then move the rows to existing data.
If it is an exact match then do nothing/next row.
If it does not exist then add row to existing data.


This is going to be awesome, thanks again!!
 
Upvote 0

Forum statistics

Threads
1,222,441
Messages
6,166,054
Members
452,010
Latest member
triangle3

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
Back
Top