Adding Rows to Data without losing existing data

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!

Alteryx Example.xlsx

Datasets

Tips and Tricks

Common Use Cases

Accepted answers

benakesh

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.

mfindlen.yxmd

All comments

JosephSerpis

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

Adding_Rows.yxzp

benakesh

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.

mfindlen.yxmd

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

SupplyList.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703