Taking one line and adding multiple lines

Sorry about the title on this one. I was having a hard time explaining it.

What I am trying to do this this.

I have a new employee that is hired. That employee is hired into a department. Based on that department, that employee may be able to work in many other departments.

I could look like this:

Employee 1234 is hired into department 619 - Therefore they can also work in 620, 622, 625 and 626
Employee abcd is hired into department 620 - Therefore they can also work in 619, 633, 645 and 700

Our system would need the data in this format

Screen Shot 2021-07-27 at 10.45.28 AM
thought on ho

I was thinking I could do an if/then function. I could have it return something like

If home = 619, then the return field could be 620, 622, 625, 626. Then maybe somehow transpose it? but then how would I get the employee id to also be associated with each line.

I think you should return to the data analysis. At the moment the employee / department table above might be deceptive because it hides the bidirectional one to many relationship i.e unless a department only has one employee, then ‘abcd’ could also work for department 620.

The other issue is that it is not clear whether the ‘department hired for’ is material - at the moment that property doesn’t appear.

IMHO I’d consider if you could semi normalise it, which implies an employee list and a department list, and then use your linking list in the middle, with the additional property of ‘hired for’ so a single true value would be placed against the relevant pair. You could then use ‘Lookup’ to pull the relationships together.

I’m not sure I understand. Is the table shown above the input or the expected ouput?

If you can give me example input and expected ouput I can hopefully give you an answer.

Yall are awesome.

What happens is we get a list of new employees. Lets say that a nurse is hired into department 619, which means she can also work in 620, 622, 625, 626. That is known information and not necessarily on a data table anywhere as im not sure I would know how to set that up correctly.

Screen Shot 2021-07-27 at 3.07.46 PM

The output I would need is what I copied above. The data I get would show me the new employees, which would be my first filter. Then I would have a list of them, the department they are hired in to. Then my secondary data source would be the table, like I just pasted, that shows the hire dept and the optional departments. We can get as man as 150 new employees a week. The output would need to be in the format of:

each original department could have anywhere from zero optional to 10 optional.

I hope im explaining this well enough! keep asking if not please.

an example of what the original file would look like

So I need to get that line, into the following format. Maybe I should have just done this at the beginning.

Screen Shot 2021-07-27 at 3.17.14 PM

I think you can do this easily using Lookup then Split Col into rows.

multi-line.transform (2.8 KB)

As @yorkeman comments, you need to be careful about the structure of the data or things might get messy.

Thank you all! It worked!

1 Like