Using iterative macro to transfer a row to specific fields

[mbajic]

Hello,

I have a problem. I have a table which consists of multiple rows, for example:

Table 1

Name	Year	Date	Activity	Hours
John Adams	2022	1/1	work	50
Adam Smith	2022	2/1	project	70

The final table is not of the same format, for example:

Table 2

			1/1	2022
	John Adams		work
					50

Now I need to use the first row data (for John Adams) and transfer it to another sheet/excel file (Table 2) Table 2 is always of the same format.. But then I need to to the same for Adam Smith. Since new people will be regularly put to that table is there a way to make the process repeat itself by creating a loop with each new row until there are no more names. I heard that Iterative Macro is a great tool. Also it is very important to put each set of rows in separate worksheets or excel files.

I hope I described the problem well. Thank you to anyone who helps in one way or the other.

Best regards,

Matej

[OllieClarke]

Hi @mbajic 

How's this for an approach:

which outputs this:

We can then use the RecordID field in the output data tool to output each person into their own sheet in the excel

Hope that helps :)

Ollie

[mbajic]

I have been trying using your workflow and I cannot get it right, but it is exactly what i need. When I use my real input everything just connects to single row.

This is exactly how my first tables looks like.That happens in cross tab tool

Name	Hours	Activity	Date 1	Date 2	Month	Year	File_Name
John Adams	50	project	1/1/2022	2/2/2022	Dec	2022	AdamsHours

Can you explain how you used the second formula and cross tab tool, just so I can get similar result as you. And also how to know precisely to which cell the data goes:

John Adams goes to C4

50 goes to B11

Project goes to A10 

1/1/2022 goes to B26

2/2/2022 value should go to E29 cell.

Dec goes to B7

2022 goes to C7

AdamsHours goes to F1

Thank you for your first response, I hope you will help me with this question as well and sorry for the long question.

Kind regards,

Matej