sorting an order of contract type

[Sean Thomas]

Hi,
I have a situation that I would like to see if anyone has any good ideas on a solution.
Basically I have 2 sheets
In Contract (these are sites with a current contract)
Terminated (previous contracts)

Sites may have 3 contracts in place ie A, Peugeot, Citroen
The contracts available are in order of priority:
ARC
A
B
VM but will be listed in the 2 sheets as either Peugeot or Citroen
NRG
NS
Not contracted

What I need to do is create sheet3 which is a list of unique sites based on site id and a history of their contracts

ie Site name, site id, earliest contract date, contract type, next contract date & contract type, next contract date & contract type, next contract date & contract type

so say site Rep1 was originally contract Peugeot 01/01/14 to 01/01/17, but also took on a contract A on 01/04/16 to 01/01/17 then they would read on sheet3:
Rep1, Rep1 site id, 01/01/14, VM, 01/04/16, A, 01/01/17, Not Contracted

and say site Rep2 was originally contract B 01/01/14 to 31/01/15, and then not conctracted until they took on contract NRG 01/05/16 to 01/01/17 then they would read on sheet3:
Rep2, Rep2 site id, 01/01/14, B, 01/05/16, NRG, 01/01/17, Not Contracted

I have attached a sample set of data so hopefully you can get the picture.

The idea is i have a set of data going back to 01/01/14 to present date and need to pull the data out based on their site id and then produce a report sumarising the data based on the the various type of contracts.
so in that time one site may have been a contract A, VM and also not contracted. but their data will be split up. This part i have already sorted by using the table in sheet 3 as it will look up the site id and then look up the date and find the nearest date and return the contract type. I do not expect for a site to have more than 4 contract periods hence only 4 colums sets used.

I was thinking of first off getting a list of unique ids from the first 2 sheets, then running through each site id, pulling the relevant contract types and dates, sorting them by date order and then contract type and then checking through the list and putting them in order in the chart in sheet 3.

Has anyone got any fancy ideas on how to enumerate this info quickly?

[LJMetzger]

Hi Sean,

The attached file contains the following VBA Code. I had one problem, probably because I did not understand your requirements. I put down 'VM' twice where you have it only once.

The algorithm I used was:
Possible Solution Algorithm:
a. Create a list of items in a string: Site ID, Start Date (as yyyymmdd), Coded Priority, End Date (as yyyymmdd)
e.g. '1234 , 20140101, BBA , 20170301'
b. Sort the Strings
c. Put the Strings in the Output List (removing the first 2 characters) of the encoded value.
d. Put the Latest End Date for a 'Site Id' as 'Not Contracted'

Encode the Site Ids in Priority order as:
AAARC
BBA
CCB
DDVM
EENRG
FFNS
GGNot contracted

Please Login or Register  to view this content.

Lewis

[Sean Thomas]

Lewis you are a star!
I wasn't expecting you to write the code for me, I was mainly after some different ideas.
But you have provided a perfect solution which I can use - outstanding!

I have made one alteration though.
Because both the Peugeot & Citroen contracts will always be the same dates and assigned to an overview contract name VM, they only need one entry.
So I have just checked to see if the new string is different to the previous string and if so move onto to the next one

Please Login or Register  to view this content.

This seems to work perfectly.
I will try it out on my full data set to see how it operates
Thanks again, glad to see you are still writing superb code that I can only hope to match one day.

I have finally secured a role where I can use my excel skills, so I'm slowly refreshing my skill set as I have got a little rusty the last year or so as I haven't been doing that much.

[LJMetzger]

Hi Sean,

It's good to see you active again. I was going to suggest the fix you posted if couldn't fix it yourself. I figured I had to let you have a little fun.

Lewis