L
Legacy 395409
Guest
Hi all,
I've been searching around but cant seem to find the right answer. The closest case I found is this one here:
Inserting new rows based on cell value, and copying data to the new rows
But in my case, before inserting new copied rows, the amount of rows depends on more than 1 factor:
1st factor is IF Column B says "Match JNT", then proceed to 2nd factor, but if Column B display #N/A or empty, then ignore that row & move to next row below.
2nd factor is a calculation from 2 different cell values (Column E & Column A). Column E "QTY" is the # of rows I need to match, Column A is how many rows of Column D's value already exist. Sometimes the # in Column A may be larger or smaller than Column E. If Column A # is same/larger than Column E #, then can ignore & move to next row. But IF Column A # is smaller than Column E #, then I need VBA to copy that row & insert remaining amount of copied rows beneath the row.
As shown below in the mini sheet, starting from 2nd row, I already had formulas planted in Column A to count how many duplicates of cell value in Column D, but I needed the total rows to match the QTY # from Column E. However, sometimes the data set I receive may came in random order, so sometimes the data set I receive might already have some "duplicate rows", so I need to count the total count of the value in Column D (which is the job of Column A), then subtract from QTY # from Column E.
...and this is what I want the VBA to do...
I hope I explained correctly, anyhow please advise, and I thank in advance & really appreciated if anyone knows the magic code!
Johnnie
I've been searching around but cant seem to find the right answer. The closest case I found is this one here:
Inserting new rows based on cell value, and copying data to the new rows
But in my case, before inserting new copied rows, the amount of rows depends on more than 1 factor:
1st factor is IF Column B says "Match JNT", then proceed to 2nd factor, but if Column B display #N/A or empty, then ignore that row & move to next row below.
2nd factor is a calculation from 2 different cell values (Column E & Column A). Column E "QTY" is the # of rows I need to match, Column A is how many rows of Column D's value already exist. Sometimes the # in Column A may be larger or smaller than Column E. If Column A # is same/larger than Column E #, then can ignore & move to next row. But IF Column A # is smaller than Column E #, then I need VBA to copy that row & insert remaining amount of copied rows beneath the row.
As shown below in the mini sheet, starting from 2nd row, I already had formulas planted in Column A to count how many duplicates of cell value in Column D, but I needed the total rows to match the QTY # from Column E. However, sometimes the data set I receive may came in random order, so sometimes the data set I receive might already have some "duplicate rows", so I need to count the total count of the value in Column D (which is the job of Column A), then subtract from QTY # from Column E.
| MYSP_1149000-00-A_EBOMReport.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Count | JNT MATCH | Combined Child+Parent P/N | Qty | |||
| 2 | 3 | Match JNT | 1755704-00-A_1501464-00-A | 1755704-00-A_1501464-00-A | 4 | ||
| 3 | 2 | Match JNT | 1516228-00-B_1501464-00-A | 1516228-00-B_1501464-00-A | 2 | ||
| 4 | 2 | Match JNT | 1063260-00-C_1501464-00-A | 1063260-00-C_1501464-00-A | 4 | ||
| 5 | #N/A | #N/A | 1626076-00-B_1501464-00-A | 1 | |||
| 6 | #N/A | #N/A | 1626075-00-B_1501465-00-A | 1 | |||
| 7 | 3 | Match JNT | 1755704-00-A_1501464-00-A | 1755704-00-A_1501464-00-A | 4 | ||
| 8 | #N/A | #N/A | 1497466-00-C_1501664-00-A | 2 | |||
| 9 | 2 | Match JNT | 1516228-00-B_1501464-00-A | 1516228-00-B_1501464-00-A | 2 | ||
| 10 | #N/A | #N/A | 1626026-00-B_1501264-00-A | 1 | |||
| 11 | #N/A | #N/A | 1626265-00-B_1501464-00-A | 1 | |||
| 12 | 1 | Match JNT | 1623296-00-B_1501464-00-A | 1623296-00-B_1501464-00-A | 2 | ||
| 13 | 1 | Match JNT | 1641110-00-A_1501464-00-A | 1641110-00-A_1501464-00-A | 2 | ||
| 14 | 2 | Match JNT | 1063260-00-C_1501464-00-A | 1063260-00-C_1501464-00-A | 4 | ||
| 15 | 3 | Match JNT | 1755704-00-A_1501464-00-A | 1755704-00-A_1501464-00-A | 1 | ||
temp | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A15 | A2 | =IF(ISERROR($B2),"",IF(B2="","",COUNTIF($D$2:$D$28549,$D2))) |
| B2:B15 | B2 | =IF(C2="_","",IF(C2=D2,"Match JNT","NOT")) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| D4 | Cell Value | >0 | text | NO |
| D4 | Cell | contains an error | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=1) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=2) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=3) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=4) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=5) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=6) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=7) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=8) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=9) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=10) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=11) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=12) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=13) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=14) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=15) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=16) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=17) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=18) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=19) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=20) | text | NO |
| D3 | Cell Value | >0 | text | NO |
| D3 | Cell | contains an error | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=1) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=2) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=3) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=4) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=5) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=6) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=7) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=8) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=9) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=10) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=11) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=12) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=13) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=14) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=15) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=16) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=17) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=18) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=19) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=20) | text | NO |
| D2 | Cell Value | >0 | text | NO |
| D2 | Cell | contains an error | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=1) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=2) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=3) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=4) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=5) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=6) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=7) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=8) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=9) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=10) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=11) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=12) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=13) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=14) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=15) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=16) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=17) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=18) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=19) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=20) | text | NO |
| B1:D1,B2:C4,B5:D15 | Cell Value | >0 | text | NO |
| B1:D1,B2:C4,B5:D15 | Cell | contains an error | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=1) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=2) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=3) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=4) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=5) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=6) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=7) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=8) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=9) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=10) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=11) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=12) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=13) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=14) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=15) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=16) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=17) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=18) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=19) | text | NO |
| E1,E5:E15 | Expression | =AND(B1="Match JNT",E1=20) | text | NO |
| A:A | Expression | =AND($B1="Match JNT",$A1<>$E1) | text | NO |
...and this is what I want the VBA to do...
| MYSP_1149000-00-A_EBOMReport.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Count | JNT MATCH | Combined Child+Parent P/N | Qty | |||
| 2 | 4 | Match JNT | 1755704-00-A_1501464-00-A | 1755704-00-A_1501464-00-A | 4 | ||
| 3 | 4 | Match JNT | 1755704-00-A_1501464-00-A | 1755704-00-A_1501464-00-A | 4 | ||
| 4 | 2 | Match JNT | 1516228-00-B_1501464-00-A | 1516228-00-B_1501464-00-A | 2 | ||
| 5 | 4 | Match JNT | 1063260-00-C_1501464-00-A | 1063260-00-C_1501464-00-A | 4 | ||
| 6 | 4 | Match JNT | 1063260-00-C_1501464-00-A | 1063260-00-C_1501464-00-A | 4 | ||
| 7 | 4 | Match JNT | 1063260-00-C_1501464-00-A | 1063260-00-C_1501464-00-A | 4 | ||
| 8 | #N/A | #N/A | 1626076-00-B_1501464-00-A | 1 | |||
| 9 | #N/A | #N/A | 1626075-00-B_1501465-00-A | 1 | |||
| 10 | 4 | Match JNT | 1755704-00-A_1501464-00-A | 1755704-00-A_1501464-00-A | 4 | ||
| 11 | #N/A | #N/A | 1497466-00-C_1501664-00-A | 2 | |||
| 12 | 2 | Match JNT | 1516228-00-B_1501464-00-A | 1516228-00-B_1501464-00-A | 2 | ||
| 13 | #N/A | #N/A | 1626026-00-B_1501264-00-A | 1 | |||
| 14 | #N/A | #N/A | 1626265-00-B_1501464-00-A | 1 | |||
| 15 | 2 | Match JNT | 1623296-00-B_1501464-00-A | 1623296-00-B_1501464-00-A | 2 | ||
| 16 | 2 | Match JNT | 1623296-00-B_1501464-00-A | 1623296-00-B_1501464-00-A | 2 | ||
| 17 | 2 | Match JNT | 1641110-00-A_1501464-00-A | 1641110-00-A_1501464-00-A | 2 | ||
| 18 | 2 | Match JNT | 1641110-00-A_1501464-00-A | 1641110-00-A_1501464-00-A | 2 | ||
| 19 | 4 | Match JNT | 1063260-00-C_1501464-00-A | 1063260-00-C_1501464-00-A | 4 | ||
| 20 | 4 | Match JNT | 1755704-00-A_1501464-00-A | 1755704-00-A_1501464-00-A | 1 | ||
temp | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A2:A20 | A2 | =IF(ISERROR($B2),"",IF(B2="","",COUNTIF($D$2:$D$28554,$D2))) |
| B2:B20 | B2 | =IF(C2="_","",IF(C2=D2,"Match JNT","NOT")) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B18:D18 | Cell Value | >0 | text | NO |
| B18:D18 | Cell | contains an error | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=1) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=2) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=3) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=4) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=5) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=6) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=7) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=8) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=9) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=10) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=11) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=12) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=13) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=14) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=15) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=16) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=17) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=18) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=19) | text | NO |
| E18 | Expression | =AND(B18="Match JNT",E18=20) | text | NO |
| A18 | Expression | =AND($B18="Match JNT",$A18<>$E18) | text | NO |
| B16:D16 | Cell Value | >0 | text | NO |
| B16:D16 | Cell | contains an error | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=1) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=2) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=3) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=4) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=5) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=6) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=7) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=8) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=9) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=10) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=11) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=12) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=13) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=14) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=15) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=16) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=17) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=18) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=19) | text | NO |
| E16 | Expression | =AND(B16="Match JNT",E16=20) | text | NO |
| A16 | Expression | =AND($B16="Match JNT",$A16<>$E16) | text | NO |
| D6:D7 | Cell Value | >0 | text | NO |
| D6:D7 | Cell | contains an error | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=1) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=2) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=3) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=4) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=5) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=6) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=7) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=8) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=9) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=10) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=11) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=12) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=13) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=14) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=15) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=16) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=17) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=18) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=19) | text | NO |
| E6:E7 | Expression | =AND(B6="Match JNT",E6=20) | text | NO |
| B6:C7 | Cell Value | >0 | text | NO |
| B6:C7 | Cell | contains an error | text | NO |
| A6:A7 | Expression | =AND($B6="Match JNT",$A6<>$E6) | text | NO |
| D3 | Cell Value | >0 | text | NO |
| D3 | Cell | contains an error | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=1) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=2) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=3) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=4) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=5) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=6) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=7) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=8) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=9) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=10) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=11) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=12) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=13) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=14) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=15) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=16) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=17) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=18) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=19) | text | NO |
| E3 | Expression | =AND(B3="Match JNT",E3=20) | text | NO |
| B3:C3 | Cell Value | >0 | text | NO |
| B3:C3 | Cell | contains an error | text | NO |
| A3 | Expression | =AND($B3="Match JNT",$A3<>$E3) | text | NO |
| D5 | Cell Value | >0 | text | NO |
| D5 | Cell | contains an error | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=1) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=2) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=3) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=4) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=5) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=6) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=7) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=8) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=9) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=10) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=11) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=12) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=13) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=14) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=15) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=16) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=17) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=18) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=19) | text | NO |
| E5 | Expression | =AND(B5="Match JNT",E5=20) | text | NO |
| D4 | Cell Value | >0 | text | NO |
| D4 | Cell | contains an error | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=1) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=2) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=3) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=4) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=5) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=6) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=7) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=8) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=9) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=10) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=11) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=12) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=13) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=14) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=15) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=16) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=17) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=18) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=19) | text | NO |
| E4 | Expression | =AND(B4="Match JNT",E4=20) | text | NO |
| D2 | Cell Value | >0 | text | NO |
| D2 | Cell | contains an error | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=1) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=2) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=3) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=4) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=5) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=6) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=7) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=8) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=9) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=10) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=11) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=12) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=13) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=14) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=15) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=16) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=17) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=18) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=19) | text | NO |
| E2 | Expression | =AND(B2="Match JNT",E2=20) | text | NO |
| B1:D1,B2:C2,B8:D15,B4:C5,B17:D17,B19:D20 | Cell Value | >0 | text | NO |
| B1:D1,B2:C2,B8:D15,B4:C5,B17:D17,B19:D20 | Cell | contains an error | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=1) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=2) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=3) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=4) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=5) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=6) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=7) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=8) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=9) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=10) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=11) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=12) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=13) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=14) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=15) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=16) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=17) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=18) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=19) | text | NO |
| E1,E8:E15,E17,E19:E20 | Expression | =AND(B1="Match JNT",E1=20) | text | NO |
| A1:A2,A4:A5,A8:A15,A17,A19:A1048576 | Expression | =AND($B1="Match JNT",$A1<>$E1) | text | NO |
I hope I explained correctly, anyhow please advise, and I thank in advance & really appreciated if anyone knows the magic code!
Johnnie
