-2

Basically, I want a way to take a column of values, numerical or strings, in Excel, like this:
Val1
BigVal2
SmallVal3
MediumVal6

And turn it into this:
Val1
Val1
Val1
Val1
BigVal2
BigVal2
BigVal2
BigVal2
SmallVal3
SmallVal3
SmallVal3
SmallVal3
MediumVal6
MediumVal6
MediumVal6
MediumVal6

Now, obviously I can just ctrl c, drag mouse over 4 cells, and ctrl v. But the actual list that I am wanting to use is like 1000 values long. So if there is some way that I could convert one format to the other lickety-split that would be great.

Improve this question
2
  • 1
    What version of excel are you using? Newer versions of excel have formulas that can do this? VBA can also do this. Regardless, what have you tried so far? Generally a question that describes what you have done and where you are getting stuck will result in higher quality responses.
    – gns100
    Commented Jun 10 at 15:16
  • Are you asking to repeat a column, or a row, as you show in the question? In either case, how many times?
    – DrMoishe Pippik
    Commented Jun 10 at 17:27

2 Answers 2

Reset to default
2

Using Office 365 you could approach it with TOCOL:

=TOCOL(IF({1,2,3,4},A1:A4))

Or

=TOCOL(IF(SEQUENCE(,4),A1:A4))

enter image description here

The horizontal array of integers ({1,1,1,1} would work just as well) converts to TRUE inside IF and since it's horizontal, unlike the range, it copies the row's value along to the side; same for next row's value, etc.

If this array is wrapped in TOCOL it gets flattened, so the repeated values are underneath eachother.

And for older Excel versions this is a way: =IFERROR(INDEX($A$1:$A$4,ROUNDUP(ROW(1:1)/4,)),"")

(Needs dragging down)

You may even want to add a blank line in between different values. Using the same kind of logic as the Office 365 solution you can accomplish this using:

=DROP(TOCOL(IF({1,1,1,1,0},A1:A4,"")),-1)

Or dynamically:

=LET(n,4,DROP(TOCOL(IF(SEQUENCE(,n+1,n,-1),A1:A4,"")),-1))

Improve this answer
-1

If it's a one time thing:

  1. make a helper column, numbering each item 1,2,3... and so on

  2. Copy both columns

enter image description here

  1. Paste them four times, one below the other

enter image description here

  1. Sort on the numbers

enter image description here

  1. Delete the helper columns

If this is a repeating task you may want to consider automating this with a macro

Improve this answer
2
  • The OP clearly states, "Now, obviously I can just ctrl c, drag mouse over 4 cells, and ctrl v. But the actual list that I am wanting to use is like 1000 values long..."
    – DrMoishe Pippik
    Commented Jun 10 at 17:26
  • And? The solution I put forward uses the same number of steps whether you have 4 or 4000 rows. And does not depend on having the latest version of Excel
    – cybernetic.nomad
    Commented Jun 11 at 14:01

Not the answer you're looking for? Browse other questions tagged .