copying down rows

Brass Contributor

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 

You could enter the formula

=QUOTIENT(ROW()-1,53)+1

in A1, then fill down.

@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.

@Riny_van_Eekelen Apologies I'm new to Power Query please excuse me, This is the steps I see when I open Advanced Editor, I cant see the steps you mentioned in the query. Did you apply those steps before going into Power Query?
let
Source = Excel.CurrentWorkbook(){[Name="tblALB_2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"WeekNum", Int64.Type}, {"Area", type text}, {"LDP", type text}, {"Banding", type text}})
in
#"Changed Type"

@excel_learner No apologies needed. PQ isn't difficult but it takes some time to get used to.

The "Applied steps" are also on the right-hand side of the screen.

Screenshot 2021-11-15 at 14.37.48.png

Everything is done by clicking on the correct buttons, except for the "Added Custom" step. Perhaps wise to start get familiar with PQ with the following material:

https://exceloffthegrid.com/power-query-introduction/ 

Thanks mate this formula helped alot
Thank you mate, this really helped me.