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?

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?

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?

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?