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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks