Grouping Excel rows based on similar values using VBA

Question

I have report.xlsx file which contains data from 2 other excel workbook which got merged based on some unique value. Pool_RAM column has data from file1.xlsx and Pool_HDD column has data from file2.xlsx. Both the column contains some duplicate values. Now I want to group the rows based on similar values and put in format as mentioned below.

Report.xlsx Actual Data

enter image description here

And I want data to in the below format.

Expected Format

enter image description here

Please let me know if you need any other information.

+1 @SiddharthRout -> I would even add, what is your real question? it's not very clear. — Scott Holtzman
@ScottHoltzman: I presume the question is in the title. I believe the user wants to group values in a row after the merge happens based on similar values like 113e1fb6-8998-ad9c-fbd0-61101ef544ac and 113e1fb6-8998-ad9c-fbd0-61101ef544ac — Siddharth Rout
@SiddharthRout :- Actually question got posted in middle of writing . Please see the screenshot which i attached. Yeah i want group the rows in based on similar values. — workspace
@Scott Holtzman :- Actually question got posted in middle of writing — workspace
Here is the logic. 1. Define an object for the sheet which has the data 2. Add a temp sheet for output 3. Get last Row of Col G of the sheet which has the data 4. Sort the data in Col G in ascending order 5. Get last Row of Col F of the sheet which has the data 6. Sort the data in Col F in ascending order 7. Add values from Col F in a unique collection 8. Set the row for output in the new sheet 9. Loop through the unique collection — Siddharth Rout

Setyo N Setyo N · Accepted Answer · 2012-09-21T21:50:11

Try this macro and adjust it to your needs:

Sub GroupMyData()
'Assuming the data source is in column F and G, from row 2 to 1000
'The result will be put in column K and L
'part1: filter to unique values, put in column J temporarily
    Range("F2:F1000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J2" _
        ), Unique:=True

'part2: fill the data
    Range("K1") = Range("F1")
    Range("L1") = Range("G1")
    Range("J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    n = Selection.Count
    Range("K2").Select
    ck = 2
    cl = 2
    For i = 1 To n
      txt = Range("J" & (i + 1))
      numf = WorksheetFunction.CountIf(Range("F2:F1000"), txt)
      For j = 1 To numf
        Range("K" & ck) = txt
        ck = ck + 1
      Next
      numg = WorksheetFunction.CountIf(Range("G2:G1000"), txt)
      For j = 1 To numg
        Range("L" & cl) = txt
        cl = cl + 1
      Next
      If (ck > cl) Then
        cl = ck
      Else
        ck = cl
      End If
      ck = ck + 1
      cl = cl + 1
    Next
'part 3: delete the temporary data
    Range("J2:J1000").Select
    Selection.ClearContents
    Range("K2").Select

End Sub

Grouping Excel rows based on similar values using VBA

1 Answers