Paste series

[davehat]

I'm struggling with something that may either be really simple or horrendously complicated...

I want to be able to use fill series, but instead of filling a range, I fill a selection.

For example, in cell a10 of a sheet, I have the value:

1.1.1.1

Using the fill handle, I can drag a selection down the corner of the cell and fill a series that increments so I get:

a10 = 1.1.1.1
a11 = 1.1.1.2
a12 = 1.1.1.3
a = 1.1.1.(m)
a(n+1) = 1.1.1.(m+1)

What I want to do is to fill, for example, every 20th cell in a column so that:

a10 = 1.1.1.1
a30 = 1.1.1.2
a50 = 1.1.1.3
a = 1.1.1.(m)
a(n+20) = 1.1.1.(m+1)

Is it possible (in xl2000) to copy a value in a cell and then paste an incremental series over a selection of individual cells?

 

[Weaver]

Try this

="1.1.1."&INT((ROW()-10)/20)+1

in cell A10 and drag as far as you need it.

If you like, you can copy than paste special -> values once you're done.

HTH

 

[davehat]

Thanks, that's a pretty elegent little solution and works for the specific example I gave if you paste the formula into every 20th cell.

The thing is, it might not always be every 20th cell in a column, and the start cell might not always contain 1.1.1.1

The start cell could be a50 containing 1.1.1.2.1.1 and the repeat is every 14th cell with:

a50 = 1.1.1.2.1.1
a64 = 1.1.1.2.1.2
a78 = 1.1.1.2.1.3
a(m) = 1.1.1.2.1.
a(m+14) = 1.1.1.2.1.(n+1)

Excel can clearly recognise and fill an incremental range of this type - the fill handle does the increment above correctly - but I can't work out how to do this for a selection of cells.

 

[Weaver]

okay, deep breath...

in cell B1, put your start row
in cell B2, put your row increment
in cell B3, put the stub of the string to be incremented

in column A, insert the following formula

=REPT($B$3&INT((ROW()-$B$1)/$B$2)+1,(MOD(ROW()-$B$1,$B$2)=0)*ROW()>=$B$1)

HTH

 

[davehat]

Thanks Weaver, the formula works (with a minor modification):

=REPT($B$3&"."&INT((ROW()-$B$1)/$B$2)+1,(MOD(ROW()-$B$1,$B$2)=0)*ROW()>=$B$1)

The problem is that I will often need different repetitions down the same column.

Some background - the sheet concerned is a template for entering a series of nested logical rules and clauses. The rule nesting is defined by a number sequence in a sincle column to the left. There's no easy way to derive the numbering from the clauses, so this is typed in afterwards.

The excel sheet is then saved in a text file and uploaded into another system

In this system, the rules in the column are parsed from the top row to the bottom, sequential until a rule is found. The rules are numbered like so:

1
1.1.1
1.1.1.1

but at various levels there can be different sub-rules so I might have:

1.1.1
1.1.1.1
1.1.1.2
1.1.1.3
1.1.1.n
1.1.2
1.1.2.1
1.1.2.2
1.1.2.3
1.1.2.n
1.1.3
1.1.3.1
1.1.3.n

At the moment, after completing the template and creating all the rules, the sheet user types in the relationship manually. The only way to speed this up right now is to type the coloured items in the example above (eg 1.1.1 and 1.1.2) and the first line underneath it, then use the fill handle to save typing the intervening series.

What I'd like to do is to allow them to save the typing time, hence the query about pasting a series (ie, copy the green item and paste a series to create the red items)...

What I think I'll do is play around with your suggestions and see if I can come up with some VBA to do this.

Thanks for getting me started.

 

[Weaver]

I'd got the "." in the string in B3!

I can see your problem now and I think you're right in that VB might be the best way to go about it.

 

[davehat]

Ah, of course - that works too.

As always, there was nothing wrong with the formula, the problem was the end user

Thanks again.