I would like to get the unique values from column A and all the corresponding values from column B in excel. So transform this:
Into that:
Is it possible in Excel?
-
1[python] or [excel] ? Possible duplicate of Aggregate, Collate and Transpose rows into columnsuser4039065– user40390652015-09-19 14:00:20 +00:00Commented Sep 19, 2015 at 14:00
-
In Excel sorry. I already tried the script you suggested, but I can't make it work for my needs unfortunately.Sam– Sam2015-09-19 14:20:08 +00:00Commented Sep 19, 2015 at 14:20
Add a comment
|
4 Answers
With data like this in Sheet1:
running this macro:
Sub dural()
Dim s1 As Worksheet, s2 As Worksheet
Dim i As Long, j As Long, st As String
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:A").Copy s2.Range("A1")
s2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
For Each r In s2.Range("A:A")
v = r.Value
If v = "" Then Exit Sub
For Each rr In s1.Range("A:A")
vv = rr.Value
If vv = "" Then Exit For
If v = vv Then
If r.Offset(0, 1).Value = "" Then
r.Offset(0, 1).Value = rr.Offset(0, 1).Value
Else
r.Offset(0, 1).Value = r.Offset(0, 1).Value & " ," & rr.Offset(0, 1).Value
End If
End If
Next rr
Next r
End Sub
Will produce this in Sheet2:
NOTE:
The data in Sheet1 is not required to be sorted.
1 Comment
Gary's Student
@Sam Thanks for the feedback!
Try this one:
Sub Test()
Dim objIds, arrData, i, strId
Set objIds = CreateObject("Scripting.Dictionary")
arrData = Range("A1:B8").Value ' put here your source range
For i = LBound(arrData, 1) To UBound(arrData, 1)
If IsEmpty(objIds(arrData(i, 1))) Then
objIds(arrData(i, 1)) = arrData(i, 2)
Else
objIds(arrData(i, 1)) = objIds(arrData(i, 1)) & ", " & arrData(i, 2)
End If
Next
i = 1 ' first row for output
For Each strId In objIds
Cells(i, 3) = strId ' first column for output
Cells(i, 4) = objIds(strId) ' second column for output
i = i + 1
Next
End Sub
Comments
This is all you need and nothing must be sorted:
Sub Sam()
Dim c&, i&, d$, s$, v, w
v = [a1].CurrentRegion.Resize(, 2)
ReDim w(1 To UBound(v), 1 To 2)
For i = 1 To UBound(v)
d = ", "
If s <> v(i, 1) Then d = "": c = c + 1: s = v(i, 1): w(c, 1) = s
w(c, 2) = w(c, 2) & d & v(i, 2)
Next
[d1:e1].Resize(UBound(w)) = w
End Sub
This code is extremely fast. If you were to process a large list, the efficiency here would be appreciated.
You can manage where the source data is and where the output should be written by adjusting the addresses in the square brackets at the top and bottom of the procedure.
Comments
Looking at how to solve this using Excel formulae only (I know there is a VBA tag in the OP), but here is another option.
Adding 2 additional columns with formulae we get this result:
By filtering on the finalList column where the value = 1 we get the desired result:
The formula that are required are as follows:
Cell C1 : =B2
Cell C2 (And copied down to all cells in Column C) : =IF(A3=A2,C2&","&B3,B3)
Cell D1 (And copied down to all cells in Column D) : =IF(A2=A3,0,1)
NOTE: This will only work when Column A is sorted.
