EXCEL FORMULA TO COUNT

L

LAY2022

New Member
Joined
Jun 19, 2024
Messages
8
Office Version
  1. 365
Hi, I need help to create an excel formula. I need to increment the reference column if the vendor and date changed.


ORIGINAL
RefNumberAPAccountVendorTxnDateLineAmount
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
RefNumberAPAccountVendorTxnDateLineAmount
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​

EXCEL HELP.xlsx
CDEFG
4ORIGINAL
5RefNumberAPAccountVendorTxnDateLineAmount
62000QGiv1/1/202139.48
7EFT-01/01/20212000QGiv1/1/202139.48
8EFT-01/01/20212000QGiv1/1/202139.48
9EFT-01/01/20212000QGiv1/1/202139.48
10EFT-01/01/20212000Republic Services #9241/1/2021302.16
11EFT-01/01/20212000Square1/1/2021149.47
12EFT-01/01/20212000Square1/1/202121.55
13EFT-01/01/20212000Square1/1/202199.22
14
15SHOULD BE THE OUTPUT
16RefNumberAPAccountVendorTxnDateLineAmount
17EFT-01/01/2021-12000QGiv1/1/202139.48
18EFT-01/01/2021-12000QGiv1/1/202139.48
19EFT-01/01/2021-12000QGiv1/1/202139.48
20EFT-01/01/2021-12000QGiv1/1/202139.48
21EFT-01/01/2021-22000Republic Services #9241/4/2021302.16
22EFT-01/01/2021-32000Square1/4/2021149.47
23EFT-01/01/2021-32000Square1/5/202121.55
24EFT-01/01/2021-32000Square1/6/202199.22
Sheet1
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
工作簿1.xlsx
ABCDEFGH
1
2
3
4ORIGINAL
5RefNumberAPAccountVendorTxnDateLineAmount
62000QGiv2021-1-139.48
7EFT-01/01/20212000QGiv2021-1-139.48
8EFT-01/01/20212000QGiv2021-1-139.48
9EFT-01/01/20212000QGiv2021-1-139.48
10EFT-01/01/20212000Republic Services #9242021-1-1302.16
11EFT-01/01/20212000Square2021-1-1149.47
12EFT-01/01/20212000Square2021-1-121.55
13EFT-01/01/20212000Square2021-1-199.22
14
15SHOULD BE THE OUTPUT
16RefNumberAPAccountVendorTxnDateLineAmount
17EFT-01/01/2021-12000QGiv2021-1-139.48
18EFT-01/01/2021-12000QGiv2021-1-139.48
19EFT-01/01/2021-12000QGiv2021-1-139.48
20EFT-01/01/2021-12000QGiv2021-1-139.48
21EFT-01/01/2021-22000Republic Services #9242021-1-1302.16
22EFT-01/01/2021-32000Square2021-1-1149.47
23EFT-01/01/2021-32000Square2021-1-121.55
24EFT-01/01/2021-32000Square2021-1-199.22
25
Sheet1
Cell Formulas
RangeFormula
C17:C24C17="EFT-01/01/2021-"&MATCH(E17&F17,UNIQUE(E$17:E17&F$17:F17),)
 
Upvote 0
Try this:

Book1
ACDEFG
1
2
3
4ORIGINAL
5RefNumberAPAccountVendorTxnDateLineAmount
62000QGiv1/1/202139.48
7EFT-01/01/20212000QGiv1/1/202139.48
8EFT-01/01/20212000QGiv1/1/202139.48
9EFT-01/01/20212000QGiv1/1/202139.48
10EFT-01/01/20212000Republic Services #9241/1/2021302.16
11EFT-01/01/20212000Square1/1/2021149.47
12EFT-01/01/20212000Square1/1/202121.55
13EFT-01/01/20212000Square1/1/202199.22
14
15SHOULD BE THE OUTPUT
16RefNumberAPAccountVendorTxnDateLineAmount
17EFT-01/01/2021-12000QGiv1/1/202139.48
18EFT-01/01/2021-12000QGiv1/1/202139.48
19EFT-01/01/2021-12000QGiv1/1/202139.48
20EFT-01/01/2021-12000QGiv1/1/202139.48
21EFT-01/01/2021-22000Republic Services #9241/1/2021302.16
22EFT-01/01/2021-32000Square1/1/2021149.47
23EFT-01/01/2021-42000Square2/1/202121.55
24EFT-01/01/2021-52000Square3/1/202199.22
Sheet1
Cell Formulas
RangeFormula
C17:C24C17="EFT-01/01/2021-"&ROWS(UNIQUE(E$17:F17))
 
Upvote 0
Phuoc said:
Try this:

Book1
ACDEFG
1
2
3
4ORIGINAL
5RefNumberAPAccountVendorTxnDateLineAmount
62000QGiv1/1/202139.48
7EFT-01/01/20212000QGiv1/1/202139.48
8EFT-01/01/20212000QGiv1/1/202139.48
9EFT-01/01/20212000QGiv1/1/202139.48
10EFT-01/01/20212000Republic Services #9241/1/2021302.16
11EFT-01/01/20212000Square1/1/2021149.47
12EFT-01/01/20212000Square1/1/202121.55
13EFT-01/01/20212000Square1/1/202199.22
14
15SHOULD BE THE OUTPUT
16RefNumberAPAccountVendorTxnDateLineAmount
17EFT-01/01/2021-12000QGiv1/1/202139.48
18EFT-01/01/2021-12000QGiv1/1/202139.48
19EFT-01/01/2021-12000QGiv1/1/202139.48
20EFT-01/01/2021-12000QGiv1/1/202139.48
21EFT-01/01/2021-22000Republic Services #9241/1/2021302.16
22EFT-01/01/2021-32000Square1/1/2021149.47
23EFT-01/01/2021-42000Square2/1/202121.55
24EFT-01/01/2021-52000Square3/1/202199.22
Sheet1
Cell Formulas
RangeFormula
C17:C24C17="EFT-01/01/2021-"&ROWS(UNIQUE(E$17:F17))
Click to expand...

Hi, thank you for your reply. The formula works but that is not what I wanted to achieved.

This should be the output. Vendor payments are made on the same date but it should the payment be group by vendor by date with unique reference. as shown below to create one payment reference.



SHOULD BE THE OUTPUT
16RefNumberAPAccountVendorTxnDateLineAmount
17EFT-01/01/2021-12000QGiv2021-1-139.48
18EFT-01/01/2021-12000QGiv2021-1-139.48
19EFT-01/01/2021-12000QGiv2021-1-139.48
20EFT-01/01/2021-12000QGiv2021-1-139.48
21EFT-01/01/2021-22000Republic Services #9242021-1-1302.16
22EFT-01/01/2021-32000Square2021-1-1149.47
23EFT-01/01/2021-32000Square2021-1-121.55
24EFT-01/01/2021-32000Square2021-1-199.22
25
 
Upvote 0
Phuoc said:
Try this:

Book1
ACDEFG
1
2
3
4ORIGINAL
5RefNumberAPAccountVendorTxnDateLineAmount
62000QGiv1/1/202139.48
7EFT-01/01/20212000QGiv1/1/202139.48
8EFT-01/01/20212000QGiv1/1/202139.48
9EFT-01/01/20212000QGiv1/1/202139.48
10EFT-01/01/20212000Republic Services #9241/1/2021302.16
11EFT-01/01/20212000Square1/1/2021149.47
12EFT-01/01/20212000Square1/1/202121.55
13EFT-01/01/20212000Square1/1/202199.22
14
15SHOULD BE THE OUTPUT
16RefNumberAPAccountVendorTxnDateLineAmount
17EFT-01/01/2021-12000QGiv1/1/202139.48
18EFT-01/01/2021-12000QGiv1/1/202139.48
19EFT-01/01/2021-12000QGiv1/1/202139.48
20EFT-01/01/2021-12000QGiv1/1/202139.48
21EFT-01/01/2021-22000Republic Services #9241/1/2021302.16
22EFT-01/01/2021-32000Square1/1/2021149.47
23EFT-01/01/2021-42000Square2/1/202121.55
24EFT-01/01/2021-52000Square3/1/202199.22
Sheet1
Cell Formulas
RangeFormula
C17:C24C17="EFT-01/01/2021-"&ROWS(UNIQUE(E$17:F17))
Click to expand...

Hi,
For every changes in the date, it should be back at 1 by vendor. With the example below, since the transaction date is 2021-1-1 and there are 3 vendor paid on that date, then the ref number is correct.


SHOULD BE THE OUTPUT
16RefNumberAPAccountVendorTxnDateLineAmount
17EFT-01/01/2021-12000QGiv2021-1-139.48
18EFT-01/01/2021-12000QGiv2021-1-139.48
19EFT-01/01/2021-12000QGiv2021-1-139.48
20EFT-01/01/2021-12000QGiv2021-1-139.48
21EFT-01/01/2021-22000Republic Services #9242021-1-1302.16
22EFT-01/01/2021-32000Square2021-1-1149.47
23EFT-01/01/2021-32000Square2021-1-121.55
24EFT-01/01/2021-32000Square2021-1-199.22
25
 
Upvote 0
Try change to:

Book1
ABCDEFG
1RefNumberAPAccountVendorTxnDateLineAmount
2EFT-01/01/2021-12000QGiv1/1/202139.48
3EFT-01/01/2021-12000QGiv1/1/202139.48
4EFT-01/01/2021-12000QGiv1/1/202139.48
5EFT-01/01/2021-12000QGiv1/1/202139.48
6EFT-01/01/2021-22000Republic Services #9241/1/2021302.16
7EFT-01/01/2021-32000Square1/1/2021149.47
8EFT-02/01/2021-12000Square2/1/202121.55
9EFT-03/01/2021-12000Square3/1/202199.22
Sheet1
Cell Formulas
RangeFormula
C2:C9C2="EFT-"&TEXT(F2,"dd/mm/yyyy-")&ROWS(UNIQUE(FILTER(E$2:F2,(F$2:F2=F2))))
 
Upvote 1
Solution
Phuoc said:
Try change to:

Book1
ABCDEFG
1RefNumberAPAccountVendorTxnDateLineAmount
2EFT-01/01/2021-12000QGiv1/1/202139.48
3EFT-01/01/2021-12000QGiv1/1/202139.48
4EFT-01/01/2021-12000QGiv1/1/202139.48
5EFT-01/01/2021-12000QGiv1/1/202139.48
6EFT-01/01/2021-22000Republic Services #9241/1/2021302.16
7EFT-01/01/2021-32000Square1/1/2021149.47
8EFT-02/01/2021-12000Square2/1/202121.55
9EFT-03/01/2021-12000Square3/1/202199.22
Sheet1
Cell Formulas
RangeFormula
C2:C9C2="EFT-"&TEXT(F2,"dd/mm/yyyy-")&ROWS(UNIQUE(FILTER(E$2:F2,(F$2:F2=F2))))
Click to expand...

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
EXCEL TO COUNT THE NUMBER OF OCCURENCE & INCREMENT THE REFERENCE NUMBER
Replies
2
Views
188
Fluff
Fluff
A
  • Question Question
FILTER Formula.
2
Replies
12
Views
326
arthurz11
A
skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
573
skyh3ck
skyh3ck
W
  • Solved
Unique with Sort but exluding rows
Replies
4
Views
359
Fluff
Fluff
R
  • Locked
  • Question Question
Excel formula - Overdue orders with dates
Replies
1
Views
667
RD1982
R

Forum statistics

Threads
1,224,990
Messages
6,182,219
Members
453,095
Latest member
SleepyAtlasHQ

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top