analysing multiple references contained in one text string

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
greetings all

I need to link the start date of one task, to the final end date of any possible combination of previous tasks. Sadly, the precedent tasks will be identified only by a single cell entry containing the name of every relevent one

If only one precedent task, then the start date of the dependent would be simply vlookup (precedent reference)

But since there might be multiple precedents, all details contained within a a cell value of e.g. ("precedent1", "precedent2", etc), I need a function to read all of these in turn, and return the maximum end date of each

As a final pain in my @rse, this function will be in a larger model, containing a lot of statistical processing, hence I can't have a long-winded way of analysing this, as the requirement to recalculate after each iteration (expected 10,000) will slow the model down too much. This is an integral calculation, so cannot be switched off in any way

Preferred way forward would be to write a specific VBA function that effectively recreates VLOOKUP, or maybe INDIRECT or similar, but using multiple references contained in a single text string. Effectively Im trying to recreate certain functionality from MS Project, and convert into Excel for use in calculations. Alternative methods are invited!

Any help is much appreciated
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Can't understand how your data is constructed. Are you saying you have a list of precedent tasks, with end dates adjacent to them, and you want to find the latest date for any tasks which contain a certain word??

With a list of words in A1:A7 and dates in B1:B7 then you can pick out the latest date of any word containing a string in C1 by using:

=MAX(IF(ISNUMBER(SEARCH(C1,$A$1:$A$7)),$B$1:$B$7,0))

Am I anywhere near the right track?
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
ok, simplified version of problem...

4 tasks, T1 - T4

Start of T1 is say 2009. Duration is variable, say 2 years
Start of T2 is end date of T1. Cell in [row applicable to T2] tells the model this, as it contains the value "T1"
Start of T3 is also 2009, but end date is as yet unknown
Start of T4 is dependent on completion of T2 and T3, hence it must be the higher of the two end dates. Cell in [row applicable to T4] tells the model this, as it contains the value "T2, T3"

I need a function that can look at the cell containing "T2, T3", do a VLOOKUP on both T2 and T3, and return the higher of the two end dates

Hope this helps, it seems easier to explain when you are looking at the problem..
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
Yard - possibly, but the string in C1 will be any combination of the specific words in A1:A7, and I need the highest (single) date of all that are listed in C1, if that makes sense?!
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
Hmmm, I figured this might be tricky

I think I will write a routine that looks at the precedents list in turn, and rewrites specific formulae for each line, depending on the criteria, i.e. whether cell is (a) blank, (b) contains "," within text, or (c) neither

Thanks for looking anyway
 

Watch MrExcel Video

Forum statistics

Threads
1,122,914
Messages
5,598,840
Members
414,261
Latest member
KatieBsc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top