Is there a Formula or Macro for This?
 

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,

Is there a Formula or Macro for This?
 

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
--
Gary''s Student - gsnu200719


"Marilyn" wrote:

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,

Is there a Formula or Macro for This?
 

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


"Marilyn" wrote in message
...
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,

Is there a Formula or Macro for This?
 

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

--
Don Guillett
SalesAid Software

"Marilyn" wrote in message
...
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,

Is there a Formula or Macro for This?
 

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

"PCLIVE" wrote:

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


"Marilyn" wrote in message
...
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,

Is there a Formula or Macro for This?
 

Your welcome. Thanks for the feedback.

"Marilyn" wrote in message
...
AMAZING what you can do with Macros.....This one works for me....Thanks a
bunch

"PCLIVE" wrote:

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


"Marilyn" wrote in message
...
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,

Is there a Formula or Macro for This?
 

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

--
Don Guillett
SalesAid Software

"Marilyn" wrote in message
...
AMAZING what you can do with Macros.....This one works for me....Thanks a
bunch

"PCLIVE" wrote:

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


"Marilyn" wrote in message
...
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,

Is there a Formula or Macro for This?
 

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" wrote:

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

--
Don Guillett
SalesAid Software

"Marilyn" wrote in message
...
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,

Is there a Formula or Macro for This?
 

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

--
Don Guillett
SalesAid Software

"Marilyn" wrote in message
...
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" wrote:

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

--
Don Guillett
SalesAid Software

"Marilyn" wrote in message
...
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,