brent.fraser
06-16-2016, 12:50 PM
I have a report that's 800,000 lines that I have to parse in a report.
The data is configured as follows:
TagId
InfoClass
Value
12163
J15
0
12163
D36
0
12163
K25
0
12163
B3
0
12163
K41
0
12165
K35
0
12165
F1
1
12165
D38
1
12165
F7
0
12165
H9
0
12180
F1
1
12180
K41
0
12180
K44
0
12180
D38
1
12180
H9
0
What I'd like it to be is this:
TagId
Value
12163
0
0
0
0
0
12165
0
1
1
0
0
12180
1
0
0
1
0
Where each row of duplicated values in column A is consolidated into columns B, C, D etc.
Sometimes there will need to be 9 columns, sometimes 3 so that will not be consistent.
I found a macro that concatenated the information into column B but I need it to be in separate columns.
The code I found was as follows (close to what I need it for):
Sub mergeCategoryValues()
Dim lngRow As Long
With ActiveSheet
lngRow = .Cells(1048576, 1).End(xlUp).Row
.Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes
MsgBox lngRow
Do
If .Cells(lngRow, 1) = .Cells(lngRow - 1, 1) Then
.Cells(lngRow - 1, 3) = .Cells(lngRow - 1, 3) & "; " & .Cells(lngRow, 3)
.Cells(lngRow - 1, 4) = .Cells(lngRow - 1, 4) + .Cells(lngRow, 4)
.Rows(lngRow).Delete
End If
lngRow = lngRow - 1
Loop Until lngRow = 1
End With
End Sub
Thanks for the help peoples.
The data is configured as follows:
TagId
InfoClass
Value
12163
J15
0
12163
D36
0
12163
K25
0
12163
B3
0
12163
K41
0
12165
K35
0
12165
F1
1
12165
D38
1
12165
F7
0
12165
H9
0
12180
F1
1
12180
K41
0
12180
K44
0
12180
D38
1
12180
H9
0
What I'd like it to be is this:
TagId
Value
12163
0
0
0
0
0
12165
0
1
1
0
0
12180
1
0
0
1
0
Where each row of duplicated values in column A is consolidated into columns B, C, D etc.
Sometimes there will need to be 9 columns, sometimes 3 so that will not be consistent.
I found a macro that concatenated the information into column B but I need it to be in separate columns.
The code I found was as follows (close to what I need it for):
Sub mergeCategoryValues()
Dim lngRow As Long
With ActiveSheet
lngRow = .Cells(1048576, 1).End(xlUp).Row
.Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes
MsgBox lngRow
Do
If .Cells(lngRow, 1) = .Cells(lngRow - 1, 1) Then
.Cells(lngRow - 1, 3) = .Cells(lngRow - 1, 3) & "; " & .Cells(lngRow, 3)
.Cells(lngRow - 1, 4) = .Cells(lngRow - 1, 4) + .Cells(lngRow, 4)
.Rows(lngRow).Delete
End If
lngRow = lngRow - 1
Loop Until lngRow = 1
End With
End Sub
Thanks for the help peoples.