0

I have a list of different names that I would like to duplicate each one by 3 copies of itself. For instance:

A
B
C
D
E

to the following:

A
A
A
B
B
B
C
C
C
D
D
D
E
E
E

How I would I accomplish this in excel? Can it be done in Excel?

user3456571
  • 181
  • 2
  • 7
  • 14

1 Answers1

4

There are SOOO many ways to do this... One way would be using something along the lines of the OFFSET() function like so:

Supposing your original list was in cells A1:A5, say. You could then put this formula where you want it:

=OFFSET($A$1,ROUNDDOWN((ROW(A1)-1)/5,0),0)

and drag it down for the 25 rows you want.

In essence, what you're saying is:

Offset cell A1 by ROUNDDOWN((ROW(A1)-1)/5,0) rows and 0 columns.

Looking at that ROUNDDOWN() function:

Row(A1) = 1   (Similarly, Row(A2) = 2, etc...
(Row(A1)-1) / 5 = 0/5 ; 1/5 ; 2/5 ; .....
Rounddowwn(...) means 0/5 to 4/5 becomes 0 ; 5/5 to 9/5 becomes 1 ; etc

Therefore, it will offset A1 by 0 rows and 0 columns 5 times then by 1 row and 0 columns for the next 5, etc.

Hope that makes sense :)

EDIT:

The original question asked for 3 copies of each value, not 5 - I'm leaving the answer as-is purposefully with this edit so someone else can see how to change it to any number of repetitions as wanted... All that would change would be:

 =OFFSET($A$1,ROUNDDOWN((ROW(A1)-1)/3,0),0)

Simply, divide by 3 rather than 5 for that to occur...

John Bustos
  • 19,036
  • 17
  • 89
  • 151