Creating sequential count of transactions by Client ID in Excel 2

[albop]

Hi all,

I'm trying to create a count of transactions by client ID so over a period of time we would expect a client to have made multiple payments, what I need to do is number them in order of date so essentially my data will look like the below with the transaction number being the variable I need to create

Client ID Payment Date Amt Transaction No.
100 01/01/2016 50 1
100 01/02/2016 65 2
100 01/03/2016 40 3
200 05/02/2016 100 1
300 01/03/2016 75 1
300 01/05/2016 25 2

I've had a go at using a count and if formulae, but, struggling to get it to count up for each transaction and then start the count again based on a change of client ID.

I hope this makes sense! and any help you can provide would be much appreciated.
Thanks

 

[JVFriederick]

albop

Assuming the range begins in cell A1, the formula in cell D2 would be

=IF(A2<>A1,1,1+D1)

Also attached example file

 

[albop]

Thank you so much - I've been looking at it for ages and driven myself mental!!

 

[zelgar]

Of course to make the formula from JV work, the data needs to be sorted by client first.

 

[SkipVought]

Of course the formula that JVF supplied is correct as far as it goes.

However, I am also an advocate of Structured Tables that was a feature introduced in Excel 2007, a very powerful tool that should be part of EVERY Excel users' toolbox.

Since that implies that there WILL be a heading in D1...
[tt]
=IF(A2<>A1,1,1+IF(ISNUMBER(D1),D1,0))
[/tt]

If this is a Structured Table, then your formula will AUTOMATICALLY 1) propagate through all rows of existing data in that column and 2) propagate to a NEW row as you add data to your table.

You can convert any proper table by 1) Selecting in the table, 2) Insert > Tables > Table > Create table. Once converted, selecting in the table will cause a context sensitive addition to the ribbon, TABLE TOOLS, with lots of design features. You can also choose to Convert to Range in the Tools Group, if you care not to use these features.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JVFriederick]

Just when I thought I knew a little bit, Skip teaches me more!

The created table is also automatically named (which can be changed to suit), the big payoff for me is that formulas will propagate as data is added (similar to db query object).

Thanks for taking the time to mention this.

JVF

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[albop]

oosh! That's just given me something further to investigate!

Thanks so much for the help