Pls guide me

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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