VBA for may row data joint in one row

Hello forum Experts & Guru’s
We want a VBA for my following problem
1. we have a a Detaild database of more then 8000 road record database in excel sheet (Sample Data type show in sheet 1 in enclosed file) in more then one row
2. We want arrange this data base in one road data in single row.
3. We also show what is database & what result I want
4. we also want a process button in sheet 1 where we click then macro run

Pls help

Thanks in advance
Santosh

Try the attached

Hello oeldere & jindon First thanks for your reply..

oeldere sir... pls send 2003 xls file so that we look your approach ....

jindon sir your provided file work as per my requirement but every time this create a new sheet....
we want only one sheet where data copy i.e. sheet2

pls also tell me about ur code working with comment so that we can easily understand..code..

thanks both of u we also add reputation both of u..

Hello oeldere your provided macro & file not working as per my requiremtn.. u provided macro fill number in colum a then when we filter in total coloum then Road name & kms nos in one cell not resulted....


Thanks for your trying

.............

sir we copy ur provided filldata_acending_incolumnA_Oeldere() in module 1 then we run this macro ...
after run macro ..
in sheet1 colum a value filldata as per macro.. then as per ur instruction we filter data on total..

but sir when we filter data on total then

We never see road name.. & kms nos....

we only shee sr no. 1,2,3,4 in colum a colum B we see total on all filter row.. & sum of informt of total row..

sir we want VBA to copy data from sheet 1 to sheet 2

sr. road name & kms nos which is display in various row's in sheet1..

Thanks for help but my problem is solved with jidon vba... but we r tying to understant that vba coding..

we also asking for that with JINDON

Just a brief explanation.

1) Store the data in an Array variable.
2) Loop though row to find the unique Sr. No.
3) Combine duplicates in same row concatenating col.3, and sum the rest of the columns.
4) Output the result in Sheet2.

Ask only where you don't understand, I don't want to explain from the basics.

Thank you so much.. for help to solve my problem

JINDON SIR if we want to run code with in a range then where we change pls suggest

For example we want to run code from Row No 8 to 23

pls suggest me..

Sub test()
    Dim a, i As Long, ii As Long, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    a = Selection.Value
    For i = 1 To UBound(a, 1)
        If i > 1 Then
            If a(i, 1) = "" Then a(i, 1) = a(i - 1, 1)
        End If
        If a(i, 2) <> "TOTAL" Then
            If Not dic.exists(a(i, 1)) Then
                dic(a(i, 1)) = dic.Count + 1
                For ii = 1 To UBound(a, 2)
                    a(dic(a(i, 1)), ii) = a(i, ii)
                Next
            Else
                a(dic(a(i, 1)), 3) = _
                a(dic(a(i, 1)), 3) & ", " & a(i, 3)
                For ii = 4 To UBound(a, 2)
                    a(dic(a(i, 1)), ii) = a(dic(a(i, 1)), ii) + a(i, ii)
                Next
            End If
        End If
    Next
    With Sheets("sheet2").Cells(1).Resize(dic.Count, UBound(a, 2))
        .CurrentRegion.Clear
        .Value = a
        .Borders.Weight = 2
        .Columns.AutoFit
        .Parent.Select
    End With
End Sub