Previous Topic Next Topic

Pls guide me

Closed
SS - Jun 19, 2009 at 01:19 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 19, 2009 at 08:50 PM
Hello,

Thank u for ur support.

I have an excel sheet, 2 column.

S. No Concern Total
1. Delay Delivery 4
2. Water wash 5
3. Attitude 1
4. Tyre 1
5. Battery 1

This is content, now i want find the highest no of concern and result to be displayed in another excel sheet with concern and total no details.
for eg.

Water wash 5
Delay Delivery 4
Attitude 1
Tyre 1
Battery 1 like this i need result in excel sheet. Can u pls guide me.

Thanks

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 19, 2009 at 08:50 PM
Jindon has given this macro which I have adapted to suit your data .POST FEEDBACK 

Sub test()
Dim r As Range, i As Long
With Worksheets("sheet1")
For Each r In Range(.Range("b2"), Range("b" & Rows.Count).End(xlUp))
    
    If Len(r.Value) Then
        For i = 1 To Len(r.Value)
            If Val(Mid$(r.Value, i)) <> 0 Then
                r.Offset(, 1).Value = Val(Mid$(r.Value, i))
                Exit For
            End If
        Next
    End If
Next
.Range("c1") = "number"
.Range("a1").Sort key1:=.Columns("c"), order1:=xlDescending, header:=xlYes
.Columns("c:c").Delete
End With
End Sub
0