Copy EXCEL cell x number of times

Question

I have a column of about 1000 IP addresses that I need to print 5 times in a single column.

For example

10.10.10.1
10.10.10.2
10.10.10.3
10.10.10.4

should become

10.10.10.1
10.10.10.1
10.10.10.1
10.10.10.1
10.10.10.1
10.10.10.2
10.10.10.2
10.10.10.2
10.10.10.2
10.10.10.2

etc, etc

Can this be done in Excel? What functions should I research?

score 14 · Accepted Answer · answered Dec 16 '13 at 19:43

14

If your example is in range A1:A4, you can use this in column B as from cell B1:

=INDEX(A:A,INT((ROW()-1)/5)+1)

This will repeat each one 5 times.

answered Dec 16 '13 at 19:43

Jerry

After entering in the forumla I have to drag it to other cells in the column ..... how to make the other columns populate automatically? – Glowie Dec 16 '13 at 19:54
@Glowie You'd have to turn to coding if you want to do something like autopopulating other cells with a formula in one cell... (unless it's google spreadsheets). If you're willing to learn excel-VBA and macros, that's your bet, though I'm not the best person to teach you that. – Jerry Dec 16 '13 at 20:16
Ok, I'll research excel-VBA and macros – Glowie Dec 16 '13 at 20:26

1 Answers1