Reorganising Data

[Guest]

Hi

I have a spreadsheet with information about each product in a single row.

The product number is in Col A and the name is in Col B, then there are sets
of 4 columns with additional information. Most only have 1 or 2 sets of
additional information but a couple have 7 or 8.

I would like to reorganise the data so it is, and can be used, more like a
database.

As there is a lot of data this needs to be automated but I'm afraid it's too
complicated for me and I'm stuck.

What I'm trying to do is:
For each product number in Col A insert a number of rows - this can either
be 7 and remove blank rows later or it can be based on a formula where if
there is data in Col AE it inserts 7 rows, Col AA 6 rows and so on - and
fill down with the info in Cols A & B.

Then cut information from Cols G:J and put that in the second row for that
product, then K:N and put that in the third row and so on, then move down to
the next product.

I should then finish up with a sheet with no data after Col F.

I hope I've described this well enough for someone to help me make this work
otherwise I'm in for a mammoth cut and pastathon doing it manually.

Many thanks in anticipation

Kewa

 

[StumpedAgain]

Before I try to write something that will help with your problem, I want to
make sure I understand what you're trying to accomplish by knowing what you
have currently, and what you want. I couldn't quite figure this out based on
your first post. Perhaps someone else can... Tell me how close I am and
correct me on what I didn't get right.

What you have:
A database that has one product per row with information on the product
stretching out for a number of columns within the same row. Additional
information is not necessairly connected (directly ajacent) to the product
information.

What you want:
A database that lists products in a single row with information on that
product ajacent to that product in the columns to the right.

Close? Not close? I need a better idea of the situation in my head.

 

[Guest]

Thanks StumpedAgain and sorry for the lack of clarity.

At present I have 1 row for each product code but information for that code
(which consists of groups of 4 cells) is immediately to the right of the
product code and name so where there is 8 sets of info this spans 32 columns
after the Proct Code and Name.

What I am aiming for is the Product Code and Name to repeat down for each
set of information so I finish with a list of multiple instances of each
product code and name with 1 set of info alongside so instead of an array of
1 row 32 columns the data would span 8 rows 4 columns plus the Product Code
and Name of course.

I hope this is clearer

Kewa

 

[StumpedAgain]

If I understand what you're after, the following should do the trick. It
worked on a test set of what I think you're looking at. Might want to save a
backup first.

Note: I start at "A2" and if you start some place else, you need to change
all "A2" references. (mind wrapping)

Option Explicit
Sub Save_Time()

Dim glcount, i, j As Integer
Dim currentrow As Integer

With ActiveSheet.Range("A2") 'or wherever you start
glcount = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

j = 1

For i = 0 To glcount - 1
j = j - 1
currentrow = Range("A2").Offset(i + j, 0).Row
Rows(currentrow).Offset(1, 0).Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
j = j + 1
Range(Cells(currentrow, "G"), Cells(currentrow, "J")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1
Range(Cells(currentrow, "K"), Cells(currentrow, "N")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1
Range(Cells(currentrow, "O"), Cells(currentrow, "R")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1
Range(Cells(currentrow, "S"), Cells(currentrow, "V")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1
Range(Cells(currentrow, "W"), Cells(currentrow, "Z")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1
Range(Cells(currentrow, "AA"), Cells(currentrow, "AD")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1
Range(Cells(currentrow, "AE"), Cells(currentrow, "AH")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1
Range(Cells(currentrow, "AI"), Cells(currentrow, "AL")).Cut
Destination:=Range("A2").Offset(i + j, 2)
Range(Cells(currentrow, "A"), Cells(currentrow, "B")).Copy
Destination:=Range("A2").Offset(i + j, 0)
j = j + 1

Next i

End Sub