Hello,
I'm hoping you can help me with a problem im facing while attempting to put in place a macro to automate one of the files we use. The table is arranged as so:
http://imageshack.us/photo/my-images/824/questionp.jpg/
What my aim is with the macro, is to label each of the individuals with only one location. That location will be the location in which the most recent cost occured (e.g. for person E in the above example, it would label him as 'UK'). A lookup is hence not suitable as it would only pick the first location in the data.
There's a few catches, the data which produces this report does not have a fixed amount of weeks, and hence I would like it to work should with any number of columns.
Also, there may be some individuals on there which have over 10 locations (although very much in the minority) and hence a nested IF statement would be far too long.
Finally, there may be some individuals with no cost in some weeks (see C) or who may have no costs at the start (see E/F).
I am trying to be as flexible as possible so that the process works whatever the data looks like, but am currently struggling. The ideal formula would be to check if the same ID occoured in the first columns range, and if it did then provide me with the location who's cost occured most recently - else simply provide their location (as they've only got one line).
If anyone has any ideas, I would be very grateful.
Thanks
I'm hoping you can help me with a problem im facing while attempting to put in place a macro to automate one of the files we use. The table is arranged as so:
http://imageshack.us/photo/my-images/824/questionp.jpg/
What my aim is with the macro, is to label each of the individuals with only one location. That location will be the location in which the most recent cost occured (e.g. for person E in the above example, it would label him as 'UK'). A lookup is hence not suitable as it would only pick the first location in the data.
There's a few catches, the data which produces this report does not have a fixed amount of weeks, and hence I would like it to work should with any number of columns.
Also, there may be some individuals on there which have over 10 locations (although very much in the minority) and hence a nested IF statement would be far too long.
Finally, there may be some individuals with no cost in some weeks (see C) or who may have no costs at the start (see E/F).
I am trying to be as flexible as possible so that the process works whatever the data looks like, but am currently struggling. The ideal formula would be to check if the same ID occoured in the first columns range, and if it did then provide me with the location who's cost occured most recently - else simply provide their location (as they've only got one line).
If anyone has any ideas, I would be very grateful.
Thanks
