Forum Discussion
copying down rows
A bit of background information. I have a Table of 21,200 Rows.The table contains 400 unique combinations. The reason i have 21,200 of rows is because I needed this combination repeated 53 times.
on the attached table column A I want to add the week so each combination will have week 1,2,3 all the way up 53 weeks.
Is there a way to do this in excel which I can add week to each row and then the following week starts once all combinations have been covered for previous week. it would be like 1 X 400 rows down then 2 X 400 rows down then 3 X 400 rows down etc all the way up to 53
Any formula or technique there could be to tackle this.
8 Replies
excel_learner Since you have already been working in PowerQuery, why not finish the job there? I couldn't work with your queries, though, and deleted them. But I created a table with the 400 combinations. Added a column with a list of numbers { 1 .. 53 } to each row, expand, sort and clean-up.
See attached.
- Hi Thank you for your answer,
Could you explain how you managed to get the numbers 1 to 53, how you made each unique combination have a separate number?excel_learner Well, the answer is actually included in the file. You can look at each of the applied steps.
First add an Index column that numbers each row from 0 to 399. Then add a custom column (I called it WeekNum) with a formula = {1 .. 53 }. Now you can expand the WeekNum column to new rows. This will create 53 rows for each of the 400 original rows.
Now sort the table. First by WeekNum, then by Index. Remove collumns that are no longer needed and reorder the remaining columns to your liking.
- Thanks mate this formula helped alot
