I have text data in following way

high india
high australia
low russia
medium france
medium italy
low germany
high spain

Now in a separate sheet, I need result for high, medium & low by adding the
respective text of the same such as follows

High india australia spain
medium france italy
low russia germany

Can u help me phrasing the formula for the same ?



--
ashutosh

for this particular case you might use the following function:

Function conn(rg As Range, lev As String) As String
Dim counter As Integer

For Each cell In rg.Columns(1).Cells
If cell.Value = lev Then
counter = counter + 1
If counter 1 Then
conn = conn & ", " & cell.Offset(0, 1).Value
Else
conn = cell.Offset(0, 1).Value
End If
End If
Next cell

End Function

press ALT+F11 to get to Visual Basic window, then Insert-Module,
paste the code into the module
then come back to yr worksheet and insert =conn(your_range,A1) where
A1="low", A2="medium...

copy down

adjust your ranges/addresses to suit

pls click YES if this helped



On 23 Kwi, 12:14, ashutosh wrote:
I have text data in following way

high * * * * * *india * * * * * * *
high * * * * * *australia
low * * * * * * russia
medium * * * france
medium * * * italy
low * * * * * * germany
high * * * * * *spain

Now in a separate sheet, I need result for high, medium & low by adding the
respective text of the same such as follows

High * * * *india australia spain
medium * france italy
low * * * * *russia germany

Can u help me phrasing the formula for the same ?

--
ashutosh