Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reorganising Data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reorganising Data
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. "nospaminlich" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reorganising Data
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" wrote: 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. "nospaminlich" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reorganising Data
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 "nospaminlich" wrote: 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" wrote: 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. "nospaminlich" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent - Reorganising Data | Excel Discussion (Misc queries) | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
reorganising data | Excel Worksheet Functions | |||
Reorganising Data | Excel Discussion (Misc queries) | |||
Reorganising data - macro needed? | Excel Programming |