Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:) . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete records when certain records have duplicate column data | New Users to Excel | |||
Looping | Excel Discussion (Misc queries) | |||
Looping | Excel Discussion (Misc queries) | |||
Looping | Excel Programming | |||
Need Looping Help | Excel Programming |