Repetative problem

[jee22]

Good morning Ladies and Gentlemen

I have a list as below of components. You will note that 5 are of the same
value and C4 C5 are the same value
A B
C1 0.1uf
C2 0.1uf
C3 0.1uf
C6 0.1uf
C9 0.1uf
C4 12pF
C5 12pF


What I would like to do is have them displayed thus
A B
C1,C2,C3,C6,C9 0.1uF
C4,C5 12pF

There are of course other values like R1 V1 TR1 and so on, and they all are
place on a new row even though their B value is the same.

I would be grateful for any help. I have tried using VLOOKUP but cannot get
it to work and I am sure that's the wrong approach anyway
--


_____________________________________________
_____________________________________________
Mr J E Ellis
University of Cambridge
Department of Materials Science & Metallurgy
Pembroke Street
Cambridge
CB2 3QZ
England
Tel + 44-(0) 1223 334346
Fax + 44-(0) 1223 334567
E-mail (e-mail address removed)
_____________________________________________

 

[Dave Peterson]

I'd use a macro like this:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set wks = ActiveSheet

With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "b").Value = .Cells(iRow - 1, "b").Value Then
.Cells(iRow - 1, "A").Value _
= .Cells(iRow - 1, "A").Value & "," _
& .Cells(iRow, "A").Value
.Rows(iRow).Delete
End If
Next iRow
.UsedRange.Columns.AutoFit
End With

End Sub

Now the warnings.

Your data needs to be sorted first. If you want to add the sort to the process,
record a macro when you do it manually and plop that in the routine.

And this will destroy the original data. I'd test it against a copy of your
real worksheet.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[jee22]

Thanks Dave

When I get a moment I will try it out.