PDA

View Full Version : Combine rows with duplicate values into columns



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.

snb
06-16-2016, 01:44 PM
SUb M_snb()
sn=sheet1.cells(1).currentregion

with createobject("scripting.dictionary")
for j=1 to ubound(sn)
.item(sn(j,1))= .item(sn(j,1)) & " " & sn(j,3)
next

for j=1 to .count
Sheet2.Cells(j, 1).Resize(, 6) = Split(.keys()(j - 1) & .items()(j - 1))
next
end with
End Sub

p45cal
06-16-2016, 04:27 PM
…or just before the End With line in the code in msg#1 above, add the following:
Application.DisplayAlerts = False
.Cells(1).CurrentRegion.Columns(3).TextToColumns Destination:=.Cells(1, 2), ConsecutiveDelimiter:=True, Semicolon:=True
Application.DisplayAlerts = True
and remove the space next to the semicolon (between the red characters) in:
.Cells(lngRow - 1, 3) = .Cells(lngRow - 1, 3) & "; " & .Cells(lngRow, 3)