Spreadsheet idea containing formulas over my current skill level...

[blaze89]

Hello all,

This is my first post here so hello everyone, pleased to meet you all I'm a self taught Excel user so my skills at this stage are unfortunately a bit limited and quite rough on the edges as well.

I recently started working for a new company and I'm working on a spreadsheet that'll make my and my colleagues lives easier. We've got a sheet with driver names and the delivery locations. Until now, the guys here were retyping all the locations manually from another spreadsheet and matching them to the appropriate driver for the particular day. It looks like this:

The database from the other spreadsheet, after being pasted into an additional sheet in the file from the screen shot up looks like this:

What I want the spreadsheet to do is that you copy the database into a blank sheet in the file and it automatically fills itself in with the correct locations to the appropriate drivers. I was thinking of using the formulas INDEX and MATCH, but I am not sure how to modify the formula in order for it to find the data I want. When I use =INDEX(Sheet1!$4:$4,MATCH('Driver Names'!C4,Sheet1!$12:$12,0)) it sends me directly above the Driver ID in the database sheet and I want it to find the data 2 cells to the right instead. There is also a possibility for a driver to have several locations for 1 run, which means probably the IF formula, that IF the cell 2 cells to the right is full then return the value in the same cell after a coma, for example. I figured that the CONCATENATE formula might come in place in this instance, but I am really clueless how to make it all work together nice and tidily. There are also 2nd runs for drivers, which means the same name is going to appear in the database sheet 2x, so I guess the IF function again for if the 1st RUN cell is filled in with a location already then find another same Diver ID in the sheet and use the 2nd data and not the 1st one. It is a really complicated spreadsheet for me and I need some guidance from you guys. I'm still learning the arts of Excel so please forgive my noobness If anything is not clear or my explanation seems a bit off, please don't hesitate to ask me for clarification.

Thanks in advance for any advice!

 

[Rijnsent]

Hi Blaze,
looking at your example data (second screenshot), what is the desired outcome per driver? For Driver#1 I see only one location is visited and Driver#2&3 visits 3 locations. You could use a MATCH + OFFSET formula probably, but if you can show the desired outcome, that will prove helpful.
Cheers,
Koen

 

[blaze89]

Hi there,

Thanks for your reply.

You are correct, Driver #1's got one location, drivers 2&3 have 3 each. Please see an example below:

The locations, of course, would be automatically matched from a formula in the cells while using the data from the database sheet. I hope the example shows more or less what I mean.

 

[Rijnsent]

Okay, it'll be a bit complicated formula if you want to achieve your example exactly, but can be done like so:

step 1: =MATCH('Driver Names'!C4,Sheet1!$12:$12,0) -> gives back 3, let's call this STEP1
step 2: =OFFSET('Driver Names'!$A$4,0,STEP1+1) -> gives back Loacation#1. The next step is a bit depending on how elegant you want to make it, you can go all out with IF() formulas to check if the cells to the right are empty, but the easiest is:
step 3: =CONCATENATE(OFFSET('Driver Names'!$A$4,0,STEP1+1),OFFSET('Driver Names'!$A$4,0,STEP1+3),OFFSET('Driver Names'!$A$4,0,STEP1+5))
That gives back a list of locations, you can make it nicer looking with some IF statements.
Hope that helps,
Koen

 

[blaze89]

Hi,

I think you accidentally put "Driver Names" instead of "Sheet1" in the OFFSET formula, but other than that it works great, big THANK YOU!

Would you be able to help out with the 2nd run with the IF formula, so that the formula finds the 2nd time the same name is used and returns the value similarly to the 1st one, but without returning the same value from the 1st run?

Many thanks one again!

 

[blaze89]

Sorry, my message didn't make sense when I read it after posting it... Let me rephrase that:

So I want the second formula to consider the 1st run for each and every individual driver and return a value that indicates only the 2nd run, without the locations already used for the same driver name from the 1st run. I think using the IF formula will be the solution, but I'm not sure how to create the IF considering the formula from the cells from the 1st run.

 

[Rijnsent]

Hi Blaze,
do you have a screenshot of an example with a 2nd run? I don't see it in your current example, so have a hard time imagining what a formula would need to look like.
Cheers,
Koen

 

[blaze89]

Hi Koen,

Please see the data base example below:

The result for the Driver ID #1 would look like this in the Driver Names sheet:

Hope this helps.

I also stumbled upon an issue while using the =CONCATENATE(OFFSET('Driver Names'!$A$4,0,STEP1+1),OFFSET('Driver Names'!$A$4,0,STEP1+3),OFFSET('Driver Names'!$A$4,0,STEP1+5)) formula, as there are some instances when I need to go up to +11 or +13. The formula then sometimes returns another driver's runs, because the runs are in the exact same cells as the formula's range:

Is there any formula that would make the formula stop searching for next filled in cells, if on of the results is an empty cell? I tried using the IF formula for each and every search, but it didn't quite work...

Thanks in advance.
Blaze

 

[Rijnsent]

Hi Blaze,

Some formulas that might help you with the next step:

C33: the value Driver ID #1
D33: =COUNTIF(12:12,C33) -> counting the number of times that Driver #1 shows up in row 12
E33: =MATCH(C33,OFFSET(12:12,0,0,1,COLUMNS(12:12)),0) -> column with the first occurence
F33: =MATCH(C33,OFFSET(12:12,0,E33,1,COLUMNS(12:12)-E33),0)+E33 -> column with the second occurence
G33: =MATCH("*",OFFSET(12:12,0,E33,1,COLUMNS(12:12)-E33),0)+E33 ->column with the next record
H33: =(G33-E33-3)/2 -> the number of locations visited (for the IF formulas)

Those extra variables can help building the list of locations visited in a run.
Does that work for you?
Cheers,
Koen

 

[blaze89]

Hi again,

So I used this formula:

=IF(MATCH(C4, OFFSET(Sheet1!$12:$12, 0, MATCH(C4, OFFSET(Sheet1!$12:$12, 0, 0, 1, COLUMNS(Sheet1!$12:$12)), 0), 1, COLUMNS(Sheet1!$12:$12)-MATCH(C4, OFFSET(Sheet1!$12:$12, 0, 0, 1, COLUMNS(Sheet1!$12:$12)), 0)), 0)+MATCH(C4, OFFSET(Sheet1!$12:$12, 0, 0, 1, COLUMNS(Sheet1!$12:$12)), 0),

and as the "value if true" I put the formula I have in the cell for the 1st run. It works for the drivers who do not have any second runs, as the formula returns an error, which is then blanked by IFERROR, but the values for drivers who do have a 2nd run are exactly the same as the ones in their 1st run. I'm not sure if it's my logic at fault here or if the formula is missing something.