Excel wizardry challenge

[adolf hamster]

ok, so i have a sheet that i'm using for some project planning, it has a list of tasks and each one has a column for which task has it's parent and children.

what i'm wanting to do is auto-fill the parents column, so say we have the following:

Task number task parents task children
1 4,2
2 1 4
3 4
4 1,2,3

so basically what i want is to be able to search the cells in the task children for references to the task number and return their corresponding task numbers in the parents column.

the tricky thing is the standard index/match will only return the first reference to it, but i need to return multiple references to a single cell.

if it helps the syntax of the children column can be changed to whatever format is needed, i'm thinking comma seperated might make it easiest?

and yes i know i should be using project for this, but damn the licenses are expensive.

 

[adolf hamster]

bah, typical, it didn't put in the spaces for that example

Task number task parents task children
1________________________4,2
2_______________1________4
3________________________4
4_____________1,2,3

i did find a macro based solution that works, but wont handle more than 1 value for children,so in the above example it would look thus:

Task number task parents task children
1________________________4,2
2________________________4
3________________________4
4______________2,3

unfortunately i dont have access, or even better project, which are really the right tools for this job so i'm having to make do with excel.

the idea is this table when fleshed out with start/end dates, task times etc is going to be a pseudo gantt chart but due to the size of the project i was wanting to automate some of these bits as it'll be a right pain to do manually.

 

[adolf hamster]

Do you need both task parents and task children? It seems these are always going to be the inverse of each other, at least at some level. Couldn’t you just set the task parents for each task? That might simplify things.

it makes it easier to find what you need in either direction, i could do the parents/children manually but i live by the code of if in doubt automate.

 

[adolf hamster]

Ah, ok, sorry that’s where I was headed anyway. Teach me to read the post properly...

So you want to parse the parent list of each task and update the child fied, or whichever way round, yes?

I think you will need a macro, because it’s an iterative process. For each parent task you need to look at an unspecified number of tasks in a list and generate an array of tasks with that task given as the parent and then spit that array into the child field. I suppose you could do it with formulae with concatenation and matching, but you’d end up with very long formulae (is there a cap on length?) and they’d all need updating every time a task was added.

The cap on formula length is 8,192 characters, so you’re unlikely to hit that, but even so.

indeed, the problem is when the child field has seperated values that'd need split up, although so far the one ive found online seems to work ok as long as each process has only 1 child.