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

jeffcoleky · Feb 22, 2013

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

	A	B	C	D	E	F	G	H
Owners	Street1	City1	State1	Zip1	Street2	City2	State2	Zip2
Antuane & Tiffany Samarka	5369 Brentwood Ave	Woolstock	IA	536 Brentwood Ave	Woolstock	IA
Sally Kellam & Kirk Bolder	278 Fleming Ave	Woolstock	IA	2718 Fleming Ave	Woolstock	IA
James & Connie Macdonald	1114 Lansford Dr	Woolstock	IA	11114 Lansford Dr	Woolstock	IA
Paula & Douglas Mills	1019 Allen Dr	Woolstock	KY	146 Breckenridge Ln Apt B2	Woolstock	KY
Theresa Jackson	1012 Blue Creek Dr	Woolstock	KY	10112 Blue Creek Dr	Woolstock	KY
Jon Smith	6214 Maravian Dr	Ledyard	IA	123 Main St	Louisville	KY
Sally Jones	1822 Golden Dr	Woolstock	IA	552 Jones St	Louisville	KY
Thomas Wong & Billie Chai	3709 Astrocraft Dr	Ledyard	IA	111 Maple St	Louisville	KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]50556[/TD]

[TD="align: right"]40258[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]40272[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]50556[/TD]

[TD="align: right"]40229[/TD]

</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

	A	B	C	D	E	F	G	H
Owners	Street1	City1	State1	Zip1	Street2	City2	State2	Zip2
Antuane & Tiffany Samarka	5369 Brentwood Ave	Woolstock	IA	536 Brentwood Ave	Woolstock	IA
Sally Kellam & Kirk Bolder	278 Fleming Ave	Woolstock	IA	2718 Fleming Ave	Woolstock	IA
James & Connie Macdonald	1114 Lansford Dr	Woolstock	IA	11114 Lansford Dr	Woolstock	IA
Paula & Douglas Mills	1019 Allen Dr	Woolstock	KY	146 Breckenridge Ln Apt B2	Woolstock	KY
Theresa Jackson	1012 Blue Creek Dr	Woolstock	KY	10112 Blue Creek Dr	Woolstock	KY
Jon Smith	6214 Maravian Dr	Ledyard	IA	123 Main St	Louisville	KY
Sally Jones	1822 Golden Dr	Woolstock	IA	552 Jones St	Louisville	KY
Thomas Wong & Billie Chai	3709 Astrocraft Dr	Ledyard	IA	111 Maple St	Louisville	KY
Jon Smith	123 Main St	Louisville	KY	123 Main St	Louisville	KY
Sally Jones	552 Jones St	Louisville	KY	552 Jones St	Louisville	KY
Thomas Wong & Billie Chai	111 Maple St	Louisville	KY	111 Maple St	Louisville	KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]50556[/TD]

[TD="align: right"]40258[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]40272[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]50556[/TD]

[TD="align: right"]40229[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]40258[/TD]

[TD="align: right"]40258[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]40272[/TD]

[TD="align: right"]40272[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]40229[/TD]

[TD="align: right"]40229[/TD]

</tbody>

Export

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

Is there a smarter way to do this?

hiker95 · Feb 23, 2013

jeffcoleky,

PS. sorry for the confusion, I just realized the addresses in the first 3 rows were similar but not identical. Treat them as identical.

Please display screenshots with highlighting, and with the correct input, and, output.

jeffcoleky · Feb 23, 2013

RAW: (Highlighted = Selected when macro is run)

Excel 2010

	A	B	C	D	E	F	G	H
Owners	Street1	City1	State1	Zip1	Street2	City2	State2	Zip2


James & Connie Macdonald	1114 Lansford Dr	Woolstock	IA	11114 Lansford Dr	Woolstock	IA
Paula & Douglas Mills	1019 Allen Dr	Woolstock	KY	146 Breckenridge Ln Apt B2	Woolstock	KY
Theresa Jackson	1012 Blue Creek Dr	Woolstock	KY	10112 Blue Creek Dr	Woolstock	KY
Jon Smith	6214 Maravian Dr	Ledyard	IA	123 Main St	Louisville	KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]Antuane & Tiffany Samarka[/TD]
[TD="bgcolor: #FFFF00"]539 Brentwood Ave[/TD]
[TD="bgcolor: #FFFF00"]Woolstock[/TD]
[TD="bgcolor: #FFFF00"]IA[/TD]
[TD="bgcolor: #FFFF00, align: right"]50599[/TD]
[TD="bgcolor: #FFFF00"]539 Brentwood Ave[/TD]
[TD="bgcolor: #FFFF00"]Woolstock[/TD]
[TD="bgcolor: #FFFF00"]IA[/TD]
[TD="bgcolor: #FFFF00, align: right"]50599[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFF00"]Sally Kellam & Kirk Bolder[/TD]
[TD="bgcolor: #FFFF00"]278 Fleming Ave[/TD]
[TD="bgcolor: #FFFF00"]Woolstock[/TD]
[TD="bgcolor: #FFFF00"]IA[/TD]
[TD="bgcolor: #FFFF00, align: right"]50599[/TD]
[TD="bgcolor: #FFFF00"]278 Fleming Ave[/TD]
[TD="bgcolor: #FFFF00"]Woolstock[/TD]
[TD="bgcolor: #FFFF00"]IA[/TD]
[TD="bgcolor: #FFFF00, align: right"]50599[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]50556[/TD]

[TD="align: right"]40258[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFF00"]Sally Jones[/TD]
[TD="bgcolor: #FFFF00"]1822 Golden Dr[/TD]
[TD="bgcolor: #FFFF00"]Woolstock[/TD]
[TD="bgcolor: #FFFF00"]IA[/TD]
[TD="bgcolor: #FFFF00, align: right"]50599[/TD]
[TD="bgcolor: #FFFF00"]552 Jones St[/TD]
[TD="bgcolor: #FFFF00"]Louisville[/TD]
[TD="bgcolor: #FFFF00"]KY[/TD]
[TD="bgcolor: #FFFF00, align: right"]40272[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFF00"]Thomas Wong & Billie Chai[/TD]
[TD="bgcolor: #FFFF00"]3709 Astrocraft Dr[/TD]
[TD="bgcolor: #FFFF00"]Ledyard[/TD]
[TD="bgcolor: #FFFF00"]IA[/TD]
[TD="bgcolor: #FFFF00, align: right"]50556[/TD]
[TD="bgcolor: #FFFF00"]111 Maple St[/TD]
[TD="bgcolor: #FFFF00"]Louisville[/TD]
[TD="bgcolor: #FFFF00"]KY[/TD]
[TD="bgcolor: #FFFF00, align: right"]40229[/TD]

</tbody>

Sheet1

Output of macro:

Excel 2010

	A	B	C	D	E	F	G	H
Owners	Street1	City1	State1	Zip1	Street2	City2	State2	Zip2
Antuane & Tiffany Samarka	539 Brentwood Ave	Woolstock	IA	539 Brentwood Ave	Woolstock	IA
Sally Kellam & Kirk Bolder	278 Fleming Ave	Woolstock	IA	278 Fleming Ave	Woolstock	IA
Sally Jones	1822 Golden Dr	Woolstock	IA	552 Jones St	Louisville	KY
Thomas Wong & Billie Chai	3709 Astrocraft Dr	Ledyard	IA	111 Maple St	Louisville	KY
Sally Jones	552 Jones St	Louisville	KY	552 Jones St	Louisville	KY
Thomas Wong & Billie Chai	111 Maple St	Louisville	KY	111 Maple St	Louisville	KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]50599[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50599[/TD]

[TD="align: right"]40272[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]50556[/TD]

[TD="align: right"]40229[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]40272[/TD]

[TD="align: right"]40272[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]40229[/TD]

[TD="align: right"]40229[/TD]

</tbody>

Export

hiker95 · Feb 23, 2013

jeffcoleky,

Sample raw data in Sheet1:

Excel 2007

A

B

C

D

E

F

G

H

I

1

Owners

Street1

City1

State1

Zip1

Street2

City2

State2

Zip2

2

Antuane & Tiffany Samarka

539 Brentwood Ave

Woolstock

IA

50599

539 Brentwood Ave

Woolstock

IA

50599

3

Sally Kellam & Kirk Bolder

278 Fleming Ave

Woolstock

IA

50599

278 Fleming Ave

Woolstock

IA

50599

4

James & Connie Macdonald

1114 Lansford Dr

Woolstock

IA

50599

11114 Lansford Dr

Woolstock

IA

50599

5

Paula & Douglas Mills

1019 Allen Dr

Woolstock

KY

50599

146 Breckenridge Ln Apt B2

Woolstock

KY

50599

6

Theresa Jackson

1012 Blue Creek Dr

Woolstock

KY

50599

10112 Blue Creek Dr

Woolstock

KY

50599

7

Jon Smith

6214 Maravian Dr

Ledyard

IA

50556

123 Main St

Louisville

KY

40258

8

Sally Jones

1822 Golden Dr

Woolstock

IA

50599

552 Jones St

Louisville

KY

40272

9

Thomas Wong & Billie Chai

3709 Astrocraft Dr

Ledyard

IA

50556

111 Maple St

Louisville

KY

40229

10

Sheet1

After the macro in worksheet Export:

Excel 2007

A

B

C

D

E

F

G

H

I

1

Owners

Street1

City1

State1

Zip1

Street2

City2

State2

Zip2

2

Antuane & Tiffany Samarka

539 Brentwood Ave

Woolstock

IA

50599

539 Brentwood Ave

Woolstock

IA

50599

3

Sally Kellam & Kirk Bolder

278 Fleming Ave

Woolstock

IA

50599

278 Fleming Ave

Woolstock

IA

50599

4

Sally Jones

1822 Golden Dr

Woolstock

IA

50599

552 Jones St

Louisville

KY

40272

5

Sally Jones

552 Jones St

Louisville

KY

40272

552 Jones St

Louisville

KY

40272

6

Thomas Wong & Billie Chai

3709 Astrocraft Dr

Ledyard

IA

50599

111 Maple St

Louisville

KY

40229

7

Thomas Wong & Billie Chai

111 Maple St

Louisville

KY

40229

111 Maple St

Louisville

KY

40229

8

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 CheckDupeAddressV4()
' 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 b As Variant, lr As Long, lc As Long, r As Long, ii 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
  ReDim b(1 To (lr * 2), 1 To lc)
  ii = 1
  b(ii, 1) = .Cells(1, 1): b(ii, 2) = .Cells(1, 2): b(ii, 3) = .Cells(1, 3)
  b(ii, 4) = .Cells(1, 4): b(ii, 5) = .Cells(1, 5): b(ii, 6) = .Cells(1, 6)
  b(ii, 7) = .Cells(1, 7): b(ii, 8) = .Cells(1, 8): b(ii, 9) = .Cells(1, 9)
  For r = 2 To lr
    If .Cells(r, 1).Interior.Color = 65535 Then
      s1 = Application.Replace(.Cells(r, 2), 1, Application.Search(" ", .Cells(r, 2), 1), "")
      s2 = Application.Replace(.Cells(r, 6), 1, Application.Search(" ", .Cells(r, 6), 1), "")
      If s1 = s2 Then
        ii = ii + 1
        b(ii, 1) = .Cells(r, 1): b(ii, 2) = .Cells(r, 2): b(ii, 3) = .Cells(r, 3)
        b(ii, 4) = .Cells(r, 4): b(ii, 5) = .Cells(6, 5): b(ii, 6) = .Cells(r, 6)
        b(ii, 7) = .Cells(r, 7): b(ii, 8) = .Cells(r, 8): b(ii, 9) = .Cells(r, 9)
      ElseIf s1 <> s2 Then
        ii = ii + 1
        b(ii, 1) = .Cells(r, 1): b(ii, 2) = .Cells(r, 2): b(ii, 3) = .Cells(r, 3)
        b(ii, 4) = .Cells(r, 4): b(ii, 5) = .Cells(6, 5): b(ii, 6) = .Cells(r, 6)
        b(ii, 7) = .Cells(r, 7): b(ii, 8) = .Cells(r, 8): b(ii, 9) = .Cells(r, 9)
        ii = ii + 1
        b(ii, 1) = .Cells(r, 1): b(ii, 2) = .Cells(r, 6): b(ii, 3) = .Cells(r, 7)
        b(ii, 4) = .Cells(r, 8): b(ii, 5) = .Cells(r, 9): b(ii, 6) = .Cells(r, 6)
        b(ii, 7) = .Cells(r, 7): b(ii, 8) = .Cells(r, 8): b(ii, 9) = .Cells(r, 9)
      End If
    End If
  Next r
End With
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 CheckDupeAddressV4 macro.

jeffcoleky · Feb 23, 2013

Same result on my end as the last one... try highlighting ONE row and running macro, it still does all of them. In fact, now it doesn't copy all the zip codes over either.

Sorry to be so difficult. I can work around it the way it is if it's not going to be an easy change.

hiker95 · Feb 23, 2013

jeffcoleky,

How are you highlighting the data?

It could be that the highlighting you are using is not the same .Interior.Color = 65535 that I am finding on your posted screenshots.

I will need to see your sample workbook.

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

After 4 versions of the macro, if you cannot provide a sample workbook, then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

jeffcoleky · Feb 25, 2013

https://www.box.com/s/tw4cv0qi9qbdygpf1sry

hiker95 · Feb 25, 2013

jeffcoleky,

In your latest workbook, worksheet Message for Hiker95, contained:

Excel 2007

A

1

Dear Hiker95, You have the macro exactly right with only one change. I want to be able to run the macro on a SELECTION rather than on the entire sheet1.

2

**Normally I don't use colors but you have asked me to highlight the rows to show which rows I'm exporting so I have done so.

3

For example:

4

Assume I ONLY want to export rows 2, 3, 8, 9

5

I ctrl-select each row and run the macro.

6

The end result is displayed in the "Export" Tab

7

8

9

10

THANK YOU FOR YOUR HELP!

Message for Hiker95

There has been unclear communication concerning the results you are looking for. And, there has been 4 versions of the macro because of this.

I just tried to use your row selection of non-contiguous rows, and, I can not get the data into an array.

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

jeffcoleky · Feb 25, 2013

hiker95 said:
jeffcoleky,

In your latest workbook, worksheet Message for Hiker95, contained:

I just tried to use your row selection of non-contiguous rows, and, I can not get the data into an array.

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

I can appreciate your desire to move on. That's not a problem.

However, the issue could be that you didn't download the file. There are two more tabs in that file and Box will only display the first one in "Preview" mode. I'm confused as to why you cannot access the other tab in the attached file to use it in an array--it's a simple new workbook. I'm also baffled as to how to explain the final change any differently.

No matter though! We'll just call this project "Done". I can make do with what you have given me. It is 90% of what i needed and it saves me a LOT of time just the way it is. I just need to add an extra step of copying the rows I want exported to a new sheet before running the macro. Then it should work fine--even with the first version.

Thank you again for your time! I apologize for being unclear.

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

jeffcoleky

Active Member

hiker95

Well-known Member

Excel Facts

jeffcoleky

Active Member

hiker95

Well-known Member

jeffcoleky

Active Member

hiker95

Well-known Member

jeffcoleky

Active Member

hiker95

Well-known Member

jeffcoleky

Active Member

Similar threads

Forum statistics

Share this page

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

Active Member

Well-known Member

Excel Facts

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock