Matching & adding text in the respective column

A

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 ?
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top