Incrementing Values

[cm123]

I need the first 18 values under column A (Range A2-A19) to equal cells E2 and F2.

Then I will need the next 18 values, starting at (Range A20-A37) to equal cells E3 and F3.

And continue for the remainder of the cells in column A.

Please see columns I thru N as a reference of what I am looking to have.

**Note** Under columns M and N, I need the values to show in groups of 18.

I am looking for a non-code solution.

Thanks!!!!

[JohnTopley]

Pictures are of little use: please see yellow banner at top of page on how to attach a sample workbook

[cm123]

Ah, thanks. I have uploaded an actual spreadsheet.

[JohnTopley]

in K2

=IF(MOD((ROWS($1:1)*1),19)=0,"",INDEX(C$2:C$1000,INT((ROWS($1:1)-1)/19+1)))

copy across to N and down

[cm123]

WOWWW! Thanks so much. This works great.

Can the formula be adjusted where it also copies columns A and B over to columns I and J?

The reason I ask is because the values in A and B will be expanded to include other values in the very near future.

Again! Thanks so much.

[JohnTopley]

Copy formula from K2 to I2 and drag across to N then down

[cm123]

WOWWWWW!!!!!

You are the best!!!!!!! THANKS SO MUCH!

[JohnTopley]

Minor typo in formula (nothing serious - extraneous data !)

in I2 and copy across and down

=IF(MOD(ROWS($1:1),19)=0,"",INDEX(A$2:A$1000,INT((ROWS($1:1)-1)/19+1)))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[cm123]

Thanks for the formula update.

One more point of clarification, please. I forgot to mention that we need column I and J to reflect the first 18 cells in column A and the corresponding value in column B. We would like these values to show over in columns I and J and then repeat for the next 18 cells for columns A and B until there are no more data in columns A and B.

Basically column I and J should match A and B.

Example:
Col A Col B Col I Col J
AA0001 X AA0001 X
AA0002 2 AA0002 2
AA0003 R AA0003 R
... ...
AA0018 Y AA0018 Y


Columns K, L, M, and N are perfect as per the formula. Thanks for that again!

[JohnTopley]

See post #6 which answered this from post #5

[cm123]

Yes, I tried that but it was copying cell A1 18x's in cells I2 thru I19.

[JohnTopley]

Try

in I2

=IF(MOD((ROWS($1:1)*1),19)=0,"",INDEX(A$2:A$1000,INT((ROWS($1:1)-1)/19)*18+MOD((ROWS($1:2)-1),19)))

drag to J and then down