All geek questions in one place

Group Excel rows based on similar values ​​using VBA

I have a report.xlsx file that contains data from 2 other excel books that have been merged based on some unique value. The column Pool_RAM has data from the columns file1.xlsx and Pool_HDD has data from the file2.xlsx . Both columns contain multiple duplicate values. Now I want to group the strings based on similar values ​​and put them in a format as follows.

Report.xlsx Actual Data

enter image description here

And I want the data to be in the following format.

Expected Format

enter image description here

Please let me know if you need any other information.

+4
source share
1 answer

Try this macro and customize 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
0
source

Source: https://habr.com/ru/post/1435362/

More articles:


All Articles