4 Replies Latest reply: Mar 22, 2013 11:06 AM by Nitesh Srivastava

# Transaction No. in a Table

Dear All,

Need a small help... I have a table as Below

 Date ACCNo. 3/22/2013 123 3/22/2013 1 3/22/2013 2 3/23/2013 123 3/23/2013 125 3/23/2013 127 3/23/2013 1 3/23/2013 126 3/25/2013 123 3/25/2013 124 3/25/2013 2 3/25/2013 3 3/30/2013 3 3/30/2013 124

I want the output as below...

 Create Date Mobile No. Transaction# 3/22/2013 123 1 3/22/2013 1 1 3/22/2013 2 1 3/23/2013 123 2 3/23/2013 125 1 3/23/2013 127 1 3/23/2013 1 2 3/23/2013 126 1 3/25/2013 123 3 3/25/2013 124 1 3/25/2013 2 2 3/25/2013 3 1 3/30/2013 3 2 3/30/2013 124 2

I can get this Information whith the help of below formula in excel....

=COUNTIF(\$B\$2:B3,B3)

Regards,

Nitesh Srivastava

• ###### Re: Transaction No. in a Table

Can you explain the logic to get the transaction#. sorry i am not good in excel functions

• ###### Re: Transaction No. in a Table

Hi Shushil,

Thanks for the response...

I would like to explain the requirement...

Lets assume I have a Table...

 Customer No. 111 112 113 111

Now my requirement in this table '111' is repeated twice in table...

So when 111 come first in the table, Transaction# will be 1, second time it will be 2, thrird time it will be 3 so on and so forth...

 Customer No. Transaction# 111 1 112 1 113 1 111 2

This logic should apply on all the values in 'Customer No.'

 Customer No. Transaction# 111 1 112 1 113 1 111 2 111 3 112 2
• ###### Re: Transaction No. in a Table

Hi,

First order the table based on the customer number and use Previous function to achieve that.

Temp:

Date,

CustomerNo

From SourceTable

Order by CustomerNo;

Transactions:

Date,

CustomerNo,

If(Previous(CustomerNo)=CustomerNo, Peek('Transaction#') + 1, 1) As Transaction#

From Temp;

Drop Table Temp;

Hope it helps

Celambarasan

• ###### Re: Transaction No. in a Table

Thanks alot... it really helped..

Cheers!!!!