Adding Rows to Data without losing existing data

[maddiefindlen]

Hi everyone! 

I'm trying to add in the new rows with data in the "Supplies Needed" column without losing the existing data in the first few columns. The office name is the unique indicator that the following rows need to be connected to.

The supplies needed are based on the office location, which all contain different number of supplies so I can't add a set number of rows (it needs to be dependent on the location, so in the list below offices in Dallas would need more rows added than Houston)

The ultimate output would look like this:

There will be multiple offices for each location, so would need to be able to repeat it for every unique office name located in one of the cities.

I have attached an excel with the input, lists, and goal output to this post. I've been working on the list and the input data as two separate inputs. I've tried to filter the input data set by office, then transpose the list and append the fields together - I'm struggling with the multirow formula to then removed the additional data (ie. the office name in every row) because we don't want to repeat things like the office name in every row.

If you have any thoughts or different approaches, please let me know! would much appreciate as this is a huge data set!

[JosephSerpis]

Hi @maddiefindlen I mocked up a workflow that produces your output let me know what you think?

[benakesh · Accepted solution]

The tables were  created for each data set  and merged  to create  final report.  The headings repeat for each group .

Some additional  processing may be required  to  read  output  and remove  group headings.

Hope this helps.

[maddiefindlen]

Hi JS420, Thanks for the response - I am unable to import the workflow although I have recently updated my Alteryx - not sure how to address this problem otherwise, thanks!

[maddiefindlen]

Thanks Benakesh - I think this is getting me where I need to be - I think the only missing piece is in my data set that are additional columns in between the office and supplies needed columns that I would like to retain the information from. I've tried selecting them in the multi-row formula but I still loose them in the output. Do you have any suggestions for tackling this problem? Thanks!

[estherb47]

Hi @maddiefindlen 

A little convoluted, but here's a solution. Incidentally, having the empty cells makes this data much harder to analyze, because the records aren't really linked one to another by anything but the visual linkage. Programs don't understand visual linkage.

Matching the supplies to each city is easy (bottom part of the above flow). A transpose tool, then joined to the original data by city. This gives all of the records needed for each location (and would work for multiple offices within each city too)

Then we need to figure out how many workers are needed for each location, which is the top part of the flow. 2 Multi Row formula tools fill in the missing key and office information, and a summarize tool then counts the workers. That's added on to our full supplies per office list with a join.

Next, a Tile tool gives an internal count by each Key (assuming each location has a unique key). So you can see that Dallas has 19 rows, Houston 10, and Austin 29. This number will be  used to generate the Employee type field.

A formula tool takes care of the Worker 1, 2, 3, etc. If the Tile Sequence number is greater than the total number of workers, then leave the Employee Type field empty. Otherwise, use Worker + the Tile Sequence number

Another formula tool removes all of the unwanted data in the Key, Office Nme, and Office Location fields.

Let me know if this helps!

Cheers,

Esther