Minimum distance between points

Hi!

Hi can't seem to make this work and I wouldn't like to use VBA to solve this because I believe that built-in functions can do the trick.

I want to know the minimum distance between customers that I visited in each day.

If i visit customer 1, 2 and 5 in one day, which distance is smaller? 1-2, 2-5 ou 1-5 and what is the corresponding value?

I have in columns DATE, CUSTOMER 1, CUSTOMER 2, ..., CUSTOMER 5 and in each row I put the Day and 1 or blank for each customer (visited / not visited). I also have a mileage chart for each customer in another sheet which is the source for my distance data.

I tried INDEX and MATCH but all I get is ZERO... I think it is comparing the same CUSTOMER.

Any ideas?

Thank you in advance!

It would be more helpful if you posted your workbook.

Here it is.

thank you

Still no contributions?

Thank you for your reply.

However, I believe you missed the point of the question. Your answer returns the minimum distance of all the points, regardless of where they travel to.

In the book I uploaded I wrote the correct values to be returned and your formula does not give me that. It's a bit more complicated than that.

Thank you anyway

In day 2, the value should be 18.9. However, your formula returns 3.4.

And your formula returns different values in my sheet (maybe it's my mistake).

Please notice that both sheets in the book are not similar...

THank you!

Yes!

Thank you!

Ok, for my knowledge I thought that there would be a way using excel formulas...

I will try VBA then.

Thank you!

For ease to solve your problem, I use zero to replace -- and blank.

Sheet2!G2

Please Login or Register  to view this content.

Committed with Ctrl+Shift+Enter. copied down.
You can also see attached.

Hope this helps,
windknife

Great solution Windknife. Well done!

Simply overwhelmed...



THANK YOU!

You are welcome ^_^

WindKnife