Copy cell text from E and F on Book1 over to Book2 if a string match is found in column A?

[evenyougreg]

I'll try to explain this as concisely as I can, and please interject if something doesn't make sense.


So I have an inventory spreadsheet (Book1) of server data that I generate every month, but there are a few columns that get updated manually because the information changes sometimes. What I need to do is copy over that updated info to the next newly generated spreadsheet (Book2) at the end of the month if the hostnames in column A line up.

My pseudo-code thinking is:

If NEW inventory, call it "October" finds an exact string match in OLD inventory "September" in terms of a matching hostname in column A, then copy over the raw text from columns E and F to replace whatever was there.

These inventories are in the thousands, and this is just a really bad representation of what I'm trying to get at. Some of these servers get decommissioned, and new ones are created regularly.

Old September inventory:

New October inventory:

Hope someone can help, not even sure where to begin with this one!

Thanks!

Greg

 

[JLGWhiz]

See if this works for you.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks("Book1").Sheets(1)
Set sh2 = Workbooks("Book2").Sheets(1)
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If fn.Offset(, 4).Value <> c.Offset(, 4).Value Or fn.Offset(, 5).Value <> c.Offset(, 5).Value Then
                    c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
                End If
            End If
        Set fn = Nothing
    Next
End Sub

 

[evenyougreg]

See if this works for you.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks("Book1").Sheets(1)
Set sh2 = Workbooks("Book2").Sheets(1)
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                If fn.Offset(, 4).Value <> c.Offset(, 4).Value Or fn.Offset(, 5).Value <> c.Offset(, 5).Value Then
                    c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
                End If
            End If
        Set fn = Nothing
    Next
End Sub

That worked perfectly with my demo spreadsheet!

My real life inventory needs to copy over E, and then H,I,J,K. Is it easy enough to edit that into the code?

Again, thank you very much for your help!

 

[JLGWhiz]

You should have included that in the original post. It is better to think your projects through and not have lagging "Oh, I forgot" after thoughts. Some responders will tell you to start a new thread. But since you are ne to the forum, modify this section as shown below.

 If fn.Offset(, 4).Value <> c.Offset(, 4).Value Or fn.Offset(, 5).Value <> c.Offset(, 5).Value Then
        c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
        c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)
End If

 

[evenyougreg]

You should have included that in the original post. It is better to think your projects through and not have lagging "Oh, I forgot" after thoughts. Some responders will tell you to start a new thread. But since you are ne to the forum, modify this section as shown below.

 If fn.Offset(, 4).Value <> c.Offset(, 4).Value Or fn.Offset(, 5).Value <> c.Offset(, 5).Value Then
        c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
        c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)
End If

Thanks for the feedback. Sometimes on these forums I am asked for more detail, and sometimes less, so I'm never really sure.

This works on my revised demo example, buuuuut my final "Oh, I forgot" moment is that my inventories are not on a generic Sheet1 and so it fails to run on my real life spreadsheet, the exact files and their respective renamed sheets are:

ESXi_old.xlsx - inventory
ESXi_new.xlsx - inventory

They are the second tab out of 4, if that makes a difference.

Can I venmo you for a virtual coffee or something? I wish there was some way I could give back.

 

[JLGWhiz]

a

nd their respective renamed sheets are:

ESXi_old.xlsx - inventory
ESXi_new.xlsx - inventory

In the Set statements for the sheets, just replace the '1' in parentheses with the actual sheet names enclosed in quotation marks. eg. Sheets("Data").
regards, JLG

 

[evenyougreg]

VBA Code:

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks("Book1").Sheets(1)
Set sh2 = Workbo

a

In the Set statements for the sheets, just replace the '1' in parentheses with the actual sheet names enclosed in quotation marks. eg. Sheets("Data").
regards, JLG

Ok, I did and it's chugging along now but it's only copying over maybe 10% of everything it should?

 

[JLGWhiz]

The code is only looking for changes in columns E and F for items where a host in column A workbook(1) is equal to host in column A for workbook(2).
Is there more than one occurrence of a host in either worksheet? The code assumes Column A of both to be a list containing only one occurrence of each host ID. What other columns besides E and F might be changed per host?

Would it be simpler to just copy the entire row for all host matches or would it also need to check the IP column for a match before copying to avoid creating duplicates?

 

[evenyougreg]

The code is only looking for changes in columns E and F for items where a host in column A workbook(1) is equal to host in column A for workbook(2).
Is there more than one occurrence of a host in either worksheet? The code assumes Column A of both to be a list containing only one occurrence of each host ID. What other columns besides E and F might be changed per host?

Would it be simpler to just copy the entire row for all host matches or would it also need to check the IP column for a match before copying to avoid creating duplicates?

This is what worked, I took out the additional conditional statement. I think I just confused the whole thing by giving a demo explanation instead of the real thing, but in any case, thanks for your time and help on this.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set sh1 = Workbooks(" ESXi_old.xlsx ").Sheets("inventory")
Set sh2 = Workbooks(" ESXi_new.xlsx ").Sheets("inventory")

    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                c.Offset(, 4).Resize(, 2).Copy fn.Offset(, 4)
                c.Offset(, 7).Resize(, 4).Copy fn.Offset(, 7)
            End If
        Set fn = Nothing
    Next
End Sub

 

[JLGWhiz]

This is what worked, I took out the additional conditional statement. I think I just confused the whole thing by giving a demo explanation instead of the real thing, but in any case, thanks for your time and help on this.

OK, you went with the simple option of just copying where the Host is a match.
Regards, JLG