Compare lists between two sheets

Dolez013

New Member
Joined
Sep 17, 2014
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I'll try my best to explain.

In one sheet (A) we have a Column of zip code. In another (B) we have a list of counties with the zip codes they include. What is to match the zips in A with the County name in B.

So Sheet A

62301
62962
61011
... (up to 1000 entries)

Sheet B

Adams62301623056230662320623246232562338
Alexander629146295762962629696298862990
Bond62019620866224662253622736227562284

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

So what I would then want would be
62301 Adams
62246 Bond
62962 Alexander

The number of columns for zips codes vary. I tried to illustrate that above.
Thanks, K.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Dolez013,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample worksheets:


Excel 2007
ABCDEFGHI
1Adams62301623056230662320623246232562338
2Alexander629146295762962629696298862990
3Bond62019620866224662253622736227562284
4
B



Excel 2007
AB
162301
262962
361011
462246
562962
6
A


After the macro in worksheet A:


Excel 2007
AB
162301Adams
262962Alexander
361011
462246Bond
562962Alexander
6
A


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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub GetCounty()
' hiker95, 09/17/2014, ME806135
Dim wa As Worksheet, wb As Worksheet
Dim c As Range, zc As Range
Application.ScreenUpdating = False
Set wa = Sheets("A")
Set wb = Sheets("B")
With wa
  For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set zc = wb.UsedRange.Find(c.Value, LookAt:=xlWhole)
    If Not zc Is Nothing Then
      c.Offset(, 1).Value = wb.Cells(zc.Row, 1).Value
      Set zc = Nothing
    End If
  Next c
  .Columns(2).AutoFit
End With
Application.ScreenUpdating = True
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 GetCounty macro.
 
Last edited:
Upvote 0
Works like a charm.

2013, PC btw.

Thank for all the help.

Kent.
 
Upvote 0
Dolez013,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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