Excel - Copying one single row to multiple rows 2

[vlbridge]

We have a grocery order that comes from our boats and when we receive it, it's in an excel file. However, everything is spread out in one single row. So you'll have what looks like below:

Category Item Qty Price Notes Category Item Qty Price Notes Category Item Qty Price Notes...

I would like to change it to put each item in it's own row like below:

Category Item Qty Price Notes
Category Item Qty Price Notes
Category Item Qty Price Notes
...
...

The max amount of items in one order is 150, so the order could be spread out across 750 columns.

Any help would be greatly appreciated. Thanks!

 

[foxup]

Please provide 2 or lines of data as a reference. I'll take a look at it.

Thanks,
FOXUP

 

[vlbridge]

The actual data is irrelevant... this changes depending on the grocery order. Basically, the file is one row with 750 columns that I want to change to 150 rows with 5 columns. So, take the original file...

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1.... and so on for 750 columns...

I want to leave A1:E1 where it is, move F1:J1 to A2:E2, move K1:O1 to A3:E3.

Is that a better explanation?

 

[SkipVought]

hi,

I'd use VBA, as there is no good way using spreadsheet features, IMNSHO. Insert a Sheet2.

Dim iCol As Integer, lRow As Long, i As Integer

lRow = 2

With Sheets("Sheet1")
  For iCol = 1 To 750 Step 5
    For i = 0 To 4
      Sheets("Sheet2").Cells(lRow, i + 1).Value = .Cells(1, iCol + i).Value
    Next
    lRow = lRow + 1
  Next
End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vlbridge]

Ok, this may seem like a stupid question, but I know little to nothing about VBA. I right clicked on Sheet1 and clicked View Code and pasted the code there. Is this correct? If so, how do I run it?

 

[SkipVought]

Your code needs to be encapsulated in a Sub procedure...

sub GenerateTable()
Dim iCol As Integer, lRow As Long, i As Integer

lRow = 2

With Sheets("Sheet1")
  For iCol = 1 To 750 Step 5
    For i = 0 To 4
      Sheets("Sheet2").Cells(lRow, i + 1).Value = .Cells(1, iCol + i).Value
    Next
    lRow = lRow + 1
  Next
End With
end sub

Then RUN GenerateTable.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vlbridge]

I get an error that says Subscript Out of Range

 

[SkipVought]

on what statement?

Are you sure that you have a Sheet1 & Sheet2?

What version of Excel are you using?

Please answer BOTH questions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vlbridge]

Got it. I had a space in the name of Sheet 1. Sorry. Works great. Thanks so much.

 

[foxup]

Macro Option:

You can use Tools-Macro->Record New Macro->Click OK

records your keystrokes
Manually Select F1-J1
Copy/Paste it A2
Manually Select K1-O1
Copy/Paste it A3
etc..

do this for all your data (750 rows). When you are all finished, click on the "square" in the macro form to "Stop" the macro.

At that point you have created your macro and now, save your excel workbook. Now anytime you get a file from your supplier, use this workbook and run the macro. It will automatically do the work for you

It's an option.

Thanks,
FOXUP!

 

[foxup]

or you can do what Skip has.....LOL

Good one Skip.

 

[SkipVought]

BTW, foxup's suggestion of using the Macro Recorder is a great and very useful tool.

In general VBA (Macro) questions are best addressed in forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

Skip's suggestion is, as usual, clearly superb, but I couldn't resist the temptation of looking for a (worse!) vba-free solution.

If you are using Excel2007 or better:
First use the text-to-columns feature on the data tab to split the 750-item row into 750 columns. I assume you're doing this in cell A1.

I then placed in cell A3 the following formula:
=INDEX($1:$1, 1, (ROW(A3)-ROW($A$3)) * 5 + COLUMN(A3))
Now copy this rightwards and downwards to cover the target area 5 columns wide and 750 columns deep. It should redisplay the data from row 1 in the format you want.

Incidentally, it is the gross untidiness of doing this sort of transformation of data that makes me cringe whenever someone posts a question here wanting to do the reverse: take a nice set of data on separate rows and combine to one row!

 

[vlbridge]

Cool. That works too! For the purposes of what I'm going to be doing with it, the VBA solution works the best. However, it's nice to learn a new formula! Thanks!