RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello
This is a question solved earlier by etaf. But I am getting some problem. I need help to correct the formula as it is not working in some cases. Below is the image where the formula should match the condition. If the names in the B column are same one below the other and the amounts too are same with a maximum difference of 1/- more or less, then the rows shouldn't be formatted. Else it should be formatted
The top row may be less also then the below row. Whatever the case the difference between the two amounts is 1/- or less then it should show matched.
This is a question solved earlier by etaf. But I am getting some problem. I need help to correct the formula as it is not working in some cases. Below is the image where the formula should match the condition. If the names in the B column are same one below the other and the amounts too are same with a maximum difference of 1/- more or less, then the rows shouldn't be formatted. Else it should be formatted
The top row may be less also then the below row. Whatever the case the difference between the two amounts is 1/- or less then it should show matched.
Query Approximate match.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | January | 7091.00 | This has matched - difference is not more than 1/- or less and is correct | |||||||
2 | January | 7091.20 | This has matched - difference is not more than 1/- or less and is correct | |||||||
3 | February | 7091.20 | ||||||||
4 | March | 4534.00 | ||||||||
5 | March | 4534.40 | ||||||||
6 | April | 5940.00 | This has matched - difference is not more than 1/- or less and is correct | |||||||
7 | April | 5940.22 | This has matched - difference is not more than 1/- or less and is correct | |||||||
8 | May | 5940.00 | ||||||||
9 | June | 1365.00 | ||||||||
10 | July | 1000.00 | =COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1 | |||||||
11 | July | 1000.15 | =$A:$F | |||||||
12 | August | 1000.00 | ||||||||
13 | September | 999.05 | Matched but not accepted | |||||||
14 | September | 1000.00 | Matched but not accepted | |||||||
15 | October | 8500.00 | ||||||||
16 | October | 8500.00 | ||||||||
17 | November | 8500.00 | ||||||||
18 | December | 929.00 | ||||||||
19 | December | 929.25 | ||||||||
20 | Sunday | 29075.50 | Matched but not accepted | |||||||
21 | Sunday | 29075.00 | Matched but not accepted | |||||||
22 | Tuesday | 1147.00 | ||||||||
23 | Tuesday | 1147.14 | ||||||||
24 | Thursday | 16996.00 | Matched but not accepted | |||||||
25 | Thursday | 16997.00 | Matched but not accepted | |||||||
26 | Saturday | 1859.00 | ||||||||
27 | Saturday | 1859.00 | ||||||||
28 | Friday | 10363.50 | Matched but not accepted | |||||||
29 | Friday | 10364.00 | Matched but not accepted | |||||||
30 | Saturday | 2514.00 | ||||||||
31 | January | 100.00 | ||||||||
32 | January | 100.00 | ||||||||
33 | February | 100.00 | ||||||||
34 | March | 99.50 | Matched but not accepted | |||||||
35 | March | 100.00 | Matched but not accepted | |||||||
36 | April | 100.00 | ||||||||
37 | April | 100.00 | ||||||||
38 | May | 100.00 | ||||||||
39 | June | 100.00 | ||||||||
40 | July | 100.00 | Matched but not accepted | |||||||
41 | July | 100.95 | Matched but not accepted | |||||||
42 | August | 100.00 | ||||||||
43 | September | 100.00 | Matched but not accepted | |||||||
44 | September | 99.00 | Matched but not accepted | |||||||
45 | October | 99.50 | ||||||||
46 | October | 100.50 | Matched but not accepted | |||||||
47 | November | 100.00 | Matched but not accepted | |||||||
48 | December | 100.00 | ||||||||
49 | December | 100.00 | ||||||||
50 | Sunday | 100.00 | ||||||||
51 | Sunday | 100.00 | ||||||||
format rows 2 conditions |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
F28:F29 | Expression | =COUNTIFS($B$1:$B$4000,$B28,$F$1:$F$4000,"<"&$F28+0.5,$F$1:$F$4000,">"&$F28-0.5)=1 | text | NO |
A1:F27,A28:E29,A30:F51 | Expression | =COUNTIFS($B$1:$B$4000,$B1,$F$1:$F$4000,"<"&$F1+0.5,$F$1:$F$4000,">"&$F1-0.5)=1 | text | NO |
A1:F27,A28:E29,A30:F51 | Expression | =COUNTIFS($B$1:$B$4000,$B1048417,$F$1:$F$4000,"<"&$F1048417+0.5,$F$1:$F$4000,">"&$F1048417-0.5)=1 | text | NO |
Last edited: