Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
I currently receive a file that has a persons ID # and then a list of work
skill #s they have been assigned. I need to have that data modified. EX: the data comes to me in this format 12345 100 101 110 52 12346 99 191 45 17 12347 150 101 52 99 I need the data to come out in this format: 12345 100 12345 101 12345 110 12345 52 12346 99 12346 191 12346 45 12346 17 12347 150 Etc. I have dozens of lines long and when converted will be hundreds of lines long. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
I'm going to assume that you have somehow gotten the initial data into an
Excel workbook, onto one sheet. The code below will read the data and lay it out as you've asked on another sheet that is presumed to be empty when you start (if it isn't, older data may be overwritten). The code doesn't care how many IDs are in the list, nor how many job codes are associated with any of them - the number of job codes can even change from row to row. There just cannot be any blank rows between the start of the ID list and its end, and for any given row, there cannot be any empty columns on a row. An empty cell causes that part of the code to stop and move on to the next part of the process. You can change the values of the assignments to the "Const ..." objects in the code to fit your workbook setup and the code should run properly. Copy the code below and put it into a module in the workbook and use Tools -- Macro -- Macros to [Run] the macro when you need to. To get the code into the workbook, open the workbook, press [Alt]+[F11] to open the VB Editor. In it, choose Insert -- Module. Copy the code below and paste it into the module presented to you. Should be good to go at that point once you've edited it to get the right worksheet names in it and the proper address references for the start of the initial data: Sub TransposeData() 'change these definitions as needed ' 'this is the name of the sheet with your 'original data on it as laid out in your example Const sourceSheetName = "Sheet1" 'this is the column ID of the first cell with the 'first ID code in it Const sourceIDColumn = "A" 'this is the row number with the first cell with 'the first ID code in it Const sourceIDRow = 2 ' assumes label in row 1 'this is the name of the BLANK sheet that 'we will move the data to Const destSheetName = "Sheet2" ' 'these are working values, you don't need 'to concern yourself with them Dim sourceSheet As Worksheet Dim destSheet As Worksheet Dim sourceIDList As Range Dim anySourceID As Range Dim sourceColOffset As Integer Dim baseCell As Range Dim destRowOffset As Long Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName) Set sourceIDList = sourceSheet.Range(sourceIDColumn & _ sourceIDRow & ":" & _ sourceSheet.Range(sourceIDColumn & Rows.Count).End(xlUp).Address) Set destSheet = ThisWorkbook.Worksheets(destSheetName) Set baseCell = destSheet.Range("A2") ' leave room for label Application.ScreenUpdating = False For Each anySourceID In sourceIDList sourceColOffset = 1 Do While Not IsEmpty(anySourceID.Offset(0, sourceColOffset)) baseCell.Offset(destRowOffset, 0) = anySourceID baseCell.Offset(destRowOffset, 1) = anySourceID.Offset(0, sourceColOffset) destRowOffset = destRowOffset + 1 sourceColOffset = sourceColOffset + 1 Loop Next Set sourceIDList = Nothing Set baseCell = Nothing Set sourceSheet = Nothing Set destSheet = Nothing End Sub "RA" wrote: I currently receive a file that has a persons ID # and then a list of work skill #s they have been assigned. I need to have that data modified. EX: the data comes to me in this format 12345 100 101 110 52 12346 99 191 45 17 12347 150 101 52 99 I need the data to come out in this format: 12345 100 12345 101 12345 110 12345 52 12346 99 12346 191 12346 45 12346 17 12347 150 Etc. I have dozens of lines long and when converted will be hundreds of lines long. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
Worked the first time, thanks
"JLatham" wrote: I'm going to assume that you have somehow gotten the initial data into an Excel workbook, onto one sheet. The code below will read the data and lay it out as you've asked on another sheet that is presumed to be empty when you start (if it isn't, older data may be overwritten). The code doesn't care how many IDs are in the list, nor how many job codes are associated with any of them - the number of job codes can even change from row to row. There just cannot be any blank rows between the start of the ID list and its end, and for any given row, there cannot be any empty columns on a row. An empty cell causes that part of the code to stop and move on to the next part of the process. You can change the values of the assignments to the "Const ..." objects in the code to fit your workbook setup and the code should run properly. Copy the code below and put it into a module in the workbook and use Tools -- Macro -- Macros to [Run] the macro when you need to. To get the code into the workbook, open the workbook, press [Alt]+[F11] to open the VB Editor. In it, choose Insert -- Module. Copy the code below and paste it into the module presented to you. Should be good to go at that point once you've edited it to get the right worksheet names in it and the proper address references for the start of the initial data: Sub TransposeData() 'change these definitions as needed ' 'this is the name of the sheet with your 'original data on it as laid out in your example Const sourceSheetName = "Sheet1" 'this is the column ID of the first cell with the 'first ID code in it Const sourceIDColumn = "A" 'this is the row number with the first cell with 'the first ID code in it Const sourceIDRow = 2 ' assumes label in row 1 'this is the name of the BLANK sheet that 'we will move the data to Const destSheetName = "Sheet2" ' 'these are working values, you don't need 'to concern yourself with them Dim sourceSheet As Worksheet Dim destSheet As Worksheet Dim sourceIDList As Range Dim anySourceID As Range Dim sourceColOffset As Integer Dim baseCell As Range Dim destRowOffset As Long Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName) Set sourceIDList = sourceSheet.Range(sourceIDColumn & _ sourceIDRow & ":" & _ sourceSheet.Range(sourceIDColumn & Rows.Count).End(xlUp).Address) Set destSheet = ThisWorkbook.Worksheets(destSheetName) Set baseCell = destSheet.Range("A2") ' leave room for label Application.ScreenUpdating = False For Each anySourceID In sourceIDList sourceColOffset = 1 Do While Not IsEmpty(anySourceID.Offset(0, sourceColOffset)) baseCell.Offset(destRowOffset, 0) = anySourceID baseCell.Offset(destRowOffset, 1) = anySourceID.Offset(0, sourceColOffset) destRowOffset = destRowOffset + 1 sourceColOffset = sourceColOffset + 1 Loop Next Set sourceIDList = Nothing Set baseCell = Nothing Set sourceSheet = Nothing Set destSheet = Nothing End Sub "RA" wrote: I currently receive a file that has a persons ID # and then a list of work skill #s they have been assigned. I need to have that data modified. EX: the data comes to me in this format 12345 100 101 110 52 12346 99 191 45 17 12347 150 101 52 99 I need the data to come out in this format: 12345 100 12345 101 12345 110 12345 52 12346 99 12346 191 12346 45 12346 17 12347 150 Etc. I have dozens of lines long and when converted will be hundreds of lines long. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data
Glad to hear it, thanks for the feedback.
"RA" wrote: Worked the first time, thanks "JLatham" wrote: I'm going to assume that you have somehow gotten the initial data into an Excel workbook, onto one sheet. The code below will read the data and lay it out as you've asked on another sheet that is presumed to be empty when you start (if it isn't, older data may be overwritten). The code doesn't care how many IDs are in the list, nor how many job codes are associated with any of them - the number of job codes can even change from row to row. There just cannot be any blank rows between the start of the ID list and its end, and for any given row, there cannot be any empty columns on a row. An empty cell causes that part of the code to stop and move on to the next part of the process. You can change the values of the assignments to the "Const ..." objects in the code to fit your workbook setup and the code should run properly. Copy the code below and put it into a module in the workbook and use Tools -- Macro -- Macros to [Run] the macro when you need to. To get the code into the workbook, open the workbook, press [Alt]+[F11] to open the VB Editor. In it, choose Insert -- Module. Copy the code below and paste it into the module presented to you. Should be good to go at that point once you've edited it to get the right worksheet names in it and the proper address references for the start of the initial data: Sub TransposeData() 'change these definitions as needed ' 'this is the name of the sheet with your 'original data on it as laid out in your example Const sourceSheetName = "Sheet1" 'this is the column ID of the first cell with the 'first ID code in it Const sourceIDColumn = "A" 'this is the row number with the first cell with 'the first ID code in it Const sourceIDRow = 2 ' assumes label in row 1 'this is the name of the BLANK sheet that 'we will move the data to Const destSheetName = "Sheet2" ' 'these are working values, you don't need 'to concern yourself with them Dim sourceSheet As Worksheet Dim destSheet As Worksheet Dim sourceIDList As Range Dim anySourceID As Range Dim sourceColOffset As Integer Dim baseCell As Range Dim destRowOffset As Long Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName) Set sourceIDList = sourceSheet.Range(sourceIDColumn & _ sourceIDRow & ":" & _ sourceSheet.Range(sourceIDColumn & Rows.Count).End(xlUp).Address) Set destSheet = ThisWorkbook.Worksheets(destSheetName) Set baseCell = destSheet.Range("A2") ' leave room for label Application.ScreenUpdating = False For Each anySourceID In sourceIDList sourceColOffset = 1 Do While Not IsEmpty(anySourceID.Offset(0, sourceColOffset)) baseCell.Offset(destRowOffset, 0) = anySourceID baseCell.Offset(destRowOffset, 1) = anySourceID.Offset(0, sourceColOffset) destRowOffset = destRowOffset + 1 sourceColOffset = sourceColOffset + 1 Loop Next Set sourceIDList = Nothing Set baseCell = Nothing Set sourceSheet = Nothing Set destSheet = Nothing End Sub "RA" wrote: I currently receive a file that has a persons ID # and then a list of work skill #s they have been assigned. I need to have that data modified. EX: the data comes to me in this format 12345 100 101 110 52 12346 99 191 45 17 12347 150 101 52 99 I need the data to come out in this format: 12345 100 12345 101 12345 110 12345 52 12346 99 12346 191 12346 45 12346 17 12347 150 Etc. I have dozens of lines long and when converted will be hundreds of lines long. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rearranging DATA | Excel Discussion (Misc queries) | |||
Rearranging data | Excel Discussion (Misc queries) | |||
rearranging data | Excel Worksheet Functions | |||
Rearranging the layout of data | Excel Discussion (Misc queries) | |||
Rearranging Data Help... | Excel Discussion (Misc queries) |