Comparing Two Values and then Performing Additional Steps Based on Result

[FrenchCelt]

Hello,

I'm in the middle of coding a new macro and as one of my last steps, I want to compare the values of two cells and then take one of three steps depending on the outcome.

As part of the process, I have done a Conditional Formatting (font color used in the conditional formatting -16383844 and fill color 13551615) to look for duplicates in Columns B and O. I then do a COUNTA for both columns and display the results for Column B in S2 and Column O in T2. Ideally, both columns would have an identical number of cells with values. If not, I need to figure out where the non-duplicate is located (duplicates are good, non-duplicates are an issue to be investigated further).

1) If the non-duplicate in Column O, no problem, I can disregard, but I do want to find the cell in O without the conditional formatting and delete that cell and the cell to the right, shifting cells up.

2) If the non-duplicate is in Column B, I need to find the cell in B without the Conditional Formatting and highlight the cell in Column J of the same row in yellow so I can find it quickly.

3) If the values in S2 and T2 are identical, I can delete Columns O:T and call it a day.

Does all of that make sense? Can anyone offer any suggestions on how I should proceed?

 

[offthelip]

I have always thought that using color as a boolean signal is a mistake. A much better way is the use a "helper" column with the correct logical equatoin in it. then it will happily serve two purposes one as the source for conditional formatting and secondly as the source for other equations such as countif , etc. Also if you get into VBA trying to find out the color of cells is very slow, reading TRUE/FALSE from a column is very fast. So my suggestion is use a helper column!

 

[FrenchCelt]

I've heard of helper columns before, but am unfamiliar with the logic and implementation of them. Can you please advise further how I should proceed?

 

[offthelip]

I have done a Conditional Formatting (font color used in the conditional formatting -16383844 and fill color 13551615) to look for duplicates in Columns B and O.

A helper column that will find duplicates ( instead of condtional formatting ) can be done like this looking a columnn B B2 to B10:

=COUNTIF(B$2:B$10,B2)>1

the copy it down the column
This gives you TRUE if it is a duplicate and FALSE if there is only one of them. You change slightly to get a count if you want be getting rid of the >1

 

[FrenchCelt]

The duplicate comparison is between Columns B and O. Ideally, both columns would be perfectly duplicated. The problem arises if one column has more data than the other, because then I need to isolate the cell that isn't a duplicate. If it's in Column O, it's not an issue, but I want to delete that extra cell along with the cell to the right so that all the values line up with Column B. If the non-duplicate cell is in Column B, that's relevant info that I have to pass on.

 

[offthelip]

If you are comparing two columns, then the MATCH function will tell you if the value exists in the other column. However if you are lookin g for VBA solution to "tidy up" you data and get rid non duplicates then I would do it a completely different way. IS the order of the data the same in columns B and O or can the order be different?? I get the impression that Column B is master and anything that is not in column B get deleted. Is this correct. If one cell in B is is not matched then that is flagged up in column J ( true/false would be better than color yellow, although you could use conditional formatting on the TRUE/FALSE to highlight it. What code do you have at the moment?? Do you have any equations in any of the columns of the worksheet, if so which columns , this makes a difference in writing data back tothe worksheet so avoid overwriting equations.