Making an known amount of replicates in autofill

[Reas]

Hello forum,

Since this forum usually have some very helpfull people, I will try explaining another question that comes to mind.

In one of my worksheets I want to import a list from another worksheet, though in the worksheet I want imported, the list goes like this:


33
35
38
69 etc..

So in my worksheet I import it, ex: ='Sample I.D'!B7 and autofill down so it goes ='Sample I.D'!B8 and so on.


This is fine, but I want each number to be represented 4 times, like:

33
33
33
33

35
35
35
35

38
38
38
38 etc

Is there any formula I could write to make it do quadruple instead of of single? and even better, is there a way to make it do a line between the 4 autofills? This would be so helpful in the furture!

Thanks!

[StephenR]

Probably an easier way, but if your data are in A1:A4, try this in B1 and copied down

=IF(MOD(ROW(),5)=0,"",OFFSET($A$1,CEILING(ROW()-CEILING(ROW(),4)/4+1,4)/4-1,0,1,1))

[Reas]

I keep getting an error Could it be a regional setting?

[StephenR]

Post a workbook.