Looping Through Records.
 

Thank you in advance for your time and help.

Here is the senario:
I have a table with Vendor Names and Y or N next to each
name.

Vendor Name Qualified
A N
B Y
C Y
D N
E Y

I would like to concatenate all the Vendor Names that are
qualified in one cell. So for this example the result
would be: B, C, E

I realize that I need to write a formula to loop through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)

Looping Through Records.
 

Hi Diana,

Place the code below in the standard module and please try this.
Assume the field Vendor Name is located A column.

Sub ConcatenateVendorNames()
Dim a, ret As String, i As Long
Dim c As Range
On Error Resume Next
Set c = Application.InputBox("Please select a cell for return value",
Type:=8)
a = Columns(1).SpecialCells(2).Resize(, 2).Value
For i = LBound(a) To UBound(a)
If UCase(Trim(a(i, 2))) = "Y" Then ret = ret & a(i, 1) & ","
Next
ret = Left(ret, Len(ret) - 1)
c.Value = ret: Set c = Nothing
Exit Sub
End Sub


"Diana" wrote in message
...
Thank you in advance for your time and help.

Here is the senario:
I have a table with Vendor Names and Y or N next to each
name.

Vendor Name Qualified
A N
B Y
C Y
D N
E Y

I would like to concatenate all the Vendor Names that are
qualified in one cell. So for this example the result
would be: B, C, E

I realize that I need to write a formula to loop through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)

Looping Through Records.
 

In a third column, put the formula
=IF(B2 = "Y", A2 & ", " & C3, C3)
and copy it down for the entire column,
where column A is the vendors, column B is the qualified
and column C is the formula.

The cell to display the combined result could be
=LEFT(A2, LEN(A2) - 2)
to get rid of the trailing comma and space

Kevin Beckham

-----Original Message-----
Thank you in advance for your time and help.

Here is the senario:
I have a table with Vendor Names and Y or N next to each
name.

Vendor Name Qualified
A N
B Y
C Y
D N
E Y

I would like to concatenate all the Vendor Names that are
qualified in one cell. So for this example the result
would be: B, C, E

I realize that I need to write a formula to loop through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)
.

Looping Through Records.
 

I suggest a function thatreturns the text to the sheet.

Function JoinString(MyRange As Range)

Dim iLoopCounter As Long
Dim TextList As String
Dim Cell As Range


' start loop
For Each Cell In MyRange.Cells

'check test condition
If Cell.Offset(0, 1).Value = "Y" Then
TextList = TextList & "," & Cell.Value
End If

Next Cell

'remove preceding comma
TextList = Mid(TextList, 2)

'return the result
JoinString = TextList

End Function


If your data is in the range A1:B5
then in any other cell type
=JoinString(A1:5)
and you'll see
B,C,E

Method: Use a FOR Each loop. As we have passes a range,
we can check each cell in the range. If the cell to its
right is Y then we add the cell value to the string.

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Thank you in advance for your time and help.

Here is the senario:
I have a table with Vendor Names and Y or N next to each
name.

Vendor Name Qualified
A N
B Y
C Y
D N
E Y

I would like to concatenate all the Vendor Names that
are
qualified in one cell. So for this example the result
would be: B, C, E

I realize that I need to write a formula to loop through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)
.

Looping Through Records.
 

Patrick you are a life saver! Thank you so much.

Diana

-----Original Message-----
I suggest a function thatreturns the text to the sheet.

Function JoinString(MyRange As Range)

Dim iLoopCounter As Long
Dim TextList As String
Dim Cell As Range


' start loop
For Each Cell In MyRange.Cells

'check test condition
If Cell.Offset(0, 1).Value = "Y" Then
TextList = TextList & "," & Cell.Value
End If

Next Cell

'remove preceding comma
TextList = Mid(TextList, 2)

'return the result
JoinString = TextList

End Function


If your data is in the range A1:B5
then in any other cell type
=JoinString(A1:5)
and you'll see
B,C,E

Method: Use a FOR Each loop. As we have passes a range,
we can check each cell in the range. If the cell to its
right is Y then we add the cell value to the string.

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Thank you in advance for your time and help.

Here is the senario:
I have a table with Vendor Names and Y or N next to
each
name.

Vendor Name Qualified
A N
B Y
C Y
D N
E Y

I would like to concatenate all the Vendor Names that
are
qualified in one cell. So for this example the result
would be: B, C, E

I realize that I need to write a formula to loop
through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)
.

.

Looping Through Records.
 

Thank you! This loop has a little more features than I
need but I am sure it will be helpful to me in the future.

Diana

-----Original Message-----
Hi Diana,

Place the code below in the standard module and please
try this.
Assume the field Vendor Name is located A column.

Sub ConcatenateVendorNames()
Dim a, ret As String, i As Long
Dim c As Range
On Error Resume Next
Set c = Application.InputBox("Please select a cell
for return value",
Type:=8)
a = Columns(1).SpecialCells(2).Resize(, 2).Value
For i = LBound(a) To UBound(a)
If UCase(Trim(a(i, 2))) = "Y" Then ret = ret & a
(i, 1) & ","
Next
ret = Left(ret, Len(ret) - 1)
c.Value = ret: Set c = Nothing
Exit Sub
End Sub


"Diana" wrote in message
...
Thank you in advance for your time and help.

Here is the senario:
I have a table with Vendor Names and Y or N next to
each
name.

Vendor Name Qualified
A N
B Y
C Y
D N
E Y

I would like to concatenate all the Vendor Names that
are
qualified in one cell. So for this example the result
would be: B, C, E

I realize that I need to write a formula to loop
through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)

.

Looping Through Records.
 

Kevin this is genious - and no loops:)

Thank you
Diana

-----Original Message-----
In a third column, put the formula
=IF(B2 = "Y", A2 & ", " & C3, C3)
and copy it down for the entire column,
where column A is the vendors, column B is the qualified
and column C is the formula.

The cell to display the combined result could be
=LEFT(A2, LEN(A2) - 2)
to get rid of the trailing comma and space

Kevin Beckham

-----Original Message-----
Thank you in advance for your time and help.

Here is the senario:
I have a table with Vendor Names and Y or N next to
each
name.

Vendor Name Qualified
A N
B Y
C Y
D N
E Y

I would like to concatenate all the Vendor Names that
are
qualified in one cell. So for this example the result
would be: B, C, E

I realize that I need to write a formula to loop
through
the data but I am not exactly sure how this is done. I
think I have a phobia of loops and arrays:)
.

.