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:
End State of what I need it to look like:
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!
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 | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | VM | Powerstate | Disk | Capacity (GB) | Raw | Disk Mode | Thin | Eagerly Scrub | Reservation | Limit | Controller | Label | Unit # | Datastore | Raw LUN ID | Raw Comp. Mode | VM ID | ||
2 | Test-VM1 | poweredOff | Hard disk 1 | 80 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 0 | TEMPLATES | vm-1794 | ||||
3 | Test-VM1 | poweredOff | Hard disk 3 | 6 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 2 | TEMPLATES | vm-1794 | ||||
4 | Test-VM1 | poweredOff | Hard disk 2 | 20 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 1 | TEMPLATES | vm-1794 | ||||
5 | Test-VM2 | poweredOff | Hard disk 3 | 8 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 2 | TEMPLATES | vm-441 | ||||
6 | Test-VM2 | poweredOff | Hard disk 2 | 20 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 1 | TEMPLATES | vm-441 | ||||
7 | Test-VM2 | poweredOff | Hard disk 1 | 80 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 0 | TEMPLATES | vm-441 | ||||
8 | Test-VM3 | poweredOn | Hard disk 2 | 20 | False | persistent | False | False | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 1 | DS07 | vm-6955 | ||||
9 | Test-VM3 | poweredOn | Hard disk 1 | 80 | False | persistent | False | False | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 0 | DS07 | vm-6955 | ||||
10 | Test-VM3 | poweredOn | Hard disk 4 | 20 | False | persistent | False | False | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 2 | DS07 | vm-6955 | ||||
Unmerged |
End State of what I need it to look like:
Merge_EX.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | VM | Powerstate | Disk | Capacity (GB) | Raw | Disk Mode | Thin | Eagerly Scrub | Reservation | Limit | Controller | Label | Unit # | Datastore | Raw LUN ID | Raw Comp. Mode | VM ID | ||
2 | Test-VM1 | poweredOff | Hard disk 1;Hard disk 3;Hard disk 2 | 80;6;20 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 0;2;1 | TEMPLATES | vm-1794 | ||||
3 | Test-VM2 | poweredOff | Hard disk 3;Hard disk 2;Hard disk 1 | 8;20;80 | False | persistent | False | True | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 2;1;0 | TEMPLATES | vm-441 | ||||
4 | Test-VM3 | poweredOn | Hard disk 2;Hard disk 1;Hard disk 4 | 20;80;20 | TRUE;False | persistent | False | False | 0 | -1 | VMware paravirtual SCSI | SCSI controller 0 | 1;0;2 | DS07 | vm-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!