Shopwise allotment [Solved]

[safiyya]

Sir

I have excel file sheet1 has data s range B2:M2 rows 1002,total 1001 shops

and sheet2 i make model sheet1 B2 for one shop,and i need apply balance 1000 shops

I need every row sheet1 B2 to B1002 one page(1001 pages) in sheet2

please here is my file link

https://www.dropbox.com/s/jmp5wngpv1e3wcx/SHOPWISE%20ALLOTMENT.xls

 

[NARAYANK991]

Hi Mohammad ,


I have downloaded your file , and can upload the modified file later today.


Narayan

 

[safiyya]

Sir

Thankyou

 

[SirJB7]

Hi, safiyya!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Shopwise%20allotment%20%28for%20safiyya%20at%20chandoo.org%29.xlsx


It keeps rows 1:2 fixed and then replicates a 25-row group for each shop. In the sample file there are formulas for the 1st 10 groups (up to row 252), copy down the group as required (if math doesn't lie up to row 25027).


Formulas are:

B6: =INDICE(Sheet1!B:B;ENTERO((FILA()-2+24)/25)+1) -----> in english: =INDEX(Sheet1!B:B,INT((ROW()-2+24)/25)+1)

B7: =INDICE(Sheet1!C:C;ENTERO((FILA()-2+24)/25)+1) -----> in english: =INDEX(Sheet1!C:C,INT((ROW()-2+24)/25)+1)

C12:G12: =INDICE(Sheet1!$1:$1;1;COLUMNA()+1)&CARACTER(10)&"(kgs)" -----> in english: =INDEX(Sheet1!$1:$1,1,COLUMN()+1)&CHAR(10)&"(kgs)"

C13:G13: =INDICE(Sheet1!$D:$H;ENTERO((FILA()-2+24)/25)+1;COLUMNA()-2) -----> in english: =INDEX(Sheet1!$D:$H,INT((ROW()-2+24)/25)+1,COLUMN()-2)

C17:G17: =INDICE(Sheet1!$1:$1;1;COLUMNA()+6)&CARACTER(10)&"(kgs)" -----> in english: =INDEX(Sheet1!$1:$1,1,COLUMN()+6)&CHAR(10)&"(kgs)"

C18:G18: =INDICE(Sheet1!$I:$M;ENTERO((FILA()-2+24)/25)+1;COLUMNA()-2) -----> in english: =INDEX(Sheet1!$I:$M,INT((ROW()-2+24)/25)+1,COLUMN()-2)


Just advise if any issue.


Regards!


PS: BTW would you please stop posting questions on topics that are absolutely not related to them? As b(ut)ob(ut)hc wrote you on another topic start a new one for each new question and keep all questions about it in the same thread. Thank you.

 

[safiyya]

Hi

SirJB7


Thankyou