Transposing data linked to a main part number

[dean70]

Hi all

Hope someone can help,
Column A is the main item
Column B is the item description
Column C is the items linked to the main item

the first 3 rows are correct and are how I need the linked items displayed (transposed, columns merged;separated with a semi colon, pasted in the first row of the main item and the remaining rows for the main item deleted)

The number of linked items for each main item differ.

thanks

<table style="border-collapse: collapse; width: 812pt;" border="0" cellpadding="0" cellspacing="0" width="1082"><col style="width: 69pt;" width="92"> <col style="width: 97pt;" width="129"> <col style="width: 646pt;" width="861"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl65" style="height: 11.25pt; width: 69pt;" height="15" width="92">main item</td> <td class="xl65" style="width: 97pt;" width="129">Desc</td> <td class="xl65" style="width: 646pt;" width="861">linkedITEM</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07003</td> <td class="xl66" style="width: 97pt;" width="129">item description
</td> <td class="xl67">301056;301082;520068;933111;421015;429008;450051</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07007</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">301021;301056;301082;520068;933111;421015;429008;450051</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07011</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">303164;300004;301082;322006</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07015</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">303124</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07015</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">301031</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07015</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">301056</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07016</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">301056</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07016</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">301082</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07016</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">821352</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07016</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">821585</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07016</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">851071</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07019</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">301056</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07019</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">520034</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07019</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">917004</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07019</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">933111</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07019</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">429002</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07019</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">450014</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl66" style="height: 11.25pt; width: 69pt;" height="15" width="92">07019</td> <td class="xl66" style="width: 97pt;" width="129">item description</td> <td class="xl66" style="width: 646pt;" width="861">011086</td> </tr> </tbody></table>

 

[SteveO59L]

How many entries do you have/how often would you do this ?

 

[dean70]

35947 rows

 

[SteveO59L]

Not quite what you are after, but........

Transposes the linked items into seperate cells on the same row as the main item




Application.ScreenUpdating = False

Dim c As Range, rng
Dim num_rows As Integer
Dim LR As Long, i As Long


Columns("A:B").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Columns("A:b").Select
Selection.Insert shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R1C3:R1000C3,RC[2])"
Selection.AutoFill Destination:=Range("A1:A1000"), Type:=xlFillDefault

Range("b1") = "New"
Set rng = Range("c1:c1000")
For Each c In rng
If c.Value <> c.Offset(1, 0).Value Then
c.Offset(1, -1).Value = "New"
End If
Next c
Set rng = Range("b1:b1000")
For Each c In rng
If c.Value = "New" Then
num_rows = c.Offset(0, -1).Value
c.Offset(0, 2).Select
Selection.Resize(num_rows, 1).Select
Selection.Copy
c.Offset(0, 3).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True



End If
Next c

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
For b = LastRow To 1 Step -1
If Cells(b, 2) <> "New" Then
Rows(b).Delete
End If
Next b


Columns("A:B").Select

Application.CutCopyMode = False
Selection.Delete shift:=xlToLeft
Columns("b:b").Select

Application.CutCopyMode = False
Selection.Delete shift:=xlToLeft
Application.ScreenUpdating = True

 

[dean70]

Thanks for the prompt reply.

Unfortunately it doesn't seem to work, the actual file can be downloaded from here, maybe it would be of more helpful to see the actual cause of my headache.

 

[Temo98]

Tried to download the file but was unable.

 

[Temo98]

This piece of code assumes that the data is already sorted so the similar values are next to each other. This should get you covered.

Sub test()

Dim xRow
Dim lcell

lcell = Cells(65536, 1).End(xlUp).Row

For xRow = 2 To lcell
        If Sheet1.Range("A" & xRow) = "" Then Exit For
        If Sheet1.Range("A" & xRow).Value = Sheet1.Range("A" & xRow - 1).Value Then
            Sheet1.Range("C" & xRow - 1).Value = Sheet1.Range("C" & xRow - 1).Value & "; " & _
            Sheet1.Range("C" & xRow).Value
            Rows(xRow).Delete
            xRow = xRow - 1
        End If
Next xRow

End Sub

 

[dean70]

Works like a frikkin bomb.
Thanks Temo98, You sir are a gentleman and a scholar. You have just saved me two days of work.

Gotta go, these minesweeper games don't play themselves you know