Notifications
Clear all
General Excel Questions & Answers
2
Posts
2
Users
0
Reactions
40
Views
Topic starter
I have 2 columns in a table. I need to create the 3rd column based on for each item in column A how many times the item in column B occurs. If it's the first occurrence, it should be AG1, if it's the second it should be AG2 and so on. (The list is ordered by date.) When the item in column A changes, the rank should be reset.
I would like to see something like this. I'm interested in any Excel, Power Query or Power Pivot based solution which gets the job done. Thank you for any clues!
| xxxx1 | AA | AA1 |
| xxxx1 | AB | AB1 |
| xxxx1 | AC | AC1 |
| xxxx1 | AD | AD1 |
| xxxx1 | AD | AD2 |
| xxxx1 | AE | AE1 |
| xxxx1 | AF | AF1 |
| xxxx1 | AG | AG1 |
| xxxx1 | AG | AG2 |
| xxxx1 | AG | AG3 |
| xxxx1 | AH | AH1 |
| xxxx2 | AA | AA1 |
| xxxx2 | AB | AB1 |
| xxxx2 | AC | AC1 |
| xxxx2 | AD | AD1 |
| xxxx2 | AF | AF1 |
| xxxx3 | AA | AA1 |
| xxxx3 | AB | AB1 |
| xxxx3 | AC | AC1 |
| xxxx3 | AD | AD1 |
| xxxx3 | AE | AE1 |
| xxxx3 | AF | AF1 |
| xxxx3 | AF | AF2 |
| xxxx3 | AG | AG1 |
| xxxx3 | AG | AG2 |
| xxxx3 | AH | AH1 |
| xxxx3 | AI | AI1 |
Posted : 01/11/2017 9:55 am
Hi Rita,
A simple excel formula will do the trick:
=B2&COUNTIFS($A$2:A2,A2,$B$2:B2,B2)
(assuming that data starts from row 2 and in columns A and B you have the data, adjust if necessary)
Posted : 01/11/2017 10:17 am
