Please Help me about Macros

[sampath]

Hello friend,


I need to know the following numbering convertion using macros in excel.


I/P (Sheet 1)

A B

S1 150-172-200

S2 150-172-174

S3 485-754-781

S4 119-149-163-242

S5 150-MF-R319


Needed O/P (Sheet 2)

A B

119 S4

149 S4

150 S1-S2-S5

172 S1-S2

163 S4

174 S2

200 S1

242 S4

754 S3

781 S3

MF S5

R319 S5


I need this kind of output for our project. Anyone could help me for get such kind of output using VB macros.


Thank with Regards,

Sampath

 

[Luke M]

I think this will work.

[pre]

Sub TransferData()
Dim LastRow As Long
Dim xValues As Variant
Dim xCode As String
Dim RecordCount As Long
Dim i as Long
Dim x as Long
Dim NewRecords As Long

Application.ScreenUpdating = False

'Starting output row
RecordCount = 1

'Build new table
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'Assuming that data on sheet1 starts on row 1
For i = 1 To LastRow
xCode = .Cells(i, "A").Value
xValues = Split(.Cells(i, "B").Value, "-")
For x = 0 To UBound(xValues)
Worksheets("Sheet2").Cells(RecordCount, "D") = xCode
Worksheets("Sheet2").Cells(RecordCount, "C") = xValues(x)
RecordCount = RecordCount + 1
Next x
Next i
End With

NewRecords = 1
xCode = ""
xValues = ""
With Worksheets("Sheet2")
'Sort data
.Range("C1:D" & RecordCount).Sort Key1:=.Range("C1"), Header:=xlNo

'Start building new list
For i = 1 To RecordCount
xCode = .Cells(i, "C").Value
xValues = xValues & "-" & .Cells(i, "D").Value
If xCode <> .Cells(i + 1, "C").Value Then
.Cells(NewRecords, "A") = xCode
.Cells(NewRecords, "B") = Mid(xValues, 2)
NewRecords = NewRecords + 1
xValues = ""
End If
Next i
'Clear temp list
.Range("C1:D" & RecordCount).ClearContents

End With

Application.ScreenUpdating = True
End Sub

[/pre]

 

[sampath]

Dear Luke,


It is work very charm. Thanks for your asistance.


Thank with Regards,

Sampath.S

 

[Luke M]

Sweet, glad it works. thanks for the feedback. =)