SOLVED

Formula for copying down formula in specific order

%3CLINGO-SUB%20id%3D%22lingo-sub-2992779%22%20slang%3D%22en-US%22%3EFormula%20for%20copying%20down%20formula%20in%20specific%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2992779%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20a%20table%20of%20252%20unique%20combinations%20these%20have%20been%20times%20by%2053%20to%20get%2013%2C356%20rows.%3C%2FP%3E%3CP%3EIn%20Column%20A%20i%20am%20trying%20to%20add%20the%20week%20no%20for%20each%20unique%20combination%20so%20for%20example%20the%20first%20252%20unique%20combinations%20in%20row%20C%2C%20D%20and%20E%20will%20have%20no%201%20in%20column%20A%20then%20the%20next%20252%20rows%20will%20have%20no%202%20in%20column%20A%20and%20so%20on%20all%20the%20way%20up%20to%2053.%20I%20have%20attached%20the%20Workbook%3C%2FP%3E%3CP%3EIs%20there%20a%20formula%20that%20can%20do%20this%20for%20me%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2992779%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2992843%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20copying%20down%20formula%20in%20specific%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2992843%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1163132%22%20target%3D%22_blank%22%3E%40excel_learner%3C%2FA%3E%26nbsp%3BSimilar%20to%20this%2C%20I%20guess%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fcopying-down-rows%2Fm-p%2F2959533%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fcopying-down-rows%2Fm-p%2F2959533%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2992924%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20copying%20down%20formula%20in%20specific%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2992924%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BHi%3C%2FP%3E%3CP%3EThe%20issue%20I'm%20having%20with%20how%20the%20budget%20forecast%20and%20actuals%20are%20laid%20out%20in%20power%20query%20I%20want%20to%20have%20it%20laid%20it%20out%20as%20%22%20budge%2C%20forecast%2C%20Actuals%22%3C%2FP%3E%3CP%3Elike%20this%20below%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel_learner_1-1637588473542.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328541iDEAE7E715B5A944C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22excel_learner_1-1637588473542.png%22%20alt%3D%22excel_learner_1-1637588473542.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ebut%20when%20I%20sort%20the%20customer%20column%20(week%20no)%20it%20becomes%20like%20this%20the%20BFA%20column%20is%20not%20in%20the%20correct%20order.%20is%20there%20a%20way%20to%20get%20in%20the%20correct%20order%20of%20Budget%20then%20forecast%20then%20actuals%20whilst%20having%20the%20week%20no%20sorted%20ascending.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel_learner_4-1637588737116.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328544i60719A08C1F47550%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22excel_learner_4-1637588737116.png%22%20alt%3D%22excel_learner_4-1637588737116.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2992994%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20copying%20down%20formula%20in%20specific%20order%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2992994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1163132%22%20target%3D%22_blank%22%3E%40excel_learner%3C%2FA%3E%26nbsp%3BInclude%20the%20Index%20column%20in%20your%20sort.%20First%20by%20Week%20no%26nbsp%3B%20then%20by%20Index.%20But%2C%20perhaps%2C%20difficult%20to%20judge%20from%20screenshots%20alone.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi

I have a table of 252 unique combinations these have been times by 53 to get 13,356 rows.

In Column A i am trying to add the week no for each unique combination so for example the first 252 unique combinations in row C, D and E will have no 1 in column A then the next 252 rows will have no 2 in column A and so on all the way up to 53. I have attached the Workbook

Is there a formula that can do this for me ?

 

3 Replies

@Riny_van_Eekelen Hi

The issue I'm having with how the budget forecast and actuals are laid out in power query I want to have it laid it out as " budge, forecast, Actuals"

like this below

excel_learner_1-1637588473542.png

but when I sort the customer column (week no) it becomes like this the BFA column is not in the correct order. is there a way to get in the correct order of Budget then forecast then actuals whilst having the week no sorted ascending.

 

excel_learner_4-1637588737116.png

 

best response confirmed by excel_learner (Contributor)
Solution

@excel_learner Include the Index column in your sort. First by Week no  then by Index. But, perhaps, difficult to judge from screenshots alone.