Macro to sort through data

[systemx]

Hi all,

I was wandering if anyone can offer any advice on a problem.

Basically, in A:A I have a list of numbers. In B:B I have a list of
corresponding values. The values in B:B are always unique, but values
in A:A may be the same. Identical values will always appear together
(eg A1, A2, A3, etc).

I want to make a macro to sort through this data, and if A1=A2, copy B2
to C1. If A1=A3, copy B3 to A1.

Then move down the list in A:A until it finds the next value....and
repeat.

So in essence...where values in A:A are identical, column B:B is copied
and then transposed into the first cell containing the value.

If this does not make sense...the diagram below may help explain!

Original list -

A B
1 569
1 572
2 433
3 625
4 744
5 766
5 767
5 792

Sorted List -

A B C D
1 569 572
1 572
2 433
3 625
4 744
5 766 767 792
5 767
5 792

Thank you in advance for any advice!

Robert

 

[Guest]

Try:

Sub TransposeRepeats()
Dim r As Range
Dim ws As Worksheet
Dim i As Integer, ii As Integer

Set ws = Sheets("Sheet1")
Set r = ws.Range(ws.Cells(2, 1), _
ws.Cells(2, 1).End(xlDown))
With Application
.ScreenUpdating = False
For i = 1 To r.Count
ii = 1
Do Until r(i + ii) <> r(i)
ii = ii + 1
Loop
If ii > 1 Then
r(i, 2).Resize(1, ii) = _
.Transpose(r(i, 2).Resize(ii, 1))
i = i + ii - 1
End If
Next
.ScreenUpdating = True
End With
End Sub

Change ws name and ranges to suit.

Regards,
Greg