Using Macros to combine multiple lines of data when Address column data is the same.
Hi everyone,
I'm new at this and not sure exactly how this works but I need help with a Macro. As shown in the attached sample file, I have a worksheet with names and addresses. What I am attempting to do is combine all the names where the address is the same. For example, if I have a Jason Ambros who lives at 10 Dyson Dr, Parkdale and later down the worksheet there is a David Johnson who lives at the same address, I would like the rows to be merged so the names column now shows "Jason, David".
Re: Using Macros to combine multiple lines of data when Address column data is the same.
Try this code, but I wonder how about "given name" and "other given name"?
PHP Code:
Sub ExtractData() Dim SArr(), RArr() Dim EndR As Long, i As Long, j As Long, RCount As Long Dim Dic As Object, TestStr As String Set Dic = CreateObject("Scripting.Dictionary") EndR = Sheet1.[a65000].End(xlUp).Row SArr = Sheet1.Range("A1:J" & EndR) ReDim RArr(1 To EndR, 1 To 10) For i = 1 To EndR TestStr = SArr(i, 4) & SArr(i, 5) & SArr(i, 6) & SArr(i, 7) & SArr(i, 8) If Not Dic.exists(TestStr) Then RCount = RCount + 1 Dic.Add TestStr, RCount For j = 1 To 10 RArr(RCount, j) = SArr(i, j) Next Else RArr(Dic.Item(TestStr), 1) = RArr(Dic.Item(TestStr), 1) & ", " & SArr(i, 1) End If Next Sheet2.Cells.ClearContents Sheet2.[a1].Resize(RCount, 10) = RArr End Sub
Re: Using Macros to combine multiple lines of data when Address column data is the same.
Chrispy,
On the menu Tools of VBA window (open by Alt + F11), choose References. Then pull down the scroll bar, find and check the item "Microsoft Scripting runtime"
Anyway, see my attached file in my previous post above
Bookmarks