MACRO to Run an Index Match for Multiple Values, returning multiple Rows

[akasnett]

Hi,

Sorry about the long title. After spending all morning on this it seems that this is more complicated then I thought it would be...

I attached an image with my spread sheet.

Column A is a list of full zip codes that I have from my system
Column B is the first 3 char's of each of those
-
Column C is the first 3 chars of a zip provided by the carrier i am using
Column D is the code for that zip.

What is need is the full zip that starts with the first 3 characters provided by the carrier (THERE WILL BE MULTIPLE RESULTS)
And the corresponding code from Column D.
Any way to do this not line by line copying the index of column C into the formula is have in J2 and dragging to get all the results and then copying it to a new sheet with the code and moving to the next row etc etc....

 

[braindiesel]

I will be happy to help you if I can.
What I need from you based on the image is the exact output you want based on 005 or 010... 005 will be easier... so I can begin with the end in mind.
013 is not in the image to the left so less helpful

 

[akasnett]

Thanks so much for trying to help!

Snippet Sample :

Zip Code	LEFT	Dest. ZIP	Ground
00501	005	004	002
00544	005	005	002
01001	010	006	045
01002	010	007	045
01003	010	009	045
01004	010	010	002

I want to see:

Zip	GroundCode
00501	002
00544	002

So I am looking up the Dest. Zip in the column called LEFT and returning the Zip Code, but being the amount of records, its taking forever to do it so many times

Example Formula:
=INDEX($A$2:$A$40472,AGGREGATE(15,6,($B$2:$B$40471=$C$10)/($B$2:$B$40471=$C$10)*ROW(FullZip),ROWS($J3:J$7)))

but for each new prefix, I am changing the bolded text.....

I dont see a way for me to attach my whole file

 

[braindiesel]

Cool. Heading home but I will play with this tonight

 

[akasnett]

I was overcomplicating it.
I did it the opposite way... I pull the first 3 of the zip to match the prefix and then pulled the code...

Tho the prefix's originally came in ranges, i wish thier was a way to expand things like this automatically

Zip Prefix	Code
875-877	006
878-880	007
881-882	006
883	007
884	006
885	007
889-961	008
970-986	008
988-994	008

for example on the first line, i need to expand it to its own row for
875 006
876 006
877 006

 

[braindiesel]

Sub getItTogether()
'
'   by BrainDiesel
'
    Range("C2").Select
    Application.ScreenUpdating = False
    myNow = ActiveCell.Row
    Do Until Cells(myNow, 1).Value = ""
    ActiveCell.Formula = "=+LEFT(A" & myNow & ",3)"
    ActiveCell.Offset(0, 1).Formula = "=if(LEN(A" & myNow & ")=3,0,RIGHT(A" & myNow & ",len(A" & myNow & ")-FIND(""-"",A" & myNow & ",1))-LEFT(A" & myNow & ",3))"
    myRows = ActiveCell.Offset(0, 1).Value
    ActiveCell.Offset(0, 1).ClearContents
    Selection.Value = Selection.Value
    If myRows > 0 Then Selection.AutoFill Destination:=Range(Selection, Selection.Offset(myRows, 0)), Type:=xlFillSeries
    Selection.Value = Selection.Value & " " & Format(Cells(myNow, 2).Value, "000")
    ActiveCell.Offset(1, 0).Select
    Do Until myRows = 0
        Selection.Value = Selection.Value & " " & Format(Cells(myNow, 2).Value, "000")
        myRows = myRows - 1
        ActiveCell.Offset(1, 0).Select
       
    Loop
    myNow = myNow + 1
   
    Loop
    Application.ScreenUpdating = True
    Range("A1").Select
   
End Sub

Book2
	A	B
1	Zip Prefix	Code
2	875-877	6
3	878-880	7
4	881-882	6
5	883	7
6	884	6
7	885	7
8	889-961	8
9	970-986	8
10	988-994	8
Sheet1

The code above on this spreadsheet seems to give what you are looking for
This code presumes always having the firse three characters included... if you have 1000-1004, the starting point will be 100, not 1000... first three characters... is that a concern?

Looping adds time so there may be a faster way, but this is my solution otherwise

Hope this solves your problem