EXCEL TO COUNT THE NUMBER OF OCCURENCE & INCREMENT THE REFERENCE NUMBER

[LAY2022]

HI Master,

I am new here and I need an Excel formula to increment the reference number only if the vendor column and date is changed.
Please help.


Thanks,
Lay

ORIGINAL
RefNumber	APAccount	Vendor	TxnDate	LineAmount
EFT-01/01/2021	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021	2000​	Republic Services #924	1/1/2021​	302.16​
EFT-01/01/2021	2000​	Square	1/1/2021​	149.47​
EFT-01/01/2021	2000​	Square	1/1/2021​	21.55​
EFT-01/01/2021	2000​	Square	1/1/2021​	99.22​
SHOULD BE THE OUTPUT
RefNumber	APAccount	Vendor	TxnDate	LineAmount
EFT-01/01/2021-1	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021-1	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021-1	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021-1	2000​	QGiv	1/1/2021​	39.48​
EFT-01/01/2021-2	2000​	Republic Services #924	1/4/2021​	302.16​
EFT-01/01/2021-3	2000​	Square	1/4/2021​	149.47​
EFT-01/01/2021-3	2000​	Square	1/5/2021​	21.55​
EFT-01/01/2021-3	2000​	Square	1/6/2021​	99.22​

 

[myall_blues]

According to your rules

increment the reference number only if the vendor column and date is changed.

then the second line here should not have changed as the date is the same. Is that just a mistake or should the rule be different?

EFT-01/01/2021-2	2000​	Republic Services #924	1/4/2021​	302.16​
EFT-01/01/2021-3	2000​	Square	1/4/2021​	149.47​

 

[Fluff]

Duplicate to: EXCEL FORMULA TO COUNT

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.