Match index not sure the best way

**justinwb** · 01-25-2018, 12:56 AM

Good afternoon

I have 3 sheets
picking sheet gets updated every few hours then gets delete at end of shift with new next days work
Timeslot Tracker sheet gets replaced at end of shift
Pick order sheet will get all the information from the 2 other sheets to make a list from
only the headers will stay

I have set up a sample of how I am trying to set up the Pick order sheet

I have tried to use vlookup I think match index will be better cause its needs to look left and right
and return the same Truck ID and load numbers and pick up time with different Order ID

On pick order sheet
The order the table needs to be sorted is Truck Id Pick up time
the problem I have is the load number is not unique there can be different Order ID under the same Load number Which is why the vlookup I was trying to do is not working cause it have to return
mult results from the same load number

I have attached a sample with the first 6 Truck ID's some days we can have up to 60 truck ID's with up to 150 Order ID Trying to sort this data out has been a lot of work

The main problem I have is the only thing the time slot tracker and picking sheet have in common are the load no it is it in any random order I have deleted all other data on the sheets that are not needed for the sample

picking and Timeslot tracker Sheets cells cant change order as they are pulled from our system at work via a export

any help would be great

JustinwB

**mike7952** · 01-25-2018, 04:31 AM

Someone maybe able to come up with a formula solution but this Macro will work if you can use it. See attached workbook. Click button...

Please Login or Register  to view this content.

**justinwb** · 01-25-2018, 04:42 AM

Good afternoon mike7952

you are the best this works perfectly
did not even think of using a Macro Still a very beginner with excel formulas
have not used much macros at all Do you use software/addon to be able to write that up or was that all going on having learning excel over a long time

I found 1 Bug when I Press Click here it deletes all data from Pick order which is great etc it also deletes the header
I went over all the code

With Worksheets(cShTimeslot)
arr = .Range("B2", .Cells(Rows.Count, "G").End(xlUp))
End With
With Worksheets(cShPickOrder)
With .Range("A2", .Cells(Rows.Count, 2).End(xlUp).Offset(1)).Resize(, 7) changed Rows.Count, from 1 to 2 which seem to fix the problem
.ClearContents
.Borders.LineStyle = xlNone
End With

Still looking over the code cant work out how it all works
Lots more learning to do

Thanks again for your help

JustinwB

**mike7952** · 01-25-2018, 09:54 AM

Add the line in Red at the bottom to add the headers. Add no software or add-on, its be years of learning.

Please Login or Register  to view this content.

**justinwb** · 01-25-2018, 05:05 PM

Thanks Mike7952

you are awesome

one day I love be able to read code like this still many years off

enjoy your weekend

JustinwB

Match index not sure the best way

LinkBack

Thread Tools

Rate This Thread

Display

Match index not sure the best way

Re: Match index not sure the best way

Re: Match index not sure the best way

Re: Match index not sure the best way

Re: Match index not sure the best way

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?

[SOLVED] Index / Match - match 3 input values and return the results from the index

[SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays

INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges

Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

Index, match, dynamic and connected without sorting helper column non-adjacent index match

[SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

Bookmarks

Bookmarks

Posting Permissions