Are more than ten thousand A, B two columns of data, A column of figures from 1 began to increase to the maximum, the value is equal with column B (yellow part); Then again increasing starting from 1 to the maximum and B column corresponding values are equal; Such as increasing from 1 to the maximum and B column corresponding value (green), believe that there is a lack of data, and find all the missing data cell code

CodePudding user response:

Where is the great god help me thank you

CodePudding user response:

Use conditional formatting + formula can?

1, select the first column (column A)
2, click on the "conditional formatting" - "new rules", the pop-up "new formatting rules" dialog
3, in the "select rule type" selected "by using a formula to set up the format of the cell", enter the following formula
=the AND (A1 & gt;=A2 and A1 & lt;> B1)
4, click on "format... , "pop-up" set the cell format "dialog box, select the" fill ", choose a color, click on "identify" returned "new formatting rules" dialog
5, click "ok"

Effects should be, the wrong data is marked

CodePudding user response:

Defect is can't change the color of the column A and B at the same time, if you want to change the color of the column B at the same time, need to make the same operation in the B column,

AND is not don't know why, there is no question of single set A column, set up A column to A B, the same will find B color position is wrong, then select the B column, click on the "conditional formatting" - "management rules" will find that in column B rule is not "=" AND (A1 & gt;=A2 and A1 & lt;> B1) ", the position of the 1 AND 2 will be a large number, don't know why, the formula to change to "=" AND (A1 & gt;=A2 and A1 & lt;> B1), "sure, no problem again,

CodePudding user response:

The conditional formatting upstairs put out, the column A wrong color next to the B column, A column for the color selection can
Can need not VBA as far as possible need not VBA

CodePudding user response:

Thank you for advice but too much data to see color also can be tricky to a VBA to lock data missing cell directly

CodePudding user response:

refer to 6th floor weixin_44339833 response:

thank you for directions but there are too many data to see color also can be tricky to a VBA to directly lock the data missing cell?

After mark the color, according to the color filter, not screen out all wrong data?

CodePudding user response:

refer to 7th floor response: VB amateurs

Quote: refer to the sixth floor weixin_44339833 response:

Thank directions but there are too many data to see color also can be tricky to a VBA to directly lock the data missing cell?

After mark the color, according to the color filter, not screen out all wrong data?

These are the basic function of EXCEL, knew more about

CodePudding user response:

After find out?

CodePudding user response:

IF you 32 B column data appear elsewhere in the following, can use the formula to solve, the input: C1 cell=the IF (B1 - COUNTIF (B: B, B1)=0, "number is equal to the maximum value", "the number is not equal to the maximum")

CodePudding user response:

In the column C do A auxiliary columns, when A=B, take A value, and then screening the C

CodePudding user response:

reference 5 floor qq_34921499 reply:

conditional formatting upstairs put out, the column A wrong color next to the B column, A column for the color selection can
Can need not VBA as far as possible need not VBA

EXCEL function is not very easy to understand, the function of VBA to realize the more meticulous;