RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello experts
I am trying to write a formula in column B to enter the same date in “Tally” as in "Portal". The common values are the amount and approximate date. If the date and amount are a match, in column B, I want to have the date in “Tally” as per "Portal" if the above condition are right. I can change it manually one by one but as the data is huge a formula would be very helpful and will save a lot of time.
I have already entered a formula in column B but it is not working correctly.
I am trying to write a formula in column B to enter the same date in “Tally” as in "Portal". The common values are the amount and approximate date. If the date and amount are a match, in column B, I want to have the date in “Tally” as per "Portal" if the above condition are right. I can change it manually one by one but as the data is huge a formula would be very helpful and will save a lot of time.
I have already entered a formula in column B but it is not working correctly.
Match Invoice No. Query.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | As per | Common Number | Invoice number | Date | Amount | Remarks | ||
2 | PORTAL | KR-POS-3564 | KR-POS-3564 | 16-01-2021 | 1014.00 | Correct | ||
3 | TALLY | KR-POS-3564 | Jan-64 | 16-01-2021 | 1014.00 | Correct | ||
4 | PORTAL | 2560/20-21 | 2560/20-21 | 13-03-2021 | 1025.00 | |||
5 | TALLY | KR-POS-3564 | 2560 | 13-03-2021 | 1025.00 | Error | ||
6 | PORTAL | HS/20-21/669 | HS/20-21/669 | 20-01-2021 | 1033.00 | |||
7 | TALLY | KR-POS-3564 | Jan-669 | 20-01-2021 | 1033.00 | Error | ||
8 | PORTAL | KR-POS-2035 | KR-POS-2035 | 10-10-2020 | 1040.00 | |||
9 | TALLY | KR-POS-3564 | 2035 | 15-10-2020 | 1040.00 | Error | ||
10 | PORTAL | 62 | 62 | 14-06-2020 | 1043.00 | |||
11 | PORTAL | M200831001363574 | M200831001363574 | 31-08-2020 | 1044.30 | |||
12 | PORTAL | 123/GST | 123/GST | 10-07-2020 | 1050.00 | |||
13 | TALLY | KR-POS-3564 | xx | 10-07-2020 | 1050.00 | Error | ||
14 | PORTAL | 0641/20-21 | 0641/20-21 | 13-10-2020 | 1052.00 | |||
15 | TALLY | KR-POS-3564 | 641 | 13-10-2020 | 1052.00 | Error | ||
16 | PORTAL | KR-POS-540 | KR-POS-540 | 17-06-2020 | 1053.00 | |||
17 | PORTAL | FT2129I005276327 | FT2129I005276327 | 06-02-2021 | 1060.82 | |||
18 | PORTAL | KR-POS-2183 | KR-POS-2183 | 20-10-2020 | 1064.00 | |||
19 | PORTAL | IN-QWN9-17483 | IN-QWN9-17483 | 03-09-2020 | 1070.00 | |||
20 | PORTAL | KA-5522-20-21 | KA-5522-20-21 | 16-12-2020 | 1071.00 | |||
21 | PORTAL | KA-6051-20-21 | KA-6051-20-21 | 29-12-2020 | 1071.00 | |||
22 | TALLY | KR-POS-3564 | Dec-22 | 16-12-2020 | 1071.00 | Error | ||
23 | TALLY | KR-POS-3564 | Dec-51 | 18-12-2020 | 1071.00 | Error | ||
24 | PORTAL | KR-POS-914 | KR-POS-914 | 25-07-2020 | 1074.00 | |||
25 | TALLY | KR-POS-3564 | 914 | 25-07-2020 | 1074.00 | Error | ||
26 | PORTAL | 1477/20-21 | 1477/20-21 | 21-12-2020 | 1100.00 | |||
27 | TALLY | KR-POS-3564 | Dec-1477 | 21-12-2020 | 1100.00 | Error | ||
28 | PORTAL | 0227/20-21 | 0227/20-21 | 25-08-2020 | 1131.00 | |||
29 | TALLY | KR-POS-3564 | 414 | 25-08-2020 | 1131.00 | Error | ||
30 | PORTAL | KR-POS-4508 | KR-POS-4508 | 16-03-2021 | 1138.00 | |||
31 | TALLY | KR-POS-3564 | KR-POS-4508 | 16-03-2021 | 1138.00 | Error | ||
32 | PORTAL | HS/20-21/195 | HS/20-21/195 | 18-08-2020 | 1147.00 | |||
33 | TALLY | KR-POS-3564 | 379 | 18-08-2020 | 1147.00 | Error | ||
34 | PORTAL | 1795/20-21 | 1795/20-21 | 16-01-2021 | 1173.00 | |||
35 | PORTAL | 2509/20-21 | 2509/20-21 | 10-03-2021 | 1173.00 | |||
36 | TALLY | KR-POS-3564 | Jan-1795 | 16-01-2021 | 1173.00 | Error | ||
37 | TALLY | KR-POS-3564 | 2509 | 10-03-2021 | 1173.00 | Error | ||
38 | PORTAL | KR-POS-1210 | KR-POS-1210 | 13-08-2020 | 1182.00 | |||
39 | TALLY | KR-POS-3564 | 353 | 13-08-2020 | 1182.00 | Error | ||
40 | PORTAL | 123 | 123 | 26-06-2020 | 1192.00 | |||
41 | PORTAL | KA-2021-962172 | KA-2021-962172 | 31-12-2020 | 1197.87 | |||
42 | PORTAL | 6726 | 6726 | 25-12-2020 | 1239.00 | |||
43 | TALLY | KR-POS-3564 | Dec-26 | 25-12-2020 | 1239.00 | Error | ||
44 | PORTAL | KR-POS-1463 | KR-POS-1463 | 02-09-2020 | 1255.00 | |||
45 | TALLY | KR-POS-3564 | 450 | 02-09-2020 | 1255.00 | Error | ||
46 | PORTAL | 2198 | 2198 | 08-12-2020 | 1260.00 | |||
47 | TALLY | KR-POS-3564 | Dec-98 | 05-12-2020 | 1260.00 | Error | ||
48 | PORTAL | IN-46253 | IN-46253 | 29-06-2020 | 1266.00 | |||
49 | PORTAL | 102 | 102 | 19-06-2020 | 1267.00 | |||
50 | PORTAL | KA-5451-20-21 | KA-5451-20-21 | 14-12-2020 | 1279.00 | |||
51 | TALLY | KR-POS-3564 | Dec-51 | 14-12-2020 | 1279.00 | Error | ||
B2B July-Mar + Tally |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:B51 | B2 | =IF(A2="PORTAL",C2,IF(COUNTIFS(B:B,"PORTAL",D:D,D2,E:E,E2),C2,C1)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A:B | Cell Value | ="tally" | text | NO |