Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating sequential count of transactions by Client ID in Excel 2

Status
Not open for further replies.

albop

MIS
Nov 12, 2013
6
GB
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
 
Thank you so much - I've been looking at it for ages and driven myself mental!! :)
 
Of course to make the formula from JV work, the data needs to be sorted by client first.
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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


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

Thanks so much for the help
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top