Forum Discussion
Duplicating unique data in 12 rows of 3 columns
Hello:
I am seeking help to duplicate/multiply unique data in 12 rows of 3 columns as seen in my test example. The result should be that the 12 rows of data in all 3 columns will be duplicated x 10 so that there are now 120 rows of the exact same data in the exact same order in 3 columns.
For my real life scenario, I need to multiply this same data, described above, 1,800 times for import sheets.
Thanks, in advance, for your time on this important matter.
Peace,
John
5 Replies
trinitystores Attached a demonstration of a Power Query solution.
- Connect to the 12 by 3 range that needs to be repeated
- Add and Index column
- Add a custom column with the following "formula" = {1 .. 10}
- Expand the custom column
- Sort, first by custom then by index
- Remove columns custom and index
- Load to a table in Excel
This will have copied the initial range 10 times. Change the 10 (above in red) to 1800 in your real life scenario.
Now you can copy the table produced with PQ and paste is as values wherever you need it.
Riny_van_Eekelen Riny, thank you very much for your prompt reply.
The excel spreadsheet example you attached looks perfect, exactly what I am trying to accomplish.
But I think you are saying that you attached a "demonstration of a Power Query solution." I do not see this attachment.
And I am not sure what your lines describing the process mean. I do not know what "CONNECT" to the 12 x 3 range means. I do not know what Add and "INDEX" column means, etc.
Is there an attachment "demonstration of a Power Query solution" that I am not finding? Or is there a video or other tutorial on the Internet that you can point me to?
Thanks again for your help. I am excited to see your sample excel spreadsheet example.
Peace,
John
trinitystores Forgive me! I sometimes forget that PQ is perhaps the greatest secret of Excel. After 8-9 years it's still unknown to the majority of Excel users. The link below is a good starting point to start learning about PQ. It will enable you to find the steps that I described in my previous post.
