Looping in Excel

[jokano1 (Jason Okano)]

I am trying to create a Workflow that will take duplicate material numbers for each user from a master user list in excel. Below I have attached a screen shot of what it would need to look like. Essentially, I will have a list of material numbers say Materials: “1,2,3” in a list and I have a master list of users in another excel doc with Users: “User 1 and User 2.” I want to copy all the material numbers from the list and place a user next to each one. All the material numbers will need to be duplicated per user as seen below.

I was thinking I could use a For each loop which will allow me to run it for each user, but i am certain how I will duplicate the material numbers for each user. Maybe I would read all the material numbers into an array/list and put the user number for each material number and move down rows?

Any information and suggestions would be greatly appreciated. I am not super fluent in UIpath yet, so anything would help.

[AndyMenon]

Hello @jokano1 - welcome to the forums.

You can refer to this thread to pull unique values from a Data Table and see if it helps?

Basically, you start by pulling the list of distinct Material column values nto a List or Array of some kind - let’s say ListMaterials

DataTable get all unique values from a column

it’s working

Hope this helps.

Question though: How do you know which User to assign against a material ID?

[jokano1 (Jason Okano)]

Hi @AndyMenon - Thank you for the information. I will try it out and see how it goes.

In regards to your question, all users will need to be assigned all of the materials. Basically I was copying all the material numbers at once and pasting below the previous material numbers for the amount of users in the list. then Copying the users for each block of material numbers so that all users will have all the materials next to them.

[NervYerem]

I’d handle this by doing the following:

1. Read range into a data table, for each Users (ref as UsersDataTable) and Material (ref as MaterialsDataTable)
2. Build Data Table to merge into, With columns User and Material, ref as MergedDataTable
3. Nested “for each row” actions:

• For each User in UsersDataTable, For each Material in MaterialsDataTable
• then “Add Data Row” in your material foreach loop
• Properties for the action:
  ArrayRow- {User(“User”).ToString, Material(“Material”).ToString}
  DataTable- MergedDataTable

Then you can output MergedDataTable into excel.

Edit, My testing of this method worked: