Need a Excel formula to find out Knock off values based oncriteria and it isdocument date/text should be same, one +ve no. knock with only one -ve no.

Bhavana95

New Member
Joined
Mar 19, 2023
Messages
15
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
Document DateAmount in local currencyRemarks
30-09-20221,62,00,000.00
28-09-2022-1,00,00,000.00
28-09-2022-91,60,835.00
28-09-2022-87,14,108.00
28-09-2022-73,44,769.00
31-10-20229,89,477.00
11-03-20227,14,839.00
09-03-20234,41,274.00
30-04-20221,45,778.80
31-10-20221,14,518.00
31-10-202277,580.00
30-04-202271,280.00
24-06-202258,500.32Knock off
30-06-202258,500.32
24-06-2022-58,500.32Knock off
29-05-202245,864.00
30-06-202238,456.00
24-06-202236,549.56
30-06-202236,549.56
24-06-2022-36,549.56
29-05-202228,216.00
31-10-202227,157.00
31-10-202224,150.00
30-06-202223,012.00
31-10-202218,748.00
09-03-202315,312.00
24-01-202312,968.00
29-07-202212,352.00
31-10-20228,920.00
30-03-20238,278.00
30-08-20227,994.00
30-01-20237,930.00
29-07-20227,448.00
24-01-20236,070.00
30-01-20235,990.00
31-10-20225,963.00
30-04-20225,284.00
09-03-20235,100.00
24-01-20234,034.00
24-01-20233,838.00
30-04-20223,680.00
30-08-20223,680.00
30-01-20233,680.00
31-10-20223,615.00
31-10-20223,598.00
31-10-20223,350.00
30-04-20223,240.00
30-03-20232,932.00
24-06-20222,898.00
30-06-20222,898.00
24-06-2022-2,898.00
29-05-20222,880.00
30-01-20232,750.00
29-07-20222,566.00
24-01-20232,296.00
24-06-20222,257.00
30-06-20222,257.00
24-06-2022-2,257.00
30-01-20232,250.00
28-04-20221,850.00
30-04-2022-1,850.00
30-01-20231,850.00
31-01-2023-1,850.00
30-01-20231,760.00
31-01-2023-1,760.00
30-01-20231,736.00
30-01-20231,486.00
30-09-20221,434.00
31-10-20221,420.00
30-01-20231,350.00
31-01-2023-1,350.00
27-02-20231,350.00
28-02-2023-1,350.00
30-03-20231,350.00
31-03-2023-1,350.00
31-10-20221,310.00
28-07-20221,300.00
31-07-2022-1,300.00
29-11-20221,300.00
30-11-2022-1,300.00
29-12-20221,300.00
31-12-2022-1,300.00
09-03-20231,260.00
29-06-20221,250.00
30-06-2022-1,250.00
01-07-20221,250.00
01-07-2022-1,250.00
31-10-20221,231.00
31-10-2022-1,150.00
29-08-20221,100.00
31-08-2022-1,100.00
30-01-20231,090.00
31-01-2023-1,090.00
30-01-20231,040.00
31-01-2023-1,040.00
30-03-20231,032.00
30-01-20231,006.00
30-08-20221,000.00
24-01-2023990.00
30-08-2022958.00
31-05-2022-950.00
31-05-2022950.00
29-06-2022933.00
30-01-2023920.00
24-01-2023906.00
30-09-2022-900.00
29-09-2022900.00
30-10-2022900.00
31-10-2022890.00
31-10-2022890.00
30-01-2023886.00
31-01-2023-886.00
31-01-2023-860.00
30-09-2022-850.00
30-01-2023800.00
31-01-2023-800.00
30-01-2023776.00
30-01-2023760.00
31-01-2023-760.00
29-07-2022658.00
28-04-2022600.00
28-07-2022600.00
29-08-2022600.00
31-05-2022600.00
29-09-2022600.00
31-10-2022-600.00
29-11-2022600.00
29-12-2022600.00
30-10-2022600.00
29-12-2022550.00
31-12-2022-550.00
27-02-2023550.00
28-02-2023-550.00
30-03-2023550.00
31-03-2023-550.00
30-01-2023520.00
30-01-2023520.00
31-01-2023-520.00
31-01-2023-520.00
29-06-2022510.00
29-09-2022500.00
29-11-2022500.00
30-11-2022-500.00
30-10-2022500.00
27-02-2023500.00
30-03-2023500.00
30-01-2023494.00
29-06-2022446.00
29-05-2022422.00
30-01-2023402.00
29-08-2022400.00
31-08-2022-400.00
30-01-2023400.00
31-01-2023-400.00
09-03-2023398.00
30-01-2023372.00
30-01-2023370.00
30-01-2023362.00
09-03-2023360.00
29-08-2022350.00
29-09-2022350.00
30-10-2022350.00
30-09-2022342.00
30-01-2023335.00
29-06-2022331.00
29-05-2022326.00
30-04-2022326.00
29-08-2022250.00
29-09-2022250.00
30-10-2022250.00
30-01-2023250.00
31-01-2023-250.00
30-01-2023240.00
30-01-2023240.00
31-01-2023-240.00
31-01-2023-240.00
30-01-2023206.00
29-06-2022201.00
28-04-2022200.00
28-07-2022200.00
31-05-2022200.00
29-09-2022200.00
30-01-2023200.00
30-01-2023200.00
30-01-2023200.00
31-01-2023-200.00
27-02-2023200.00
30-03-2023200.00
30-01-2023196.00
31-01-2023-196.00
30-01-2023192.00
30-01-2023190.00
31-01-2023-190.00
30-01-2023180.00
29-06-2022172.00
30-01-2023152.00
28-04-2022150.00
28-04-2022150.00
28-07-2022150.00
28-07-2022150.00
30-09-2022150.00
31-05-2022150.00
31-05-2022150.00
29-11-2022150.00
29-11-2022150.00
29-12-2022150.00
29-12-2022150.00
30-10-2022150.00
30-04-2022146.00
27-02-2023136.00
29-06-2022122.00
29-06-2022122.00
30-01-2023120.00
30-01-2023120.00
30-01-2023120.00
31-01-2023-120.00
31-01-2023-120.00
30-01-2023117.00
30-01-2023112.00
28-04-2022100.00
28-04-2022100.00
28-07-2022100.00
28-07-2022100.00
29-08-2022100.00
31-05-2022100.00
31-05-2022100.00
31-05-2022100.00
29-09-2022100.00
29-11-2022100.00
29-11-2022100.00
29-12-2022100.00
29-12-2022100.00
30-10-2022100.00
27-02-2023100.00
27-02-2023100.00
30-03-2023100.00
30-03-2023100.00
30-03-2023100.00
30-01-202395.00
09-03-202390.00
30-03-2023-90.00
29-06-202287.00
30-01-202386.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
31-01-2023-80.00
31-01-2023-80.00
30-01-202376.00
31-01-2023-76.00
30-09-2022-75.00
30-08-202275.00
29-06-202272.00
29-06-202272.00
29-06-202272.00
30-01-202355.00
31-01-2023-55.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
30-04-2022-50.00
30-04-2022-50.00
31-05-2022-50.00
31-05-2022-50.00
29-06-202250.00
29-06-202250.00
29-06-202250.00
30-06-2022-50.00
30-06-2022-50.00
01-07-202250.00
01-07-202250.00
01-07-2022-50.00
01-07-2022-50.00
28-07-202250.00
28-07-202250.00
28-07-202250.00
28-07-202250.00
28-07-202250.00
31-07-2022-50.00
31-07-2022-50.00
29-08-202250.00
29-08-202250.00
29-08-202250.00
29-08-202250.00
29-08-202250.00
31-08-2022-50.00
31-08-2022-50.00
30-09-2022-50.00
30-09-2022-50.00
31-05-202250.00
31-05-202250.00
31-05-202250.00
31-05-202250.00
31-05-202250.00
29-09-202250.00
29-09-202250.00
29-09-202250.00
29-09-202250.00
29-09-202250.00
31-10-2022-50.00
31-10-2022-50.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
30-11-2022-50.00
30-11-2022-50.00
30-11-202250.00
30-11-202250.00
29-12-202250.00
29-12-202250.00
29-12-202250.00
29-12-202250.00
29-12-202250.00
31-12-2022-50.00
31-12-2022-50.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-11-2022-50.00
30-11-2022-50.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
28-02-2023-50.00
28-02-2023-50.00
28-02-2023-50.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
31-03-2023-50.00
31-03-2023-50.00
31-03-2023-50.00
30-01-202345.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-2023-40.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-202340.00
27-02-202340.00
28-02-2023-40.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
30-06-2022-36.00
30-06-2022-36.00
01-07-202236.00
01-07-202236.00
01-07-2022-36.00
01-07-2022-36.00
30-01-202336.00
30-01-2023-20.00
29-06-202215.00
30-09-202214.00
31-10-20225.00
31-10-20225.00
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Document DateAmount in local currencyRemarks
30-09-20221,62,00,000.00
28-09-2022-1,00,00,000.00
28-09-2022-91,60,835.00
28-09-2022-87,14,108.00
28-09-2022-73,44,769.00
31-10-20229,89,477.00
11-03-20227,14,839.00
09-03-20234,41,274.00
30-04-20221,45,778.80
31-10-20221,14,518.00
31-10-202277,580.00
30-04-202271,280.00
24-06-202258,500.32Knock off
30-06-202258,500.32
24-06-2022-58,500.32Knock off
29-05-202245,864.00
30-06-202238,456.00
24-06-202236,549.56
30-06-202236,549.56
24-06-2022-36,549.56
29-05-202228,216.00
31-10-202227,157.00
31-10-202224,150.00
30-06-202223,012.00
31-10-202218,748.00
09-03-202315,312.00
24-01-202312,968.00
29-07-202212,352.00
31-10-20228,920.00
30-03-20238,278.00
30-08-20227,994.00
30-01-20237,930.00
29-07-20227,448.00
24-01-20236,070.00
30-01-20235,990.00
31-10-20225,963.00
30-04-20225,284.00
09-03-20235,100.00
24-01-20234,034.00
24-01-20233,838.00
30-04-20223,680.00
30-08-20223,680.00
30-01-20233,680.00
31-10-20223,615.00
31-10-20223,598.00
31-10-20223,350.00
30-04-20223,240.00
30-03-20232,932.00
24-06-20222,898.00
30-06-20222,898.00
24-06-2022-2,898.00
29-05-20222,880.00
30-01-20232,750.00
29-07-20222,566.00
24-01-20232,296.00
24-06-20222,257.00
30-06-20222,257.00
24-06-2022-2,257.00
30-01-20232,250.00
28-04-20221,850.00
30-04-2022-1,850.00
30-01-20231,850.00
31-01-2023-1,850.00
30-01-20231,760.00
31-01-2023-1,760.00
30-01-20231,736.00
30-01-20231,486.00
30-09-20221,434.00
31-10-20221,420.00
30-01-20231,350.00
31-01-2023-1,350.00
27-02-20231,350.00
28-02-2023-1,350.00
30-03-20231,350.00
31-03-2023-1,350.00
31-10-20221,310.00
28-07-20221,300.00
31-07-2022-1,300.00
29-11-20221,300.00
30-11-2022-1,300.00
29-12-20221,300.00
31-12-2022-1,300.00
09-03-20231,260.00
29-06-20221,250.00
30-06-2022-1,250.00
01-07-20221,250.00
01-07-2022-1,250.00
31-10-20221,231.00
31-10-2022-1,150.00
29-08-20221,100.00
31-08-2022-1,100.00
30-01-20231,090.00
31-01-2023-1,090.00
30-01-20231,040.00
31-01-2023-1,040.00
30-03-20231,032.00
30-01-20231,006.00
30-08-20221,000.00
24-01-2023990.00
30-08-2022958.00
31-05-2022-950.00
31-05-2022950.00
29-06-2022933.00
30-01-2023920.00
24-01-2023906.00
30-09-2022-900.00
29-09-2022900.00
30-10-2022900.00
31-10-2022890.00
31-10-2022890.00
30-01-2023886.00
31-01-2023-886.00
31-01-2023-860.00
30-09-2022-850.00
30-01-2023800.00
31-01-2023-800.00
30-01-2023776.00
30-01-2023760.00
31-01-2023-760.00
29-07-2022658.00
28-04-2022600.00
28-07-2022600.00
29-08-2022600.00
31-05-2022600.00
29-09-2022600.00
31-10-2022-600.00
29-11-2022600.00
29-12-2022600.00
30-10-2022600.00
29-12-2022550.00
31-12-2022-550.00
27-02-2023550.00
28-02-2023-550.00
30-03-2023550.00
31-03-2023-550.00
30-01-2023520.00
30-01-2023520.00
31-01-2023-520.00
31-01-2023-520.00
29-06-2022510.00
29-09-2022500.00
29-11-2022500.00
30-11-2022-500.00
30-10-2022500.00
27-02-2023500.00
30-03-2023500.00
30-01-2023494.00
29-06-2022446.00
29-05-2022422.00
30-01-2023402.00
29-08-2022400.00
31-08-2022-400.00
30-01-2023400.00
31-01-2023-400.00
09-03-2023398.00
30-01-2023372.00
30-01-2023370.00
30-01-2023362.00
09-03-2023360.00
29-08-2022350.00
29-09-2022350.00
30-10-2022350.00
30-09-2022342.00
30-01-2023335.00
29-06-2022331.00
29-05-2022326.00
30-04-2022326.00
29-08-2022250.00
29-09-2022250.00
30-10-2022250.00
30-01-2023250.00
31-01-2023-250.00
30-01-2023240.00
30-01-2023240.00
31-01-2023-240.00
31-01-2023-240.00
30-01-2023206.00
29-06-2022201.00
28-04-2022200.00
28-07-2022200.00
31-05-2022200.00
29-09-2022200.00
30-01-2023200.00
30-01-2023200.00
30-01-2023200.00
31-01-2023-200.00
27-02-2023200.00
30-03-2023200.00
30-01-2023196.00
31-01-2023-196.00
30-01-2023192.00
30-01-2023190.00
31-01-2023-190.00
30-01-2023180.00
29-06-2022172.00
30-01-2023152.00
28-04-2022150.00
28-04-2022150.00
28-07-2022150.00
28-07-2022150.00
30-09-2022150.00
31-05-2022150.00
31-05-2022150.00
29-11-2022150.00
29-11-2022150.00
29-12-2022150.00
29-12-2022150.00
30-10-2022150.00
30-04-2022146.00
27-02-2023136.00
29-06-2022122.00
29-06-2022122.00
30-01-2023120.00
30-01-2023120.00
30-01-2023120.00
31-01-2023-120.00
31-01-2023-120.00
30-01-2023117.00
30-01-2023112.00
28-04-2022100.00
28-04-2022100.00
28-07-2022100.00
28-07-2022100.00
29-08-2022100.00
31-05-2022100.00
31-05-2022100.00
31-05-2022100.00
29-09-2022100.00
29-11-2022100.00
29-11-2022100.00
29-12-2022100.00
29-12-2022100.00
30-10-2022100.00
27-02-2023100.00
27-02-2023100.00
30-03-2023100.00
30-03-2023100.00
30-03-2023100.00
30-01-202395.00
09-03-202390.00
30-03-2023-90.00
29-06-202287.00
30-01-202386.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
30-01-202380.00
31-01-2023-80.00
31-01-2023-80.00
30-01-202376.00
31-01-2023-76.00
30-09-2022-75.00
30-08-202275.00
29-06-202272.00
29-06-202272.00
29-06-202272.00
30-01-202355.00
31-01-2023-55.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
28-04-202250.00
30-04-2022-50.00
30-04-2022-50.00
31-05-2022-50.00
31-05-2022-50.00
29-06-202250.00
29-06-202250.00
29-06-202250.00
30-06-2022-50.00
30-06-2022-50.00
01-07-202250.00
01-07-202250.00
01-07-2022-50.00
01-07-2022-50.00
28-07-202250.00
28-07-202250.00
28-07-202250.00
28-07-202250.00
28-07-202250.00
31-07-2022-50.00
31-07-2022-50.00
29-08-202250.00
29-08-202250.00
29-08-202250.00
29-08-202250.00
29-08-202250.00
31-08-2022-50.00
31-08-2022-50.00
30-09-2022-50.00
30-09-2022-50.00
31-05-202250.00
31-05-202250.00
31-05-202250.00
31-05-202250.00
31-05-202250.00
29-09-202250.00
29-09-202250.00
29-09-202250.00
29-09-202250.00
29-09-202250.00
31-10-2022-50.00
31-10-2022-50.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
29-11-202250.00
30-11-2022-50.00
30-11-2022-50.00
30-11-202250.00
30-11-202250.00
29-12-202250.00
29-12-202250.00
29-12-202250.00
29-12-202250.00
29-12-202250.00
31-12-2022-50.00
31-12-2022-50.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-10-202250.00
30-11-2022-50.00
30-11-2022-50.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
27-02-202350.00
28-02-2023-50.00
28-02-2023-50.00
28-02-2023-50.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
30-03-202350.00
31-03-2023-50.00
31-03-2023-50.00
31-03-2023-50.00
30-01-202345.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-2023-40.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
30-01-202340.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-2023-40.00
31-01-202340.00
27-02-202340.00
28-02-2023-40.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
29-06-202236.00
30-06-2022-36.00
30-06-2022-36.00
01-07-202236.00
01-07-202236.00
01-07-2022-36.00
01-07-2022-36.00
30-01-202336.00
30-01-2023-20.00
29-06-202215.00
30-09-202214.00
31-10-20225.00
31-10-20225.00
Can any one help me on above request
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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