Copy Rows, Paste into New Sheet, Then Paste twice if Address2 is Different then replace Address 1 with Address2 on 2nd line

J

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
I hope my title wasn't too long and was helpful! :) It explains my issue pretty nicely. Here is a list of addresses we have in excel.

Excel 2010
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
4James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
5Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
6Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
7Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
8Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
9Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​
This is what we do manually that we'd like to automate with a macro:

1) Select a range of Rows
2) Copy
3) Paste values into "Export" sheet in same workbook, starting in row 2.
4) Look for all rows that have a different Address 2 (Street, City, State, & Zip) than the Address 1 and Duplicate those rows
5) Replace Address 1 with Address 2 on the duplicate line.​

The result looks like this:

Excel 2010
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
4James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
5Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
6Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
7Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
8Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
9Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
10Jon Smith123 Main StLouisvilleKY40258123 Main StLouisvilleKY40258
11Sally Jones552 Jones StLouisvilleKY40272552 Jones StLouisvilleKY40272
12Thomas Wong & Billie Chai111 Maple StLouisvilleKY40229111 Maple StLouisvilleKY40229

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Export​



Then we use mail merge in word to create mailing labels from this list.

Is there a smarter way to do this?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
jeffcoleky,


Sample raw data in Sheet1:


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
4James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
5Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
6Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
7Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
8Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
9Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
10
Sheet1



After the macro in worksheet Export (the output should look like this????):


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Antuane & Tiffany Samarka536 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
4Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
5Sally Kellam & Kirk Bolder2718 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
6James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
7James & Connie Macdonald11114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
8Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
9Paula & Douglas Mills146 Breckenridge Ln Apt B2WoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
10Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
11Theresa Jackson10112 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
12Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
13Jon Smith123 Main StLouisvilleKY40258123 Main StLouisvilleKY40258
14Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
15Sally Jones552 Jones StLouisvilleKY40272552 Jones StLouisvilleKY40272
16Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
17Thomas Wong & Billie Chai111 Maple StLouisvilleKY40229111 Maple StLouisvilleKY40229
18
Export
 
Upvote 0
jeffcoleky,

Sample raw data in Sheet1:


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
4James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
5Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
6Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
7Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
8Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
9Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
10
Sheet1


After the macro in worksheet Export:


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Antuane & Tiffany Samarka536 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
4Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
5Sally Kellam & Kirk Bolder2718 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
6James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
7James & Connie Macdonald11114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
8Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
9Paula & Douglas Mills146 Breckenridge Ln Apt B2WoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
10Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
11Theresa Jackson10112 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
12Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
13Jon Smith123 Main StLouisvilleKY40258123 Main StLouisvilleKY40258
14Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
15Sally Jones552 Jones StLouisvilleKY40272552 Jones StLouisvilleKY40272
16Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
17Thomas Wong & Billie Chai111 Maple StLouisvilleKY40229111 Maple StLouisvilleKY40229
18
Export


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Option Explicit
Sub CheckDupeAddress()
' hiker95, 02/23/2013
' http://www.mrexcel.com/forum/excel-questions/687418-copy-rows-paste-into-new-sheet-then-paste-twice-if-address2-different-then-replace-address-1-address2-2nd-line.html
Dim a As Variant, b As Variant, lr As Long, lc As Long, i As Long, ii As Long
With Worksheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
End With
ReDim b(1 To UBound(a, 1) * 2, 1 To UBound(a, 2))
i = 1
ii = ii + 1
b(ii, 1) = a(i, 1): b(ii, 2) = a(i, 2): b(ii, 3) = a(i, 3)
b(ii, 4) = a(i, 4): b(ii, 5) = a(i, 5): b(ii, 6) = a(i, 6)
b(ii, 7) = a(i, 7): b(ii, 8) = a(i, 8): b(ii, 9) = a(i, 9)
For i = 2 To UBound(a, 1)
  ii = ii + 1
  b(ii, 1) = a(i, 1): b(ii, 2) = a(i, 2): b(ii, 3) = a(i, 3)
  b(ii, 4) = a(i, 4): b(ii, 5) = a(i, 5): b(ii, 6) = a(i, 6)
  b(ii, 7) = a(i, 7): b(ii, 8) = a(i, 8): b(ii, 9) = a(i, 9)
  If a(i, 2) <> a(i, 6) Or a(i, 3) <> a(i, 7) Or a(i, 4) <> a(i, 8) Or a(i, 5) <> a(i, 9) Then
    ii = ii + 1
    b(ii, 1) = a(i, 1): b(ii, 2) = a(i, 6): b(ii, 3) = a(i, 7)
    b(ii, 4) = a(i, 8): b(ii, 5) = a(i, 9): b(ii, 6) = a(i, 6)
    b(ii, 7) = a(i, 7): b(ii, 8) = a(i, 8): b(ii, 9) = a(i, 9)
  End If
Next i
If Not Evaluate("ISREF(Export!A1)") Then Worksheets.Add(After:=Worksheets("Sheet1")).Name = "Export"
With Worksheets("Export")
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(b, 1), UBound(b, 2)) = b
  .Cells.EntireColumn.AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CheckDupeAddress macro.
 
Upvote 0
Oh that's SO close to what i need. The only difference is that if street1 and street2 are the same then that line doesn't get duplicated. Or, a 'remove duplicates' in column "B" could be run at the end i suppose?

The goal is to get all unique addresses in Column B in their own row, with the proper name associated with each.
 
Upvote 0
jeffcoleky,

Sample raw data in Sheet1:


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
4James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
5Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
6Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
7Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
8Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
9Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
10
Sheet1


After the macro in a new worksheet Export:


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA50599536 Brentwood AveWoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA505992718 Fleming AveWoolstockIA50599
4James & Connie Macdonald1114 Lansford DrWoolstockIA5059911114 Lansford DrWoolstockIA50599
5Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
6Theresa Jackson1012 Blue Creek DrWoolstockKY5059910112 Blue Creek DrWoolstockKY50599
7Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
8Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
9Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
10Paula & Douglas Mills146 Breckenridge Ln Apt B2WoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
11Jon Smith123 Main StLouisvilleKY40258123 Main StLouisvilleKY40258
12Sally Jones552 Jones StLouisvilleKY40272552 Jones StLouisvilleKY40272
13Thomas Wong & Billie Chai111 Maple StLouisvilleKY40229111 Maple StLouisvilleKY40229
14
Export



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub CheckDupeAddressV2()
' hiker95, 02/23/2013
' http://www.mrexcel.com/forum/excel-questions/687418-copy-rows-paste-into-new-sheet-then-paste-twice-if-address2-different-then-replace-address-1-address2-2nd-line.html
Dim a As Variant, lr As Long, lc As Long, i As Long, nr As Long, s1 As String, s2 As String
With Worksheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  nr = lr
  a = .Range(.Cells(1, 1), .Cells(lr + lr, lc))
End With
For i = 2 To lr
  s1 = Application.Replace(a(i, 2), 1, Application.Search(" ", a(i, 2), 1), "")
  s2 = Application.Replace(a(i, 6), 1, Application.Search(" ", a(i, 6), 1), "")
  If s1 <> s2 Then
    nr = nr + 1
    a(nr, 1) = a(i, 1): a(nr, 2) = a(i, 6): a(nr, 3) = a(i, 7)
    a(nr, 4) = a(i, 8): a(nr, 5) = a(i, 9): a(nr, 6) = a(i, 6)
    a(nr, 7) = a(i, 7): a(nr, 8) = a(i, 8): a(nr, 9) = a(i, 9)
  End If
Next i
If Not Evaluate("ISREF(Export!A1)") Then Worksheets.Add(After:=Worksheets("Sheet1")).Name = "Export"
With Worksheets("Export")
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(a, 1), UBound(a, 2)) = a
  .Cells.EntireColumn.AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CheckDupeAddressV2 macro.
 
Upvote 0
Wonderful! It works flawlessly! Thank you for using your great talent to make this for me. Have a wonderful weekend.


Edit: Correction, is it possible to change it so that it only copies the SELECTED rows in sheet 1 and not all of them?
 
Upvote 0
jeffcoleky,

Is this the output you are now looking for?


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Paula & Douglas Mills146 Breckenridge Ln Apt B2WoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
3Jon Smith123 Main StLouisvilleKY40258123 Main StLouisvilleKY40258
4Sally Jones552 Jones StLouisvilleKY40272552 Jones StLouisvilleKY40272
5Thomas Wong & Billie Chai111 Maple StLouisvilleKY40229111 Maple StLouisvilleKY40229
6
Export
 
Upvote 0
Hiker95....You've done great. The only change is that it needs to copy the selected rows in sheet1, instead of all rows in sheet1. All rows were selected it would be as you described. However, if Rows 2, 3, 8, & 9 are highlighted before running the macro, the output should look like this: (Note, it copies all selected rows, and only duplicates the ones that have a different address.)


Sample raw data in Sheet1:
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka536 Brentwood Ave
WoolstockIA50599536 Brentwood Ave
WoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA50599278 Fleming Ave
WoolstockIA50599
4James & Connie Macdonald1114 Lansford DrWoolstockIA505991114 Lansford Dr
WoolstockIA50599
5Paula & Douglas Mills1019 Allen DrWoolstockKY50599146 Breckenridge Ln Apt B2WoolstockKY50599
6Theresa Jackson1012 Blue Creek DrWoolstockKY505991012 Blue Creek Dr
WoolstockKY50599
7Jon Smith6214 Maravian DrLedyardIA50556123 Main StLouisvilleKY40258
8Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
9Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
10

<tbody>
</tbody>


Excel 2010
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
2Antuane & Tiffany Samarka5369 Brentwood AveWoolstockIA505995369 Brentwood Ave
WoolstockIA50599
3Sally Kellam & Kirk Bolder278 Fleming AveWoolstockIA50599278 Fleming Ave
WoolstockIA50599
4Sally Jones1822 Golden DrWoolstockIA50599552 Jones StLouisvilleKY40272
5Sally Jones552 Jones StLouisvilleKY40272552 Jones StLouisvilleKY40272
6Thomas Wong & Billie Chai3709 Astrocraft DrLedyardIA50556111 Maple StLouisvilleKY40229
7Thomas Wong & Billie Chai111 Maple StLouisvilleKY40229111 Maple StLouisvilleKY40229

<tbody>
</tbody>
Export
​
 
Last edited:
Upvote 0
PS. sorry for the confusion, I just realized the addresses in the first 3 rows were similar but not identical. Treat them as identical.
 
Upvote 0
jeffcoleky,

You can show highlighting with HTML Maker, like this:


Excel 2007
ABCDEFGHI
1OwnersStreet1City1State1Zip1Street2City2State2Zip2
Export


I do not want instructions on what rows are highlighted, I would like to see the highlighting.

Please show with another screenshot, your highlighting with the HTML Maker, in the correct color, and, instructions.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro Help: 6 Sets of Addresses in single row --> Export to new workbook, each address on a new sheet preceded by the data in columns A-D
Replies
2
Views
567
jeffcoleky
J

Forum statistics

Threads
1,221,418
Messages
6,159,790
Members
451,589
Latest member
Harold14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top