Hi,
currently, I have a sheet of data that contains thousands of records, I would like to match each record and provide the summarized results.
the criteria will be
if the batch date = payment date then it should count the no. of records as Current Dated
if the batch date > payment date then it should count the no. of records as Back Dated
If the batch date < payment date then it should count the no. of records as Post Dated
A sample sheet of data is attached here for reference.
Im currently using the if formula, howe we can achieve the same result in VBA, as the no. of records are voluminous using formula is making the excel slow and increasing its file size.
thanks
Aleem
currently, I have a sheet of data that contains thousands of records, I would like to match each record and provide the summarized results.
the criteria will be
if the batch date = payment date then it should count the no. of records as Current Dated
if the batch date > payment date then it should count the no. of records as Back Dated
If the batch date < payment date then it should count the no. of records as Post Dated
A sample sheet of data is attached here for reference.
Im currently using the if formula, howe we can achieve the same result in VBA, as the no. of records are voluminous using formula is making the excel slow and increasing its file size.
thanks
Aleem
Book2 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Payment ID | Match Doc Ty | Payment/ Item | Payee Number | Bank Acct-G/L | Check/ Itm Date | Void Date | Batch Number | Bth Ty | Batch Date | Payment Amount | Status | ||
2 | 1 | PN | 52667107 | 1278624 | 02539003 | 16/05/2021 | 52667073 | M | 16/05/2021 | (382,784.14) | Current Dated | |||
3 | 2 | PN | 52667118 | 1261005 | 02539003 | 15/05/2021 | 52667074 | M | 16/05/2021 | (516,758.59) | Back Dated | |||
4 | 3 | PN | 52667129 | 1276374 | 02539003 | 16/05/2021 | 52667075 | M | 16/05/2021 | (697,624.10) | Current Dated | |||
5 | 4 | PN | 52667140 | 1248451 | 00122098 | 16/05/2021 | 52667076 | M | 16/05/2021 | (941,792.53) | Current Dated | |||
6 | 5 | PN | 52667151 | 1276283 | 00122098 | 16/05/2021 | 52667077 | M | 16/05/2021 | (1,271,419.92) | Current Dated | |||
7 | 6 | PN | 52667162 | 1259758 | 00122098 | 31/05/2021 | 52667078 | M | 16/05/2021 | (1,716,416.89) | Future Dated | |||
8 | 7 | PN | 52667173 | 1221375 | 00122098 | 18/05/2021 | 52667079 | M | 16/05/2021 | (2,317,162.81) | Future Dated | |||
9 | 8 | PN | 52667184 | 106907 | 00122098 | 16/05/2021 | 52667080 | M | 16/05/2021 | (3,128,169.79) | Current Dated | |||
10 | 9 | PN | 52667195 | 1269342 | 00122098 | 19/05/2021 | 52667081 | M | 16/05/2021 | (4,223,029.22) | Future Dated | |||
11 | 10 | PN | 52667206 | 1271214 | 00122098 | 20/05/2021 | 52667082 | M | 16/05/2021 | (5,701,089.44) | Future Dated | |||
12 | ||||||||||||||
13 | ||||||||||||||
14 | Results should be | |||||||||||||
15 | Current Dated Records | 5 | which is = 16/05/2021 | |||||||||||
16 | Back Dated | 1 | which is < 16/05/2021 | |||||||||||
17 | Future Dated | 4 | > 16/05/2021 | |||||||||||
18 | Formula used | IF(J2=F2,"Current Dated",IF(J2>F2,"Back Dated","Future Dated")) | ||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L2:L11 | L2 | =IF(J2=F2,"Current Dated",IF(J2>F2,"Back Dated","Future Dated")) |