I have the data below where I want to identify duplicate journals. The fields "Account" , "Account Number" and "Amount" need to be same for a journal to be same. For e.g
Journal Number | Account | Account number | Amount |
1 | Revenue | 1000 | -100 |
1 | Debtors | 2000 | 120 |
1 | VAT | 3000 | -20 |
2 | Revenue | 1000 | -100 |
2 | Debtors | 2000 | 120 |
2 | VAT | 3000 | -20 |
3 | COS | 1500 | 50 |
3 | Creditors | 1600 | -50 |
4 | Revenue | 1000 | -100 |
4 | Debtors | 2000 | 120 |
4 | VAT | 3000 | -20 |
5 | Revenue | 1000 | -100 |
5 | Debtors | 2000 | 120 |
5 | VAT | 3000 | -20 |
6 | COS | 1500 | 50 |
6 | Creditors | 1600 | -50 |
7 | Cash | 1000 | 120 |
7 | Debtors | 2000 | 120 |
8 | Revenue | 1000 | -60 |
8 | Debtors | 2000 | 80 |
8 | VAT | 3000 | -20 |
I want the result to look like below
Journal Number | Account | Account number | Amount | Number of occurrences |
1 | Revenue | 1000 | -100 | 4 |
1 | Debtors | 2000 | 120 | 4 |
1 | VAT | 3000 | -20 | 4 |
2 | Revenue | 1000 | -100 | 4 |
2 | Debtors | 2000 | 120 | 4 |
2 | VAT | 3000 | -20 | 4 |
3 | COS | 1500 | 50 | 2 |
3 | Creditors | 1600 | -50 | 2 |
4 | Revenue | 1000 | -100 | 4 |
4 | Debtors | 2000 | 120 | 4 |
4 | VAT | 3000 | -20 | 4 |
5 | Revenue | 1000 | -100 | 4 |
5 | Debtors | 2000 | 120 | 4 |
5 | VAT | 3000 | -20 | 4 |
6 | COS | 1500 | 50 | 2 |
6 | Creditors | 1600 | -50 | 2 |
7 | Cash | 1000 | 120 | 1 |
7 | Debtors | 2000 | 120 | 1 |
8 | Revenue | 1000 | -60 | 1 |
8 | Debtors | 2000 | 80 | 1 |
8 | VAT | 3000 | -20 | 1 |
Solved! Go to Solution.
Hi @Data_Alter ,
This is relatively straightforward but I think your numbers are incorrect. As you can see in the image below of your data, you have this combination as 4 when I can see 5 combinations:
The workflow is as follows:
The workflow is attached.
I hope this helps.
M.
Thanks looks great 👍🏻👍🏻👍🏻😊
Thank you for the accept mark!