Hi all, above I have attached two tables of data which is a sample of what I'm working on (the actual tables are each 10,000+ rows long).
What i need to do is identify those agreements in B that have been started within 10 days of an intial survey for the specific client in A. Now the only unique identifier we have in our system to link the two is the Client ref and as you can see there is the possibility of multiple surveys and agreements.
I have shown in italic within table A the results I expect to get...but using Vlookup I'm stuck on defining the formula so that it returns the correct data....any help would be appreciated as i'm sure this would be a sinch for you guys.
Ian
Last edited by lyonsi01; 01-26-2012 at 06:19 AM.
Hello,
what is the logical relationship between the data in the green table and the data in the red table? For example, in row 11, where does that value come from? The red table has multiple entries with the same client ID and the same agreement start date. What determines the start date in the green table?
Can you describe it in words?
cheers,
pike, didn't see your comment before posting, but apart from the drama, the title is fine with me.
Hi Teylyn,
Ok I’ll try.
Essentially it’s a “many to many” relationship between Table A and table B.
Surveys are carried out for clients from which service agreements are generated. We currently do not have a unique identifier to link the two pieces of work (which having not long been here defies belief). It is what it is though so, I need to try and identify a way of identifying how many of the Surveys in Table A have had a “potential” Agreement started within 10 days for the client. You’ll note that I have said ‘potentially’ because that’s exactly what the formula would be identifying; however this formula would save me days of work individually checking the entire list one by one.
So in essence would I would like to do is based on the Client iD in Table A…lookup the same Client iD in Table B and then return the information if there is a Agreement that has started within 10days (After) the date the Survey was started.
Was that any clearer?
>> Was that any clearer?
Not really.
If you have many entries for the same client, how do you expect Excel to identify the "correct" item to show in the list? You will need to put the logic into simple words. If you can do that, the logic can be translated into some formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks