you can use a pivot table to group the values by their respective categories. See attached.
cheers, teylyn
Book1.xlsx
Sub MatchVals()
Dim rng1 As Range
Dim rng2 As Range
Dim rowCount As Long
Dim arr1() As Variant
Dim arr2() As Variant
Dim counter As Integer, counter2 As Integer
Dim colInc As Integer, rowInc As Integer
colInc = 1
rowInc = 1
Sheets("Sheet1").Activate
rowCount = Range("A1").CurrentRegion.Rows.Count
Set rng1 = Range(Cells(1, 1), Cells(rowCount, 2))
Set rng2 = Range(Cells(1, 2), Cells(rowCount, 2))
arr1 = rng1
arr2 = rng2
SortArr arr2
Sheets("Sheet2").Activate
Range("A1").CurrentRegion.Clear
For counter = 1 To UBound(arr2, 1) - 1
If arr2(counter, 1) <> arr2(counter + 1, 1) Then
Cells(1, colInc).Value = arr2(counter, 1)
colInc = colInc + 1
End If
Next
Cells(1, colInc).Value = arr2(UBound(arr2, 1), 1)
Set rng2 = Range(Cells(1, 1), Cells(1, Cells(1, 1).CurrentRegion.Columns.Count))
arr2 = rng2
For counter = 1 To UBound(arr2, 2)
For counter2 = 1 To UBound(arr1, 1)
If arr1(counter2, 2) = arr2(1, counter) Then
Cells(rowInc + 1, counter).Value = arr1(counter2, 1)
rowInc = rowInc + 1
End If
Next counter2
rowInc = 1
Next counter
End Sub
Sub SortArr(ByRef arr As Variant)
Dim tempVal As Variant
Dim counter As Integer
For counter = 1 To UBound(arr, 1) - 1
If arr(counter, 1) > arr(counter + 1, 1) Then
tempVal = arr(counter, 1)
arr(counter, 1) = arr(counter + 1, 1)
arr(counter + 1, 1) = tempVal
SortArr arr
End If
Next
End Sub
Title | # Comments | Views | Activity |
---|---|---|---|
Relative link_location when pasting a =HYPERLINK() formula to a different cell in Excel | 7 | 18 | |
SQL Server 2008 R2 - Updating Table/Fields Documentation | 3 | 28 | |
Copy the formula in excel | 8 | 29 | |
Out of stack space (Error 28) | 5 | 24 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!