is there a better way to sort

[CPT]

I have a list of locations and their ranking of nearness to individual customers ie 2 columns and a list of locations to include/consider.

The data I'm using has 10+ locations repeated for hundreds of customers in varying order and I want look at various scenarios where locations may be removed to see how removing locations may effect customers demand at other locations.

If I remove a number of locations from 'locations to include' how do i create a new column with the highest ranked remaining location and make that the new first ranked other than using by using a lot of embedded IFs or IF and multiple look ups - this seems very inefficient

Is there a better way ie can i identify the first value that is in the reduced locations list???.

[Richard Buttrey]

Hi,

It's not clear, at least to me, exactly what you want to achieve. Please manually add some examples of results for various lists of included locations so that we can see the goal.

[mehmetcik]

Hi

The way I would approach this is to list your locations and customers on a single sheet. Columns A and b

Create your formulas to calculate your demand Column C.

Write a macro to create your macros automatically just in case they get corrupted.

*write a new macro to insert a new Column at Column D and to copy paste value your result from Column c into column D with a suitable Header.
repeat * as many times as you need deleting a differrent location or locations each time.

[CPT]

I agree- not fully explained initially...

I need to know what is the highest ranked valid/selected location for each customer based on the selection in the lookup.
A new sheet has been added. It must be one value only per customer. It can be assumed that the lookup wont remove all the customers locations

[aydeegee]

In order to understand your requirements, I have assumed there is a typo in E9 and it should read "d"

I have used a helper column and Index and Match

Let me know if I have missed something

Regards
Alastair