Matching & adding text in the respective column

[ashutosh]

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 ?

 

[Jarek Kujawa]

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