How to Assign a Number to Unique Values in a Table

[jwiseAHS]

I would like to put a formula in the Acct# column that assigns a number to each unique value in the Account Column for each Transaction (Trans#). I've seen plenty of explanations of how to count the number of unique values, but nothing so far that assigns each unique value a number. I've provided sample output below for the Acct# column.


Allocation#.......Trans#....Account.....Acct#
001 ................001 ........Water.........1
002 ................001 ........Water.........1
003 ................002 ........Telephone...1
004 ................002 ........Travel.........2
005 ................003 ........Supplies......1
006 ................003 ........Training......2
007 ................003 ........Supplies......1

Thank You and Sorry if this has already been covered,
Jeff

 

[Joe4]

Welcome to the Board!

I think you are going to need to explain what exactly how you are defining "unique". I am not understanding the logic regarding what should get a "1" and what should get a "2" in your Acct# column.

 

[jwiseAHS]

Welcome to the Board!

I think you are going to need to explain what exactly how you are defining "unique". I am not understanding the logic regarding what should get a "1" and what should get a "2" in your Acct# column.

Thanks for the welcome.

I want to count the number of different accounts used per transaction (easy). But, then I want each of those accounts to receive a number (starting at 1).

The first transaction has two allocations, but only uses 1 account. So, it counts 1 account used, and since all accounts used are the first account, each one gets a "1".
The second transaction has two allocations, and uses 2 accounts (Telephone and Travel). The first (Telephone) numbered "1" and the second is numbered "2"
The third transaction has three allocations, but uses 2 accounts (Supplies and Training). The first (Supplies) is numbered "1", the second (Training) is numbered two. And the 3rd, since it is Supplies again, is numbered "1".

Hopefully that clears things up, and thanks again for the help.

 

[MarcelBeug]

Array formula in D2, confirm with Ctrl+Shift+Enter and copy down:

=IFERROR(INDEX(D$1:D1,MATCH($B2&$C2,$B$1:$B1&$C$1:$C1,0)),IFERROR(1+LARGE(IF(B$1:B1=$B2,D$1:D1),1),1))

 

[jwiseAHS]

Array formula in D2, confirm with Ctrl+Shift+Enter and copy down:

=IFERROR(INDEX(D$1:D1,MATCH($B2&$C2,$B$1:$B1&$C$1:$C1,0)),IFERROR(1+LARGE(IF(B$1:B1=$B2,D$1:D1),1),1))

Thank You Marcel and Joe,

This works fantastically.

 

[MarcelBeug]

You're welcome.