Compare lists between two sheets

[Dolez013]

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

Adams	62301	62305	62306	62320	62324	62325	62338
Alexander	62914	62957	62962	62969	62988	62990
Bond	62019	62086	62246	62253	62273	62275	62284

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

 

[hiker95]

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
	A	B	C	D	E	F	G	H	I
1	Adams	62301	62305	62306	62320	62324	62325	62338
2	Alexander	62914	62957	62962	62969	62988	62990
3	Bond	62019	62086	62246	62253	62273	62275	62284
4
B

Excel 2007
	A	B
1	62301
2	62962
3	61011
4	62246
5	62962
6
A

After the macro in worksheet A:

Excel 2007
	A	B
1	62301	Adams
2	62962	Alexander
3	61011
4	62246	Bond
5	62962	Alexander
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.

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.

 

[Dolez013]

Works like a charm.

2013, PC btw.

Thank for all the help.

Kent.

 

[hiker95]

Dolez013,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.