Is there a Formula or Macro for This?

[Marilyn]

I'm in Excel 2003. I have a sheet with the following data:
ColumnA Column B
Reports ID
Report1 53124
Report1 52145
Report3 58216
Report2 58691

I have been trying to create a formula that will give me all of the ID
numbers associated with Report1, Report2, Report3...and separate each ID with
a Comma. For example the Results will look like this:

Column C
Report1 53124, 52145

Is there something I can use to achieve this results?

Thanks,

 

[Gary''s Student]

There is a nice feature call AutoFilter that you can use:

Data > Filter > AutoFilter

You can specify the Report and it will product a list of the associated IDs

 

[PCLIVE]

This code will get you close to what you want. Cell C1 contains the report
you want (example. - Report1, Report2, etc.). The result is placed in cell
D1

Sub test()
For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row)

If cell.Value = Range("C1").Value _
Then
r = r & cell.Offset(0, 1).Value & ", "
Else
End If

Next cell
Range("D1").Value = Range("C1").Value & " " & r
End Sub


HTH,
Paul

 

[Don Guillett]

Sub makelist()
For i = 1 To 3
ms = ""
For Each c In Range("i2:i5")
If Left(c, 7) = "Report" & i Then ms = ms & "," & c.Offset(, 1)
Next c
MsgBox "Report" & i & " " & Right(ms, Len(ms) - 1)
Cells(i, "k").Value = "Report" & i & " " & Right(ms, Len(ms) - 1)
Next i
End Sub

 

[Marilyn]

AMAZING what you can do with Macros.....This one works for me....Thanks a bunch

 

[PCLIVE]

Your welcome. Thanks for the feedback.

AMAZING what you can do with Macros.....This one works for me....Thanks a
bunch

 

[Don Guillett]

If you like that one you should take a look at mine

 

[Marilyn]

Don, it's your macro that works for me. The other one gives me a bunch of
commas with no values.

Once again Thanks a Bunch

 

[Don Guillett]

The other one would have worked for ONE report had you put Report1 in cell
c1 and it would have left a , at the end
glad to help