Compair list macro

[John Swift]

Hello and thank you for your help.

I have a macro that compares 2 lists and highlights the unique values in column A after comparing them to column B. This macro only being some what basic has a flaw.

It works perfectly until a unique value is added into column B at that point it stops working where it highlights the whole of column A red underneath the unique number in column B which is not what I need to happen.

So my hope is that some one can help me solve this issue and help me make it also highlight unique values in column B in a different colour as this macro is used to check hundreds of values at a time and some are duplicates. I used to just use the remove duplicates tool but because of the fact that in my lists that there could be more than one of the same numbers this did not work often leading to mistakes.

This is the macro I use

Please Login or Register  to view this content.

Once again thank you for your help

[sktneer]

Try this to see if this works as per your requirement....

Please Login or Register  to view this content.

[John Swift]

Hello and thank you for your help unfortunately it does not work as I need it to
Untitled.pngThis is knid of what I am working with but in a larger scale and you can see in column A I have a list of what I am expected to have and on the left it is what I actually have. As you can see by the picture that I only have one 7894561 but it has cleared both of them in column A which cant happen and as a but apart from that the macro works fine.

As a sub not it is possible for cells to only turn green when it is a unique number not auto filled?

Thank you for you help.

[sktneer]

Better you attach a sample workbook (not an image). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet (Sheet1) and an AFTER sheet (Sheet2) in the workbook. Make sure your desired results are shown on Sheet2 along with the logic behind the desired result if necessary.

Remember to desensitize the data.

[John Swift]

Hello and thank you hopefully this should be what you are looking for
Example.xlsm

[sktneer]

Please find the attached sheet and click on the Green Button on Test Sheet to run the code and compare the output of the code with your desired output on "Your Desired Output" Sheet to see if this is what you are trying to achieve.

[John Swift]

That is what I would like to happen.

But if I change any of the numbers in column B the macro does not work.

Example 1.xlsm

as attached you can see that the bottom values when removed do not highlight the bottom ones as missing even though they are.

Thank you for your help

[sktneer]

I think you need to explain your logic more clearly.
The code I suggested will first check col. A (from the bottom row) if the number is present in col. B also and if so, it checks then if this is the first occurrence of that number in col. A from the bottom and then highlights the cell.
And for col. B, the code checks if the number in col. B is not present in col. A and if so, it highlights the cell with that number.

As I was very unclear about your logic, so I concluded with this logic on the basis of your sample worksheet in post#5 considering your desired output. I didn't find any other logic to match your desired output.

Therefore it would be better to explain your logic that when will you prefer to highlight a number in col. A and when in col. B (keeping all other suggested codes and even your code aside).

Like in the attached sheet in post#5, you highlighted the number in bottom rows in col. A while the same numbers were present in top rows also. You should give your logic behind it.

[John Swift]

sorry for the confusion

the logic behind the macro that I need would be

Start at cell B1 use this value and compare it to the the values in column A (top to bottom or bottom to top does not matter to me but sometimes one list may be shorter than the other this generally varies)

if the value is found in column A then everything is ok and nothing should happen

if a cell value in column B is not found in column A then that value in column in column B should be highlighted

after all cells in column B have been checked if a all the cells in column A with a unique value should be highlighted

as I cant code in VBA very well this is the best explanation I can give

basically I need unique values highlighted in each column. Bearing in mind that there can be 2 or more of one value in each column which makes this more difficult.(I started off using the delete duplicate functions but if I had the value 01 in column B and two of this value in column A i.e. 01 01
then performing the remove duplicates it will remove both of the 01's in column A even though I only have one in column B when really I want it to leave one 01 as I only have one of them and need to find the other 01)

I hope this make some sense and will help.

[sktneer]

Based on your logic given in post#9, please find the attached sheet and see if this works as per your requirement now.

[John Swift]

thank you very much it work well

[sktneer]

Glad it worked for you and thanks for the feedback as well.