1

I have a list of 300 items in an Excel XLS. One item per row.

I would like to have 5 items for each row. So, for example, my current system is :

Item 1
Item 2
Item 3

I would like to make it 

Item 1
Item 1 
Item 1
Item 1
Item 1
Item 2
Item 2
Item 2
Item 2
Item 2
Item 3
Item 3
Item 3
Item 3
Item 3

Is there any trick for doing this easily? At the moment, I am right clicking on each row 5 times to add a new lines, then copy pasting the item into each row....it's quite tedious.

I would be able to do it with some code, but it's the kind of thing I'm interested in doing on the fly in different spreadsheets within Excel - maybe just for one item here and there....so I'm just looking to be able to do it with the minimum amount of clicks really.

Any advice is greatly received!

Thanks.

Improve this question

4 Answers 4

Reset to default
2

Just copy the items and paste them on the lines under your data. Then sort your data. The items with the same name will be grouped together.

Just make sure to count correctly the number of rows when pasting, to get exactly 5 copies of each item (if you have n items, paste them in 4n rows).

Improve this answer
1
  • 1
    This would be the simplest way. Extention: to record a macro (select all, insert x4) and just play it on all sites.
    – Leo Chapiro
    Commented Feb 16, 2017 at 13:40
0

Create your new items below your existing items, as

Item 1  
Item 2  
Item 3  
Item 1  
Item 1  
Item 1  
Item 1  
Item 2  
Item 2  
Item 2  
Item 2

...etc

Then, simply sort your data! All the Item 1 rows will be grouped together, then item 2, item 3, etc.

You can create your new items in whatever way you like - you can copy your existing list and paste it in however many times you need, or you can manually enter however many you need of each item...how you create the list really doesn't matter, it's the sort at the end that gets all the data organised.

If you have multiple fields (columns) that need to stay together, make sure your sort includes those rows as well.

Improve this answer
4
  • 1
    Is this solution not exactly the same as the @Thales before? He means: "Just copy the items and paste them on the lines under your data". What is the difference to your "solution"?
    – Leo Chapiro
    Commented Feb 16, 2017 at 14:02
  • Apparently @Thales and I were both writing our solutions at the same time - he just posted before me. And I specified sorting the data, which the other answer did not.
    – Werrf
    Commented Feb 16, 2017 at 14:12
  • 1
    The other answer speaks about "classifing data" what means " The items with the same name will be grouped together" - this is sorting by name. And he answered a half an hour before your answer.
    – Leo Chapiro
    Commented Feb 16, 2017 at 14:15
  • If he meant "sorting", he should probably have said "sorting", not "classifying". After I posted my answer I went back and tried to read that answer and couldn't make any sense of what he was saying. And I'm doing other things while trying to answer, so my answers are sometimes a bit slow. What's your point?
    – Werrf
    Commented Feb 16, 2017 at 14:18
-1

To get the entries in each cell I used Word Wrap as shown in the image. It still requires a little editing after but this may be what you wanted. My attempt to duplicate your issue

Improve this answer
1
  • I don't think he would need them in the same cell. Also the question is not how to set up the layout, but how to do it efficiently for 300 items.
    – Máté Juhász
    Commented Feb 17, 2017 at 8:19
-2

Type Item 1, Item 2,Item 3 in 3 cells. After that select these cells and drag them upto required times. After that sort them.

Improve this answer

You must log in to answer this question.

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