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

[Ti1164]

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!

 

[KasperC]

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

Best regards,
Kasper

 

[Ti1164]

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

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

 

[KasperC]

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

/Kasper

 

[KasperC]

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

/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.

 

[Ti1164]

/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.

 

[Ti1164]

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 Name	PO Number	Material	Date Received	Date Required
Frank	0001	Coca Cola	12/05/2023	23/07/2023
Frank	0001	Sprite	12/05/2023	23/07/2023

New Data to update existing data with material change

Customer Name	PO Number	Material	Date Received	Date Required
Frank	0001	Fanta	12/05/2023	23/07/2023
Frank	0001	Sprite	12/05/2023	23/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 Name	PO Number	Ref.	Material	Date received	Date Required
Frank	0001	1	Coca Cola	12/05/2023	23/07/2023
Frank	0001	2	Sprite	12/05/2023	23/07/2023
Bob	2345	1?	Mars bar	20/06/2023	14/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!!

 

[Ti1164]

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 Name	PO Number	Material	Date Received	Date Required
Frank	0001	Coca Cola	12/05/2023	23/07/2023
Frank	0001	Sprite	12/05/2023	23/07/2023

New Data to update existing data with material change

Customer Name	PO Number	Material	Date Received	Date Required
Frank	0001	Fanta	12/05/2023	23/07/2023
Frank	0001	Sprite	12/05/2023	23/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 Name	PO Number	Ref.	Material	Date received	Date Required
Frank	0001	1	Coca Cola	12/05/2023	23/07/2023
Frank	0001	2	Sprite	12/05/2023	23/07/2023
Bob	2345	1?	Mars bar	20/06/2023	14/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)?

 

[KasperC]

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.

 

[Ti1164]

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!!