Answered

Help to divide excel comluns into rows

(0) Share

Report

Posted on by capuman

Hi all, new to Power Automate, but from what i have seen so far, i think i can achieve what i need...i jut cant get my head around it. I hope one of you can help me.

So here is my use case:

I have an excel file, this file contains a number of columns that match to CV headings, i.e. Name, email, Job title 1, job desc 1, job title 2, job desc 2, etc (there is a finite amount of columns, which i know)
This excel contains one row per CV, so there could be 1 or many.
I'd like to be able to open the Excel and copy all the data into a new excel, however, if a row has data in more than one 'job title/job desc' then i need that data to be added as a new row into the new excel (see attached image as example, green table represents the origin and the yellow table represents the end result of what i need).

So ive figured out how to open the origin excel and get the data, and i see that there are for loops with conditions, so im assuming id have to add some condition per column, i.e. if column name = 'job title 1' then create new row or something like that so that the data can be divided into a new rows, but i just cant figure that part out.

Any help appreciated please.

2023-11-08 19_21_31-Job application-CM-3 (1).xlsx - Excel.png

Categories:

Power Automate Desktop

All responses (3)

Answers (1)

Sort by

Verified answer

Agnius Bartninkas Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
Since you said that the number of columns is finite, you can in fact just do conditions per specific column. What you will essentially need is to process each row individually, meaning you cannot just write the entire data table to the new file. You will need a loop for that. A For each loop works perfectly fine. This way you will end up with a single row at a time stored in %CurrentItem%.

Then you can access the values of each column by referencing the column name, such as %CurrentItem['job title 1']%. And then you essentially just need to use Write to Excel spreadsheet inside the For each loop as many times as the number of rows you want to create.

The way I see it, you want a single row for a name all the time. Then one more row for each job title and each education title. So, what you should do is more or less as follows:

Set variable - set a %RowIndex% to the first row you want to write to. Possibly 2, because row one will need to be dedicated for header data.

For each in %ExcelData%, store the current item into %CurrentItem%

Write to Excel worksheet - write the value %{[CurrentItem['name'],CurrentItem['tel'],CurrentItem['job title 1'],'']}% into column A (assuming your data starts at A) and row %RowIndex%

Increase variable - increment %RowIndex% by 1 for the next row to be written

If %CurrentItem['job title 2']% is not empty:

Write to Excel worksheet - write the value %{'','',CurrentItem['job title 2'],'']}% into column A (again, assuming your data starts at A) and row %RowIndex%

Increase variable - increment %RowIndex% by 1 for the next row to be written

End if

If %CurrentItem['education title 1']% is not empty:

Write to Excel worksheet - write the value %{'','','',CurrentItem['education title 1']]}% into column A (again, assuming your data starts at A) and row %RowIndex%

Increase variable - increment %RowIndex% by 1 for the next row to be written

End if

If %CurrentItem['education title 2']% is not empty:

Write to Excel worksheet - write the value %{'','','',CurrentItem['education title 2']]}% into column A (again, assuming your data starts at A) and row %RowIndex%

Increase variable - increment %RowIndex% by 1 for the next row to be written

End if

End loop

This will essentially write one row at a time with only the values you want, keeping the other cells empty.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
capuman 7 on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you! i actually worked it out last night and im happy to see my solution was pretty much what you explained 🙂
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Great work! Glad to hear you worked it out yourself 🙂