Update and import records

Sharid · Apr 16, 2018

I have the following sheets

Sheet1 = Customer Record
Sheet5 = Dormant
Sheet6 = Update

1^st part of the code.

I need a code that will check column A in Sheet1 “Customer Records” from Row 3 down. These cells will have unique number and then see if they exist in Sheet6 “Update” Column A Row 2, if they do not exists then it will copy those rows into Sheet1 “Customer Record” under last row with data.

The range of data is columns A:H

2^nd part of the code

This is a bit more tricky and not sure if possible as it updates the record. Not sure if it should be on a separate button.

The code does the same but this time checks if Sheet6 “Update” has an updated record of what exists in sheet1 “Customer Record” is so it replaces it at the location it found it. So

Sheet 1 Customer Record
Row 8 (Update version of this record exists on Sheet 6 row 150)

Sheet 6 Update
Row 150 (This is updated record)

The updated is copied into Sheet1 Customer Record row 8 and NOT next blank row.
Before the update it copies and pastes the old record into the dormant sheet as a backup, sheet5 next blank row.

I was looking at unique ID being email or Tax number as names are too common, Problems are emails can be changed and Tax number might not be supplied upfront
and the Problem is I cannot issue a Unique ID upfront as data is submitted via the web.

Problems I can see is that the customer might not provide the unique ID up front (Tax No) and a few month later change everything, Name address and issue a tax no.
Thus creating a new record,

Any advice and help will do.

Sharid · Apr 16, 2018

Just for the record, it does not need to update if the same place, it can be the next free row, but it will need to copy the old record to Sheet6 dormant and delete the row in Sheet1 "Customer Record"

This is wrong

Sheet 1 Customer Record
Row 8 (Update version of this record exists on Sheet 6 row 150)

Sheet 6 Update
Row 150 (This is updated record)

The updated is copied into Sheet1 Customer Record row 8 and NOT next blank row.

this is important - Before the update it copies and pastes the old record into the dormant sheet as a backup, sheet5 next blank row.

Sharid · Apr 16, 2018

Here is some data, I hope it helps. This is the first part of the code that I need. It checks Column A the tax ID from sheet1 and checks if there is a copy in Sheet6, if not it copy and Pastes it over, in next blank row. Range A:H

Sheet 1 "Customer Records" Has the following 3 records

Doe

Smith

<tbody>
[TD="class: xl67"] Recipient Tax ID
[/TD]
[TD="class: xl68, width: 97"] Name 1
[/TD]
[TD="class: xl68, width: 78"] Name 2
[/TD]
[TD="class: xl68, width: 104"] Address
[/TD]
[TD="class: xl68, width: 179"] City
[/TD]
[TD="class: xl68, width: 87"] State [/TD]
[TD="class: xl68, width: 64"] Zip
[/TD]

[TD="class: xl65"]777-55-8888[/TD]
[TD="class: xl65"]Robert[/TD]
[TD="class: xl66"]Doe[/TD]
[TD="class: xl66"]3 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G1[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl65"]12345[/TD]

[TD="class: xl65"]777-55-8889[/TD]
[TD="class: xl65"]John[/TD]

[TD="class: xl66"]4 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G2[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]23456[/TD]

[TD="class: xl65"]777-55-8890[/TD]
[TD="class: xl65"]Jane[/TD]

[TD="class: xl66"]5 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G3[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]34567[/TD]

[TD="class: xl65"][/TD]

</tbody>

Sheet6 "Update" Has the following

Doe

Smith

Smith

<tbody>
[TD="class: xl67"] Recipient Tax ID
[/TD]
[TD="class: xl68, width: 97"] Name 1 [/TD]
[TD="class: xl68, width: 78"] Name 2 [/TD]
[TD="class: xl68, width: 104"] Address [/TD]
[TD="class: xl68, width: 179"] City [/TD]
[TD="class: xl68, width: 87"] State [/TD]
[TD="class: xl68, width: 64"] Zip [/TD]

[TD="class: xl65"]777-55-8888[/TD]
[TD="class: xl65"]Robert[/TD]
[TD="class: xl66"]Doe[/TD]
[TD="class: xl66"]3 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G1[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl65"]12345[/TD]

[TD="class: xl65"]777-55-8889[/TD]
[TD="class: xl65"]John[/TD]

[TD="class: xl66"]4 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G2[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]23456[/TD]

[TD="class: xl65"]777-55-8890[/TD]
[TD="class: xl65"]Jane[/TD]

[TD="class: xl66"]5 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G3[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]34567[/TD]

[TD="class: xl65"] 777-55-8891
[/TD]
[TD="class: xl65"] John [/TD]

[TD="class: xl66"] 8 Eden Park Dr. [/TD]
[TD="class: xl65"] Cincinnati [/TD]
[TD="class: xl65"] OH [/TD]
[TD="class: xl65"] 45678
[/TD]

</tbody>

Since John Smith is new it is imported (Copy and Paste ) from Sheet6 to sheet1, so now it looks like this, SHEET6 still look like the above.

Doe

Smith

John

<tbody>
[TD="class: xl67"] Recipient Tax ID
[/TD]
[TD="class: xl68, width: 97"] Name 1
[/TD]
[TD="class: xl68, width: 78"] Name 2
[/TD]
[TD="class: xl68, width: 104"] Address
[/TD]
[TD="class: xl68, width: 179"] City
[/TD]
[TD="class: xl68, width: 87"] State
[/TD]
[TD="class: xl68, width: 64"] Zip
[/TD]

[TD="class: xl65"]777-55-8888[/TD]
[TD="class: xl65"]Robert[/TD]
[TD="class: xl66"]Doe[/TD]
[TD="class: xl66"]3 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G1[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl65"]12345[/TD]

[TD="class: xl65"]777-55-8889[/TD]
[TD="class: xl65"]John[/TD]

[TD="class: xl66"]4 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G2[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]23456[/TD]

[TD="class: xl65"]777-55-8890[/TD]
[TD="class: xl65"]Jane [/TD]

[TD="class: xl66"]5 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G3[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]34567[/TD]

[TD="class: xl65"]777-55-8891[/TD]
[TD="class: xl65"]John[/TD]

[TD="class: xl66"]8 Eden Park Dr.[/TD]
[TD="class: xl65"]Cincinnati[/TD]
[TD="class: xl65"]OH[/TD]
[TD="class: xl65"]45678[/TD]

</tbody>

Second part of the code

If the record is live, but needs updating

Sheet1 "Customer Record"

Doe

Smith

<tbody>
[TD="class: xl67"] Recipient Tax ID
[/TD]
[TD="class: xl68, width: 97"] Name 1 [/TD]
[TD="class: xl68, width: 78"] Name 2 [/TD]
[TD="class: xl68, width: 104"] Address [/TD]
[TD="class: xl68, width: 179"] City [/TD]
[TD="class: xl68, width: 87"] State [/TD]
[TD="class: xl68, width: 64"] Zip [/TD]

[TD="class: xl65"]777-55-8888[/TD]
[TD="class: xl65"]Robert [/TD]
[TD="class: xl66"]Doe[/TD]
[TD="class: xl66"]3 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G1[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl65"]12345[/TD]

[TD="class: xl65"]777-55-8889[/TD]
[TD="class: xl65"]John [/TD]

[TD="class: xl66"]4 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G2[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]23456[/TD]

[TD="class: xl65"] 777-55-8890 [/TD]
[TD="class: xl65"]Jane[/TD]

[TD="class: xl66"]5 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G3[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]34567[/TD]

[TD="class: xl65"]777-55-8891[/TD]
[TD="class: xl65"]John[/TD]

[TD="class: xl66"]8 Eden Park Dr.
[/TD]
[TD="class: xl65"]Cincinnati
[/TD]
[TD="class: xl65"]OH[/TD]
[TD="class: xl65"]45678[/TD]

</tbody>

Sheet6 "Update"

Doe

Smith

Doe

<tbody>
[TD="class: xl67"]Recipient Tax ID[/TD]
[TD="class: xl68, width: 97"]Name 1[/TD]
[TD="class: xl68, width: 78"]Name 2[/TD]
[TD="class: xl68, width: 104"]Address[/TD]
[TD="class: xl68, width: 179"]City[/TD]
[TD="class: xl68, width: 87"]State[/TD]
[TD="class: xl68, width: 64"]Zip[/TD]

[TD="class: xl65"]777-55-8888[/TD]
[TD="class: xl65"]Robert[/TD]
[TD="class: xl66"]Doe[/TD]
[TD="class: xl66"]3 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G1[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl65"]12345[/TD]

[TD="class: xl65"]777-55-8889[/TD]
[TD="class: xl65"]John[/TD]

[TD="class: xl66"]4 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G2[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl70"]23456[/TD]

[TD="class: xl65"]777-55-8891[/TD]
[TD="class: xl65"]John [/TD]

[TD="class: xl66"]8 Eden Park Dr.[/TD]
[TD="class: xl65"]Cininnati
[/TD]
[TD="class: xl65"]OH[/TD]
[TD="class: xl70"]45678[/TD]

[TD="class: xl65"] 777-55-8890 [/TD]
[TD="class: xl65"]Jane [/TD]

[TD="class: xl66"]5 Home Road[/TD]
[TD="class: xl65"]Toronto Ontario MCG 2G3[/TD]
[TD="class: xl65"]NH[/TD]
[TD="class: xl65"]34567[/TD]

</tbody>

Jane has changed here name from Smith to Doe and record in sheet1 has been updated. With TAX ID it should be easy to find the record, problem will arise if this has not been supplied upfront.

Old Jane Smith record A:H has been copied to sheet6 "Dormant" and deleted from Sheet1 "Customer Record"

Sharid · Apr 17, 2018

Hi

I'm still stuck on this one, if anyone can help. Even if it is the first part of the code

Fluff · Apr 17, 2018

This should do the 1st part

Code:

Sub UpdateRecord()

   Dim Cl As Range
   Dim Itm As Variant
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("Update")
   Set Ws2 = Sheets("Customer records")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("A3", Ws1.Range("A" & Rows.count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Resize(, 8)
      Next Cl
      For Each Cl In Ws2.Range("A3", Ws2.Range("A" & Rows.count).End(xlUp))
         If .exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      For Each Itm In .items
         Itm.Copy Ws2.Range("A" & Rows.count).End(xlUp).Offset(1)
      Next Itm
   End With
End Sub

Sharid · Apr 17, 2018

sorry posted before, I saw your code, so have removed my post

Sharid · Apr 17, 2018

Thank you Fluff you saved me again

Fluff · Apr 17, 2018

Glad to help & thanks for the feedback

Update and import records

Sharid

Well-known Member

Excel Facts

Sharid

Well-known Member

Sharid

Well-known Member

Sharid

Well-known Member

Fluff

MrExcel MVP, Moderator

Sharid

Well-known Member

Sharid

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Update and import records

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock