Merge Duplicates and Delete Duplicate Values via VBA

R

radeon187

New Member
Joined
Mar 25, 2020
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon! First off, I would like to thank everyone that has been so helpful with all the questions I've asked. You guys rock! And to the folks that I haven't interacted with, I look forward in doing so!

Now down to business...I have a list of data that I want to merge the data across rows using the "VM ID" as the key column. I currently use a add-on that merges duplicates for the whole sheet by defining a key column (VM ID) and deletes any duplicate values for all of the columns except the VM, powerstate, capacity (GB) and Unit # and makes a copy of the original sheet. Then I have to run the add-on again on a duplicate sheet using the VM ID as keys, and selects to merge capacity (GB) and Unit # without deleting duplicates. I would like to have a VBA script that I can define on a per sheet basis since I will have a similar sheet with different data that I will need to perform the same actions on.

Here is what the source data looks like:
Merge_EX.xlsx
ABCDEFGHIJKLMNOPQ
1VMPowerstateDiskCapacity (GB)RawDisk ModeThinEagerly ScrubReservationLimitControllerLabelUnit #DatastoreRaw LUN IDRaw Comp. ModeVM ID
2Test-VM1poweredOffHard disk 180FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 00TEMPLATESvm-1794
3Test-VM1poweredOffHard disk 36FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 02TEMPLATESvm-1794
4Test-VM1poweredOffHard disk 220FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 01TEMPLATESvm-1794
5Test-VM2poweredOffHard disk 38FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 02TEMPLATESvm-441
6Test-VM2poweredOffHard disk 220FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 01TEMPLATESvm-441
7Test-VM2poweredOffHard disk 180FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 00TEMPLATESvm-441
8Test-VM3poweredOnHard disk 220FalsepersistentFalseFalse0-1VMware paravirtual SCSISCSI controller 01DS07vm-6955
9Test-VM3poweredOnHard disk 180FalsepersistentFalseFalse0-1VMware paravirtual SCSISCSI controller 00DS07vm-6955
10Test-VM3poweredOnHard disk 420FalsepersistentFalseFalse0-1VMware paravirtual SCSISCSI controller 02DS07vm-6955
Unmerged



End State of what I need it to look like:

Merge_EX.xlsx
ABCDEFGHIJKLMNOPQ
1VMPowerstateDiskCapacity (GB)RawDisk ModeThinEagerly ScrubReservationLimitControllerLabelUnit #DatastoreRaw LUN IDRaw Comp. ModeVM ID
2Test-VM1poweredOffHard disk 1;Hard disk 3;Hard disk 280;6;20FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 00;2;1TEMPLATESvm-1794
3Test-VM2poweredOffHard disk 3;Hard disk 2;Hard disk 18;20;80FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 02;1;0TEMPLATESvm-441
4Test-VM3poweredOnHard disk 2;Hard disk 1;Hard disk 420;80;20TRUE;FalsepersistentFalseFalse0-1VMware paravirtual SCSISCSI controller 01;0;2DS07vm-6955
Merged



Keep in mind, that the capacity (gb) and unit # will have duplicate values initially, which is why i exclude them on my first run of the add-on. Thanks in advance!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Give it a shot
VBA Code: 
Sub t()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
            If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ";" & .Cells(i, 3).Value
                .Cells(i - 1, 4) = .Cells(i - 1, 4).Value & ";" & .Cells(i, 4).Value
                .Cells(i - 1, 13) = .Cells(i - 1, 13).Value & ";" & Cells(i, 13).Value
                .Rows(i).Delete
            End If
        Next
        .UsedRange.Columns.AutoFit
    End With
End Sub
 
Upvote 0
JLGWhiz said:
Give it a shot
VBA Code: 
Sub t()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
            If .Cells(i, 1).Value = .Cells(i - 1, 1).Value Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ";" & .Cells(i, 3).Value
                .Cells(i - 1, 4) = .Cells(i - 1, 4).Value & ";" & .Cells(i, 4).Value
                .Cells(i - 1, 13) = .Cells(i - 1, 13).Value & ";" & Cells(i, 13).Value
                .Rows(i).Delete
            End If
        Next
        .UsedRange.Columns.AutoFit
    End With
End Sub
Click to expand...
Not sure if I need to modify your code above, but when I attempt to use it on a sheet with over 8,000 rows, its doesn't work. It'll run successfully, but duplicate values still exist in the larger test bed...
 
Upvote 0
radeon187 said:
Not sure if I need to modify your code above, but when I attempt to use it on a sheet with over 8,000 rows, its doesn't work. It'll run successfully, but duplicate values still exist in the larger test bed...
Click to expand...
Here is a larger subset of data. Let me know if this helps or if you need more clarity.

RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 -vdisk testing.xlsx
ABCDEFGHIJKLMNOPQR
2Test01dbaora003poweredOnHard disk 4104Truepersistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006bphysicalModevm-7425
3Test01dbaora003poweredOnHard disk 556Truepersistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006cphysicalModevm-7425
4Test01dbaora003poweredOnHard disk 632Truepersistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006dphysicalModevm-7425
5Test01dbaora003poweredOnHard disk 78Truepersistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006ephysicalModevm-7425
6Test01dbaora004poweredOnHard disk 4104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006bphysicalModevm-7728
7Test01dbaora004poweredOnHard disk 556Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006cphysicalModevm-7728
8Test01dbaora004poweredOnHard disk 632Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006dphysicalModevm-7728
9Test01dbaora004poweredOnHard disk 78Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006ephysicalModevm-7728
10Test01ilsora005poweredOnHard disk 4200Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008bphysicalModevm-898
11Test01ilsora005poweredOnHard disk 580Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008cphysicalModevm-898
12Test01ilsora005poweredOnHard disk 656Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008dphysicalModevm-898
13Test01ilsora005poweredOnHard disk 78Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008ephysicalModevm-898
14Test01ilsora006poweredOnHard disk 4200Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008bphysicalModevm-911
15Test01ilsora006poweredOnHard disk 580Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008cphysicalModevm-911
16Test01ilsora006poweredOnHard disk 656Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008dphysicalModevm-911
17Test01ilsora006poweredOnHard disk 78Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008ephysicalModevm-911
18Test01ilsora013poweredOnHard disk 4304Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-01454d43physicalModevm-6648
19Test01ilsora013poweredOnHard disk 5104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-01physicalModevm-6648
20Test01ilsora013poweredOnHard disk 656Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010065physicalModevm-6648
21Test01ilsora013poweredOnHard disk 78Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010066physicalModevm-6648
22Test01ilsora014poweredOnHard disk 4304Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-01454d43physicalModevm-6650
23Test01ilsora014poweredOnHard disk 5104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010064physicalModevm-6650
24Test01ilsora014poweredOnHard disk 656Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010065physicalModevm-6650
25Test01ilsora014poweredOnHard disk 78Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010066physicalModevm-6650
26Test01infsto001poweredOnHard disk 62,048Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingGEN-TST-01010269physicalModevm-261
27Test01inftst001poweredOnHard disk 8104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingTest01-UNT02-ora-CL01-ATA-DS-01010067physicalModevm-6944
28Test01inftst001poweredOnHard disk 956Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 12physicalSharingTest01-UNT02-ora-CL01-ATA-DS-01010068physicalModevm-6944
29Test01inftst001poweredOnHard disk 1032Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 13physicalSharingTest01-UNT02-ora-CL01-ATA-DS-01010069physicalModevm-6944
30Test01inftst001poweredOnHard disk 118Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 14physicalSharingTest01-UNT02-ora-CL01-ATA-DS-0101006aphysicalModevm-6944
31Test01-mpora140poweredOnHard disk 4104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008fphysicalModevm-969
32Test01-mpora140poweredOnHard disk 524Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010090physicalModevm-969
33Test01-mpora140poweredOnHard disk 616Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010091physicalModevm-969
34Test01-mpora140poweredOnHard disk 108Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 32physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010092physicalModevm-969
35Test01-mpora140poweredOnHard disk 7104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010093physicalModevm-969
36Test01-mpora140poweredOnHard disk 824Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 21physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010094physicalModevm-969
37Test01-mpora140poweredOnHard disk 916Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010095physicalModevm-969
38Test01-mpora141poweredOnHard disk 4104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008fphysicalModevm-980
39Test01-mpora141poweredOnHard disk 524Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010090physicalModevm-980
40Test01-mpora141poweredOnHard disk 616Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010091physicalModevm-980
41Test01-mpora141poweredOnHard disk 108Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 32physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010092physicalModevm-980
42Test01-mpora141poweredOnHard disk 7104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010093physicalModevm-980
43Test01-mpora141poweredOnHard disk 824Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 21physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010094physicalModevm-980
44Test01-mpora141poweredOnHard disk 916Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010095physicalModevm-980
45Test01-nboora22poweredOnHard disk 756Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010084physicalModevm-838
46Test01-nboora22poweredOnHard disk 832Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 21physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010085physicalModevm-838
47Test01-nboora22poweredOnHard disk 932Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010086physicalModevm-838
48Test01-nboora22poweredOnHard disk 456Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010087physicalModevm-838
49Test01-nboora22poweredOnHard disk 532Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010088physicalModevm-838
50Test01-nboora22poweredOnHard disk 632Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010089physicalModevm-838
51Test01-nboora22poweredOnHard disk 108Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 32physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008aphysicalModevm-838
52Test01-nboora23poweredOnHard disk 756Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010084physicalModevm-855
53Test01-nboora23poweredOnHard disk 832Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 21physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010085physicalModevm-855
54Test01-nboora23poweredOnHard disk 932Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010086physicalModevm-855
55Test01-nboora23poweredOnHard disk 456Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010087physicalModevm-855
56Test01-nboora23poweredOnHard disk 532Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010088physicalModevm-855
57Test01-nboora23poweredOnHard disk 632Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010089physicalModevm-855
58Test01-nboora23poweredOnHard disk 108Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 32physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008aphysicalModevm-855
59Test01-replvsq50poweredOnHard disk 1156Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 12physicalSharingora TST ATA-0101007dphysicalModevm-833
60Test01-replvsq50poweredOnHard disk 1232Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 13physicalSharingora TST ATA-0101007ephysicalModevm-833
61Test01-replvsq50poweredOnHard disk 1332Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 14physicalSharingora TST ATA-0101007fphysicalModevm-833
62Test01-replvsq50poweredOnHard disk 1456Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 15physicalSharingora TST ATA-01010080physicalModevm-833
63Test01-replvsq50poweredOnHard disk 1532Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 16physicalSharingora TST ATA-01010081physicalModevm-833
64Test01-replvsq50poweredOnHard disk 1632Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 18physicalSharingora TST ATA-01010082physicalModevm-833
65Test01-replvsq50poweredOnHard disk 178Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 19physicalSharingora TST ATA-01010083physicalModevm-833
Test Sheet





End Result Expected:

RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 -vdisk testing.xlsx
ABCDEFGHIJKLMNOPQR
1VMPowerstateDiskCapacity (GB)RawDisk ModeThinEagerly ScrubReservationLimitControllerLabelUnit #Shared BusDatastoreRaw LUN IDRaw Comp. ModeVM ID
2Test01dbaora003poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 7104;56;32;8Truepersistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006b;01006c;01006d;01006ephysicalModevm-7425
3Test01dbaora004poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 7104;56;32;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-0201006b;01006c;01006d;01006ephysicalModevm-7728
4Test01ilsora005poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 7200;80;56;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008b;01008c;01008d;01008ephysicalModevm-898
5Test01ilsora006poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 7200;80;56;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008b;01008c;01008d;01008ephysicalModevm-911
6Test01ilsora013poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 7304;104;56;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-01454d43;;010065;010066physicalModevm-6648
7Test01ilsora014poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 7304;104;56;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-01454d43;010064;010065;010066physicalModevm-6650
8Test01infsto001poweredOnHard disk 62,048Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingGEN-TST-01010269physicalModevm-261
9Test01inftst001poweredOnHard disk 8;Hard disk 9;Hard disk 10;Hard disk 11104;56;32;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11;2;3;4physicalSharingTest01-UNT02-ora-CL01-ATA-DS-01010067;010068;010069;01006aphysicalModevm-6944
10Test01-mpora140poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 10;Hard disk 7;Hard disk 8;Hard disk 9104;24;16;8;104;24;16Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;2;1;1;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008f;010090;010091;010092;010093;010094;010095physicalModevm-969
11Test01-mpora141poweredOnHard disk 4;Hard disk 5;Hard disk 6;Hard disk 10;Hard disk 7;Hard disk 8;Hard disk 9104;24;16;8;104;24;16Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 30;0;0;2;1;1;1physicalSharingTest01-ARRY-ora-TST-ATA-DS-0101008f;010090;010091;010092;010093;010094;010095physicalModevm-980
12Test01-nboora22poweredOnHard disk 7;Hard disk 8;Hard disk 9;Hard disk 4;Hard disk 5;Hard disk 6;Hard disk 1056;32;32;56;32;32;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 31;1;1;0;0;0;2physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010084;010085;010086;010087;010088;010089;01008aphysicalModevm-838
13Test01-nboora23poweredOnHard disk 7;Hard disk 8;Hard disk 9;Hard disk 4;Hard disk 5;Hard disk 6;Hard disk 1056;32;32;56;32;32;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 31;1;1;0;0;0;2physicalSharingTest01-ARRY-ora-TST-ATA-DS-01010084;010085;010086;010087;010088;010089;01008aphysicalModevm-855
14Test01-replvsq50poweredOnHard disk 11;Hard disk 12;Hard disk 13;Hard disk 14;Hard disk 15;Hard disk 16;Hard disk 1756;32;32;56;32;32;8Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 12;3;4;5;6;8;9physicalSharingora TST ATA-0101007d;01007e;01007f;010080;010081;010082;010083physicalModevm-833
Test Sheet


Im using an add-on (@bl3bits)-< not sure if there're issue name dropping add-ons within the forum. This is my process:
  1. 1st pass = Use VM ID as key and select everything except VM, powerstate, capacity (GB) and Unit # with delete duplicate values enabled- then run the tool (A duplicate sheet is created to preserve the original data)
  2. 2nd pass (This is done on the duplicate original sheet) = Use VM ID as key and only select capacity (GB) and Unit # with delete duplicate values disabled- then run the tool
  3. I manually copy/paste the Capacity (GB) and Unit # rows back into the original sheet created during the first pass merge.


I hope this make sense....
 
Upvote 0
The only thing I can think of is that some of your column A entries might have trailing or leading spaces, so try this modified version.
VBA Code: 
Sub t()
Dim i As Long    With ActiveSheet
        For i = .Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
            If Trim(.Cells(i, 1).Value) = Trim(.Cells(i - 1, 1).Value) Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ";" & .Cells(i, 3).Value                .Cells(i - 1, 4) = .Cells(i - 1, 4).Value & ";" & .Cells(i, 4).Value
                .Cells(i - 1, 13) = .Cells(i - 1, 13).Value & ";" & Cells(i, 13).Value
                .Rows(i).Delete
            End If
        Next
        .UsedRange.Columns.AutoFit
    End With
End Sub

Also, the code assumes that column A has been sorted to group like items.
 
Upvote 0
JLGWhiz said:
The only thing I can think of is that some of your column A entries might have trailing or leading spaces, so try this modified version.
VBA Code: 
Sub t()
Dim i As Long    With ActiveSheet
        For i = .Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
            If Trim(.Cells(i, 1).Value) = Trim(.Cells(i - 1, 1).Value) Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ";" & .Cells(i, 3).Value                .Cells(i - 1, 4) = .Cells(i - 1, 4).Value & ";" & .Cells(i, 4).Value
                .Cells(i - 1, 13) = .Cells(i - 1, 13).Value & ";" & Cells(i, 13).Value
                .Rows(i).Delete
            End If
        Next
        .UsedRange.Columns.AutoFit
    End With
End Sub

Also, the code assumes that column A has been sorted to group like items.
Click to expand...
Thanks, but still having issues. Is there a way to look at the last column (VM ID) and use that as key? The issue im having is that there may be duplicate values in column A (VM), but the VM ID may be different. (e.g. The VM column A may have duplicates but have a different VM ID which makes them unique)

Here is an example (highlighted):

RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 -vdisk testing.xlsx
ABCDEFGHIJKLMNOPQR
1VMPowerstateDiskCapacity (GB)RawDisk ModeThinEagerly ScrubReservationLimitControllerLabelUnit #Shared BusDatastoreRaw LUN IDRaw Comp. ModeVM ID
2test1vmdns001poweredOnHard disk 130FalsepersistentFalseTrue0-1LSI LogicSCSI controller 00noSharingtest1-arry2-INF-DS-08vm-2943
3test1vmdns001poweredOnHard disk 130FalsepersistentFalseTrue0-1LSI LogicSCSI controller 00noSharingtest1-arry2-INF-DS-03vm-273
4test1vmftp001poweredOnHard disk 1100FalsepersistentFalseTrue0-1LSI LogicSCSI controller 00noSharingtest1-arry2-INF-DS-06vm-163
5test1vmicb001poweredOnHard disk 1100FalsepersistentFalseTrue0-1LSI LogicSCSI controller 00noSharingtest1-arry2-INF-DS-09vm-2314
6test1vmicb001poweredOnHard disk 2500FalsepersistentFalseTrue0-1LSI LogicSCSI controller 01noSharingtest1-arry2-INF-DS-09vm-2314
7test1vmopo001poweredOnHard disk 36FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 02noSharingtest1-arry2-INF-DS-04vm-2350
8test1vmopo001poweredOnHard disk 220FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 01noSharingtest1-arry2-INF-DS-04vm-2350
9test1vmopo001poweredOnHard disk 180FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 00noSharingtest1-arry2-INF-DS-04vm-2350
10test1vmsky001poweredOnHard disk 315FalsepersistentFalseTrue0-1LSI LogicSCSI controller 02noSharingtest1-arry2-INF-DS-05vm-8020
11test1vmsky001poweredOnHard disk 260FalsepersistentFalseTrue0-1LSI LogicSCSI controller 01noSharingtest1-arry2-INF-DS-05vm-8020
12test1vmsky001poweredOnHard disk 112FalsepersistentFalseTrue0-1LSI LogicSCSI controller 00noSharingtest1-arry2-INF-DS-05vm-8020
13test1vmtst001poweredOnHard disk 8104Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 11physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01010067physicalModevm-6944
14test1vmtst001poweredOnHard disk 956Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 12physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01010068physicalModevm-6944
15test1vmtst001poweredOnHard disk 1032Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 13physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01010069physicalModevm-6944
16test1vmtst001poweredOnHard disk 118Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 14physicalSharingtest1-arry1-SQL-CL01-OSP-DS-0101006aphysicalModevm-6944
17test1vmtst001poweredOnHard disk 4100Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888888physicalModevm-6944
18test1vmtst001poweredOnHard disk 550Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888889physicalModevm-6944
19test1vmtst001poweredOnHard disk 71Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888887physicalModevm-6944
20test1vmtst001poweredOnHard disk 625Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888886physicalModevm-6944
21test1vmtst001poweredOnHard disk 180FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 00noSharingtest1-arry1-SQL-CL01-OSP-DS-01vm-6944
22test1vmtst001poweredOnHard disk 28FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 01noSharingtest1-arry1-SQL-CL01-OSP-DS-01vm-6944
23test1vmtst001poweredOnHard disk 320FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 02noSharingtest1-arry1-SQL-CL01-OSP-DS-01vm-6944
24test1vmtst001poweredOnHard disk 180FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 00noSharingtest1-arry2-GEN-WAP-DS-03vm-715
25test1vmtst001poweredOnHard disk 220FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 01noSharingtest1-arry2-GEN-WAP-DS-03vm-715
26test1vmtst001poweredOnHard disk 36FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 02noSharingtest1-arry2-GEN-WAP-DS-03vm-715
27test1vmtst002poweredOffHard disk 4100Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 10physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888888physicalModevm-6945
28test1vmtst002poweredOffHard disk 550Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 20physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888889physicalModevm-6945
29test1vmtst002poweredOffHard disk 71Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 31physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888887physicalModevm-6945
30test1vmtst002poweredOffHard disk 625Trueindependent_persistent0-1VMware paravirtual SCSISCSI controller 30physicalSharingtest1-arry1-SQL-CL01-OSP-DS-01912d0c558888888886physicalModevm-6945
31test1vmtst002poweredOffHard disk 28FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 01noSharingtest1-arry1-SQL-CL01-OSP-DS-02vm-6945
32test1vmtst002poweredOffHard disk 320FalsepersistentFalseTrue0-1VMware paravirtual SCSISCSI controller 02noSharingtest1-arry1-SQL-CL01-OSP-DS-02vm-6945
#Test Sheet (5)
 
Upvote 0
This would do the comparison based on data in column R.

VBA Code: 
Sub t3()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, "R").End(xlUp).Row To 3 Step -1
            If Trim(.Cells(i, 18).Value) = Trim(.Cells(i - 1, 18).Value) Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ";" & .Cells(i, 3).Value
                .Cells(i - 1, 4) = .Cells(i - 1, 4).Value & ";" & .Cells(i, 4).Value
                .Cells(i - 1, 13) = .Cells(i - 1, 13).Value & ";" & Cells(i, 13).Value
                .Rows(i).Delete
            End If
        Next
        .UsedRange.Columns.AutoFit
    End With
End Sub

Again, it is based on column R being sorted so that duplicates are grouped togethes.
 
Upvote 0
JLGWhiz said:
This would do the comparison based on data in column R.

VBA Code: 
Sub t3()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, "R").End(xlUp).Row To 3 Step -1
            If Trim(.Cells(i, 18).Value) = Trim(.Cells(i - 1, 18).Value) Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ";" & .Cells(i, 3).Value
                .Cells(i - 1, 4) = .Cells(i - 1, 4).Value & ";" & .Cells(i, 4).Value
                .Cells(i - 1, 13) = .Cells(i - 1, 13).Value & ";" & Cells(i, 13).Value
                .Rows(i).Delete
            End If
        Next
        .UsedRange.Columns.AutoFit
    End With
End Sub

Again, it is based on column R being sorted so that duplicates are grouped togethes.
Click to expand...


I used the following code because i need more rows to be combined:

VBA Code: 
Sub t3()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, "R").End(xlUp).Row To 3 Step -1
            If Trim(.Cells(i, 18).Value) = Trim(.Cells(i - 1, 18).Value) Then
                .Cells(i - 1, 3) = .Cells(i - 1, 3).Value & ";" & .Cells(i, 3).Value
                .Cells(i - 1, 4) = .Cells(i - 1, 4).Value & ";" & .Cells(i, 4).Value
                .Cells(i - 1, 5) = .Cells(i - 1, 5).Value & ";" & Cells(i, 5).Value
                .Cells(i - 1, 6) = .Cells(i - 1, 6).Value & ";" & Cells(i, 6).Value
                .Cells(i - 1, 7) = .Cells(i - 1, 7).Value & ";" & Cells(i, 7).Value
                .Cells(i - 1, 8) = .Cells(i - 1, 8).Value & ";" & Cells(i, 8).Value
                .Cells(i - 1, 9) = .Cells(i - 1, 9).Value & ";" & Cells(i, 9).Value
                .Cells(i - 1, 10) = .Cells(i - 1, 10).Value & ";" & Cells(i, 10).Value
                .Cells(i - 1, 11) = .Cells(i - 1, 11).Value & ";" & Cells(i, 11).Value
                .Cells(i - 1, 12) = .Cells(i - 1, 12).Value & ";" & Cells(i, 12).Value
                .Cells(i - 1, 13) = .Cells(i - 1, 13).Value & ";" & Cells(i, 13).Value
                .Cells(i - 1, 14) = .Cells(i - 1, 14).Value & ";" & Cells(i, 14).Value
                .Cells(i - 1, 15) = .Cells(i - 1, 15).Value & ";" & Cells(i, 15).Value
                .Cells(i - 1, 16) = .Cells(i - 1, 16).Value & ";" & Cells(i, 16).Value
                .Cells(i - 1, 17) = .Cells(i - 1, 17).Value & ";" & Cells(i, 17).Value
                .Rows(i).Delete
            End If
        Next
        .UsedRange.Columns.AutoFit
    End With
End Sub

Problem is that it took 25 minutes to run on 8k rows (hopefully there's a way to make it more efficient) and some columns like RAW and Disk Mode have duplicate entries which I only need to keep one unique entry in the cell.

For example:

This is what I got after the code ran:
RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 -vdisk testing.xlsx
EFGHIJKLMN
40True;True;True;True;True;True;True;False;False;Falseindependent_persistent;independent_persistent;independent_persistent;independent_persistent;independent_persistent;independent_persistent;independent_persistent;persistent;persistent;persistent;;;;;;;False;False;False;;;;;;;True;True;True0;0;0;0;0;0;0;0;0;0-1;-1;-1;-1;-1;-1;-1;-1;-1;-1VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 3;SCSI controller 0;SCSI controller 0;SCSI controller 00;0;0;1;1;1;2;2;1;0physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;noSharing;noSharing;noSharing
41True;True;True;True;True;True;True;False;False;Falseindependent_persistent;independent_persistent;independent_persistent;independent_persistent;independent_persistent;independent_persistent;independent_persistent;persistent;persistent;persistent;;;;;;;False;False;False;;;;;;;True;True;True0;0;0;0;0;0;0;0;0;0-1;-1;-1;-1;-1;-1;-1;-1;-1;-1VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 3;SCSI controller 0;SCSI controller 0;SCSI controller 00;0;0;1;1;1;2;2;1;0physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;noSharing;noSharing;noSharing
42True;True;True;True;True;True;True;False;False;Falseindependent_persistent;independent_persistent;independent_persistent;independent_persistent;persistent;independent_persistent;independent_persistent;persistent;persistent;persistent;;;;;;;False;False;False;;;;;;;True;True;True0;0;0;0;0;0;0;0;0;0-1;-1;-1;-1;-1;-1;-1;-1;-1;-1VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSI;VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 3;SCSI controller 0;SCSI controller 0;SCSI controller 00;0;0;1;1;1;2;1;0;2physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;physicalSharing;noSharing;noSharing;noSharing
vDisk (2)



This is what I would like it to look like if possible:
RVTools_export_dfw5infvcn001_2020-03-24_15.09.19 -vdisk testing.xlsx
ABCDEFGHIJ
1True;Falseindependent_persistent;persistentFALSETRUE0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 00;1;2physicalSharing;noSharing
2True;Falseindependent_persistent;persistentFALSETRUE0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 00;1;2physicalSharing;noSharing
3True;Falseindependent_persistent;persistentFALSETRUE0-1VMware paravirtual SCSISCSI controller 1;SCSI controller 2;SCSI controller 3;SCSI controller 00;1;2physicalSharing;noSharing
#Sheet10 (3)
 
Upvote 0
It looks like you have the concept of how the code works, so you should be able to modify it to your specific needs. There is no need to include every column in the part that accumulates the values on different rows if there is no difference in the values. So if you review your source database and pick only the columns where the data varies between rows, you can then eliminate the lines of code for the other columns and reduce the run time. I only counted three columns with varying data within a group of duplicates and that was how I wrote the code. But your actual database might be different so you need to make that choice. And there is always somebody who can figure out how to make the code shorter and faster. But what I posted is what I think the description of the issue called for.
Regards, JLG
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA to merge duplicate rows and sum values in certain column with adjustments
Replies
9
Views
317
Alex Blakenburg
A
N
  • Solved
Duplicates question and scoring system
Replies
6
Views
254
MountainFog
M
M
  • Question
VBA hides/unhides columns based upon pivot table and value
Replies
0
Views
121
mapatton
M
R
  • Solved
VBA: Merging columns based on header
2
Replies
12
Views
600
ruinedelf
R
J
  • Solved
VBA to Sum and Delete Duplicate Values
2
Replies
11
Views
1K
alansidman
A

Forum statistics

Threads
1,214,522
Messages
6,120,019
Members
448,938
Latest member
Aaliya13

Share this page

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