Converge 2 data centers with VBA

[evenyougreg]

Hi!

I've had great success in the past here with VBA macros so I thought I would try my luck again. I pasted some mini sheets as an example, hopefully I did it correctly...

I need to converge 2 spreadsheet given the following scenario:

Spreadsheet 1 entitled "total_siteA.xlsx" with a sheet called "listA" has a long list of network devices,

row 1 are the headers "hostname,operating system,location" and the list starts on line 2. And it goes on and on for about 4000 rows.

Spreadsheet 2 entitled "total_siteB.xlsx" with a sheet called "listB" has the same sort of setup,

same row 1 headers "hostname,operating system,location" and the list starts on line 2 for about 2000 rows roughly.

If there is a hostname match when comparing siteB to siteA, then do nothing and move on,

If siteB has a hostname that is not present on siteA, then copy over/insert the values as a new row and move on.

Simply put, siteB has stuff that siteA has, and I don't care about those, but if B has something new then copy/insert it over to A along with the values.

Hopefully that makes sense, and thanks in advance!!

total_siteA.xlsx
	A	B	C
1	hostname	operating system	location
2	l-ubu1	Ubuntu Linux (64-bit)	vcenter1
3	m-win1	Microsoft Windows Server 2016 (64-bit)	vcenter2
4	l-ubu2	Ubuntu Linux (64-bit)	vcenter1
5	l-rhel1	RedHat (64-bit)	vcenter1
6	l-ubu3	Ubuntu Linux (64-bit)	vcenter1
7	m-win2	Microsoft Windows Server 2016 (64-bit)	vcenter2
8	l-ubu4	Ubuntu Linux (64-bit)	vcenter2
9	m-win3	Microsoft Windows Server 2016 (64-bit)	vcenter2
10	m-win4	Microsoft Windows Server 2012 (64-bit)	vcenter2
11	l-rhel2	RedHat (64-bit)	vcenter2
12	l-rhel3	RedHat (64-bit)	vcenter2
13	m-win5	Microsoft Windows Server 2016 (64-bit)	vcenter2
14	m-win6	Microsoft Windows Server 2016 (64-bit)	vcenter2
15	l-ubu5	Ubuntu Linux (64-bit)	vcenter1
16	m-win7	Microsoft Windows Server 2016 (64-bit)	vcenter1
17	l-rhel4	RedHat (64-bit)	vcenter1
18	m-win8	Microsoft Windows Server 2016 (64-bit)	vcenter1
19	l-ubu6	Ubuntu Linux (64-bit)	vcenter1
20	l-ubu7	Ubuntu Linux (64-bit)	vcenter1
21	m-win9	Microsoft Windows Server 2019 (64-bit)	vcenter1
22	m-win10	Microsoft Windows Server 2019 (64-bit)	vcenter2
23	l-ubu8	Ubuntu Linux (64-bit)	vcenter2
24	m-win11	Microsoft Windows Server 2016 (64-bit)	vcenter2
25	l-ubu9	Ubuntu Linux (64-bit)	vcenter2
26	m-win12	Microsoft Windows Server 2012 (64-bit)	vcenter1
27	l-rhel5	RedHat (64-bit)	vcenter1
28	l-ubu10	Ubuntu Linux (64-bit)	vcenter2
listA

total_siteB.xlsx
	A	B	C
1	hostname	operating system	location
2	l-suse1	Suse 12 (64-bit)	nutanix
3	l-suse2	Suse 12 (64-bit)	nutanix
4	l-suse3	Suse 12 (64-bit)	nutanix
5	l-rhel2	RedHat (64-bit)	nutanix
6	l-ubu9	Ubuntu Linux (64-bit)	nutanix
7	l-suse4	Suse 12 (64-bit)	nutanix
8	l-suse5	Suse 12 (64-bit)	nutanix
9	l-suse6	Suse 12 (64-bit)	nutanix
10	l-ubu10	Ubuntu Linux (64-bit)	nutanix
11	m-win5	Microsoft Windows Server 2016 (64-bit)	nutanix
listB

 

[DanteAmor]

Try the following macro. You can put it in a third book. The books with the lists must be open.

Sub checklist()
  Dim dic As Object
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, lr As Long
  Dim c As Range, rng As Range
  
  Set sh1 = Workbooks("total_siteA.xlsx").Sheets("listA")
  Set sh2 = Workbooks("total_siteB.xlsx").Sheets("listB")
  Set dic = CreateObject("Scripting.Dictionary")
  
  lr = sh1.Range("A" & Rows.Count).End(3).Row
  For Each c In sh1.Range("A2", sh1.Range("A" & lr))
    dic(c.Value) = Empty
  Next
  
  For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(3))
    If Not dic.exists(c.Value) Then
      If rng Is Nothing Then Set rng = c Else Set rng = Union(rng, c)
    End If
  Next
  If Not rng Is Nothing Then rng.EntireRow.Copy sh1.Range("A" & lr + 1)
End Sub

 

[evenyougreg]

This is exactly what I needed. Thank you!!!!!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[evenyougreg]

@DanteAmor would you be able to help with 1 more? Not sure if I should open up a new thread or not?

In this example file, sheet1 is called List and sheet 2 is called VMs.

If there is a "Hostname" match between the 2 sheets, I would like to copy the "OS" cell and the "Env" cell over from "VMs" to the "List" sheet. If no match is found then it would be cool if something like "not found" was to fill the empty cell.

example.xlsx
	A	B	C	D	E	F
1	Hostname	VM/Physical	Power State	OS	Location	Env
2	l-centtest01	VM	on		West
3	l-ubu01	VM	on		East
4	l-ubu02	VM	on		West
5	l-webapp01	VM	on		East
6	l-webapp02	VM	on		East
7	m-iisapp01	Physical	on		West
8	m-iisapp02	Physical	on		East
9	m-poc01	VM	on		East
10	m-poc02	VM	on		West
11	m-sqldb01	Physical	on		West
12	m-dsc01	VM	on		East
13	m-dsc02	VM	on		West
List

example.xlsx
	A	B	C
1	Hostname	OS	Env
2	l-centtest01	CentOS 7	prod
3	l-ubu01	Ubuntu 20.04	prod
4	l-ubu02	Ubuntu 20.04	test
5	l-webapp01	RHEL 7	prod
6	l-webapp02	RHEL 7	prod
7	m-iisapp01	Windows-2012ServerR2	prod
8	m-iisapp02	Windows-2012ServerR2	prod
9	m-poc01	Windows-2019Server	test
10	m-poc02	Windows-2019Server	test
11	m-sqldb01	Windows-2016Server	prod
12	m-sqldbtest	Windows-2016Server	test
VMs

 

[DanteAmor]

The macro is different, you must create a new thread.

 

[evenyougreg]

I posted a new thread, if you're able to assist I appreciate it, if not thanks for your help anyhow.

Copy over cells from different sheet2 if name match

In this example file, sheet1 is called List and sheet 2 is called VMs. If there is a "Hostname" match between the 2 sheets, I would like to copy the "OS" cell and the "Env" cell over from "VMs" to the "List" sheet. If no match is found then it would be cool if something like "not found" was to...

www.mrexcel.com