Counting Consecutive Values in a range but for each occurence a distinct count

Hello friends,

I am stuck at a problem which I thought was easy to overcome but am now confused. Any help shall be appreciated.
My problem and the answer I seek: I attach a snapshot as the columns may not come good in this post.

City 1 Delhi 0 Answer that I want is like the following Arrangement
City 2 Agra 1 Delhi 0 Delhi comes here
City 3 Jaipur 4 Agra 1
City 4 udaipur 2 Jaipur 4
City 5 jodhpur 2 Udaipur 2
City 6 jaisalmer 1 Jodhpur 2
City 7 jaisalmer 1 Jaisalmer 4 Jaisalmer adds to 4
City 8 jaisalmer 1 Bikaner 2
City 9 jaisalmer 1 Nawalgarh 1
City 10 Bikaner 2 Delhi 3 Delhi adds to 3
City 12 Nawalgarh 1 Agra 1
City 13 Delhi 1 Jaipur 1
City 14 Delhi 1 Pushkar 1
City 15 Delhi 1 Delhi 1 Delhi remains 1
City 16 Agra 1 last day of tour 0
City 17 Jaipur 1
City 18 Pushkar 1
City 19 Delhi 1
City 20 last day of tour 0
City 21 NA 0
City 22 NA 0
City 24 NA 0
City 25 NA 0
City 26 NA 0
City 27 NA 0
City 28 NA 0
City 29 NA 0
City 30 NA 0
City 31 NA 0
City 32 NA 0
City 33 NA 0
City 34 NA 0
City 35 NA 0
City 36 NA 0
City 37 NA 0
City 38 NA 0
City 39 NA 0
City 39 NA 0

Hi,

Welcome to the forum...On what basis are the numbers added... I see that in your solution, almost all the cities are listed twice. Please explain that a bit

Also, it would be great if you can post a dummy workbook instead of an image...

Originally Posted by inayat

Hi,

Welcome to the forum...On what basis are the numbers added... I see that in your solution, almost all the cities are listed twice. Please explain that a bit

Also, it would be great if you can post a dummy workbook instead of an image...

Thank you for the warm welcome.

This is a tour itinerary and the final output will be like

1nt Delhi | 4nt Jaipur | 2nt Udaipur | 2nt Jodhpur | 4nt Jaisalmer | 2nt Bikaner | 1nt Nawalgarh | 3nt Delhi | 1nt Agra | 1nt Jaipur | 1nt Pushkar | 1nt Delhi |

All the solutions that I came across totals Delhi to 5 when I wish it to be 1, 3, 1 i.e each consecutive block is counted individually not comprehensively.

I am attaching a worksheet.