Count numbers in data

[SanjayaGarg]

Dear Friends ! I have a large data in a column containing 'Route No.' and below it nos. then again route no. then no. and so on. as shown in attached example. Now the required thing is that route nos are shown in another column continuously and count of nos. in that route no. in front of that. e.g.

 

[rlv01]

FWIW, when you post an image of your data instead of something that can be copied and pasted into a spreadsheet, it is difficult for others to experiment with it. Which means your chances of getting help drop significantly. Instead, use the free XL2BB tool (link below) to post your data in a way that makes it accessible to others.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[SanjayaGarg]

Dear Friends ! I have a large data in a column containing 'Route No.' and below it nos. then again route no. then no. and so on. as shown in attached example. Now the required thing is that route nos are shown in another column continuously and count of nos. in that route no. in front of that. e.g. View attachment 110439

		Result
Data		Route No.	Count	Sum
Route No. 54		Route No. 54	5​	516​
45​		Route No. 56	4​	1122​
345​		Route No. 59	5​	3513​
23​		Route No. 60	6​	2302​
57​
46​
Route No. 56
487​
324​
78​
233​
Route No. 59
21​
435​
445​
267​
2345​
Route No. 60
59​
467​
234​
276​
879​
387​

 

[SanjayaGarg]

Can it be done?

 

[Joe4]

Can it be done?

Because the route number of each block only appears at the top and not on each row, and the number of rows in each block can change, the only way I can think of is to use VBA.
Are you open to a VBA solution?

 

[Joe4]

Here is the VBA code I came up with:

Sub GetTotals()

    Dim rng1 As Range
    Dim cell As Range
    Dim rt As String
    Dim fnd As Range
    Dim ct As Integer
    Dim tot As Double
    Dim lr As Long
    Dim rng2 As Range
        
    Application.ScreenUpdating = False
    
'   Get range of values to total (starting in cell C3)
    Set rng1 = Range("C3:C" & Range("C2").End(xlDown).Row)
    
'   Loop through all cells in range of values to total
    For Each cell In rng1
'       Get route number
        rt = cell.Value
'       Search for route number in column A
        Set fnd = Columns("A:A").Find(What:=rt, After:=Range("A1"), LookIn:= _
            xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False)
'       If not found, put 0 for Count and Sum in columns D and E
        If fnd Is Nothing Then
            cell.Offset(0, 1).Value = 0
            cell.Offset(0, 2).Value = 0
        Else
'           See if any data below heading, and if not, put in zeroes
            If fnd.Offset(1, 0) = "" Then
                cell.Offset(0, 1).Value = 0
                cell.Offset(0, 2).Value = 0
            Else
'               Find last row in data block
                lr = fnd.End(xlDown).Row
'               Get counts and sums
                Set rng2 = Range(Cells(fnd.Row + 1, "A"), Cells(lr, "A"))
                cell.Offset(0, 1).Value = lr - fnd.Row
                cell.Offset(0, 2).Value = Application.WorksheetFunction.Sum(rng2)
            End If
'           Reset found range
            Set fnd = Nothing
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

And here is the proof (everything in red populated by VBA code):

 

[SanjayaGarg]

Thanks Joe for the code. Can this be done without VBA code, only by using formula. Actually this was asked in job interview. Interviewer asked that without VBA code how would you get the result? Of course, question was not answered.

 

[Cubist]

In the older version, I used a helper column. However, with a newer version like 365, it can be done without the helper. Not sure which was allowed. I think the tricky part was to get the label for each route.

Book1
	A	B	C	D	E	F
1
2	Data	Helper
3	Route No. 54			Route No.	Count	Sum
4	45.00	Route No. 54		Route No. 54	5	516
5	345.00	Route No. 54		Route No. 56	4	1122
6	23.00	Route No. 54		Route No. 59	5	3513
7	57.00	Route No. 54		Route No. 60	6	2302
8	46.00	Route No. 54
9
10	Route No. 56
11	487.00	Route No. 56
12	324.00	Route No. 56
13	78.00	Route No. 56
14	233.00	Route No. 56
15
16	Route No. 59
17	21.00	Route No. 59
18	435.00	Route No. 59
19	445.00	Route No. 59
20	267.00	Route No. 59
21	2345.00	Route No. 59
22
23	Route No. 60
24	59.00	Route No. 60
25	467.00	Route No. 60
26	234.00	Route No. 60
27	276.00	Route No. 60
28	879.00	Route No. 60
29	387.00	Route No. 60
Sheet8

Cell Formulas
Range	Formula
E4:E7	E4	=COUNTIF($B$3:$B$29,D4)
F4:F7	F4	=SUMIF($B$3:$B$29,D4,$A$3:$A$29)
B3:B29	B3	=IF(ISNUMBER(A3),LOOKUP(2,1/(LEFT($A$3:A3,5)="Route"),$A$3:A3),"")

 

[SanjayaGarg]

In the older version, I used a helper column. However, with a newer version like 365, it can be done without the helper. Not sure which was allowed. I think the tricky part was to get the label for each route.

Book1
	A	B	C	D	E	F
1
2	Data	Helper
3	Route No. 54			Route No.	Count	Sum
4	45.00	Route No. 54		Route No. 54	5	516
5	345.00	Route No. 54		Route No. 56	4	1122
6	23.00	Route No. 54		Route No. 59	5	3513
7	57.00	Route No. 54		Route No. 60	6	2302
8	46.00	Route No. 54
9
10	Route No. 56
11	487.00	Route No. 56
12	324.00	Route No. 56
13	78.00	Route No. 56
14	233.00	Route No. 56
15
16	Route No. 59
17	21.00	Route No. 59
18	435.00	Route No. 59
19	445.00	Route No. 59
20	267.00	Route No. 59
21	2345.00	Route No. 59
22
23	Route No. 60
24	59.00	Route No. 60
25	467.00	Route No. 60
26	234.00	Route No. 60
27	276.00	Route No. 60
28	879.00	Route No. 60
29	387.00	Route No. 60
Sheet8

Cell Formulas
Range	Formula
E4:E7	E4	=COUNTIF($B$3:$B$29,D4)
F4:F7	F4	=SUMIF($B$3:$B$29,D4,$A$3:$A$29)
B3:B29	B3	=IF(ISNUMBER(A3),LOOKUP(2,1/(LEFT($A$3:A3,5)="Route"),$A$3:A3),"")

Thanks Cubist. Solved the problem. Thanks again.