Forum Discussion

Copper Contributor

Dec 30, 2019

Solved

Excel Help: Showing and repeating data from one sheet to another

HI, I have been trying to find a smart way to take a list of numbers and put them together with a storenumber., I have a list with maybe 8 numbers, that should show up again and again until all s...

excel

SergeiBaklan
Dec 30, 2019
Annette_W

If in first table you add total as count of elements

in resulting table it could be

=INDEX(Table1[No],MOD(ROW()-ROW(Table3[[#Headers],[Butik]])-1,Table1[[#Totals],[No]])+1)

in E1, and

=INDEX(Table2[Butik],INT((ROW()-ROW(Table3[[#Headers],[Butik]])-1)/Table1[[#Totals],[No]])+1)

in F1, drag both down.
Repeat columns.xlsx22 KB

SergeiBaklan

Diamond Contributor

Dec 30, 2019

Annette_W

If in first table you add total as count of elements

in resulting table it could be

=INDEX(Table1[No],MOD(ROW()-ROW(Table3[[#Headers],[Butik]])-1,Table1[[#Totals],[No]])+1)

in E1, and

=INDEX(Table2[Butik],INT((ROW()-ROW(Table3[[#Headers],[Butik]])-1)/Table1[[#Totals],[No]])+1)

in F1, drag both down.

Repeat columns.xlsx22 KB

Annette_W

Copper Contributor

Dec 30, 2019

Thank you verry much. I do have some difficulties changes it to danish.

Is there a chance you could explain the Row part.
My brain works best with a lot of info 🙂

SergeiBaklan
Diamond Contributor
Dec 30, 2019
Annette_W

If you open the file I attached to previous post formulas will be in your locale, no need to translate. But, juts in case, you always use this tool https://en.excel-translator.de/translator/

In both columns of the resulting table first we calculate sequential number of the row with the table as difference between the current row in the sheet and row number of the header

ROW()-ROW(Table3[[#Headers],[Butik]])

In first column MOD(#-1,8)+1 repeats row numbers of first table, in second column INT((#-1)/8+1 repeats each row number of the second table 8 times (or your actual number instead of 8). Wrap both by INDEX and we have values from first and second tables.
- Annette_W
  Copper Contributor
  Dec 30, 2019
  I finally understand the setup. sorry.
  And it works perfectly. Thank you. 🙂
  - SergeiBaklan
    Diamond Contributor
    Dec 30, 2019
    Annette_W , you are welcome