Comparing Same Column to Column for Matches and then Comparing Offset Values

Hello,

Hope someone could help me with this one. I am comparing a range in one column to the same column for identical matches and then if there are any matches, I am then comparing the next column over to see if the value is lower, higher, or the same.

I think I am close but I also think that my issue is that the initial data is being compared to itself which would mean I need to exclude the active data in my first range. I am not sure how to do this and actually I am not sure this is the only problem with what I have so far. The macro is needed to compare document numbers and revisions to determine which are duplicates and then which are older and newer.

Here is what I have so far:

Please Login or Register  to view this content.

Here is an example of data in excel:

Document Name	Document Revision	Dup Comparison	Example (What Column C should output)
DOC-XX-001	0		Duplicate (Same Rev)
DOC-XX-001	0		Duplicate (Same Rev)
DOC-XX-002	0		Duplicate (Lower Rev)
DOC-XX-002	1		Duplicate (Lower Rev)
DOC-XX-002	2		Duplicate (Lower Rev)
DOC-XX-002	3		Duplicate (Lower Rev)
DOC-XX-002	4		Duplicate (Higher Rev)
DOC-XX-003	1		Duplicate (Higher Rev)
DOC-XX-003	0		Duplicate (Lower Rev)
DOC-XX-004	0		Duplicate (Lower Rev)
DOC-XX-004	3		Duplicate (Higher Rev)
DOC-XX-004	0		Duplicate (Lower Rev)

Eventually I will add in a function to move anything that is the lowest revision or the additional same revisions to another speadsheet as I am only interested in keeping the highest revisions. Anyway, any help would be much appreciated.

Thank you

Hi Richard,

I guess I didn't consider using a formula as I already had a macro for the document comparison and now I am trying to add in the revision comparison as well. Would a formula be faster or maybe there is something in a formula that I cannot do in a macro? Thanks for the reply and I would absolutely be open to using a formula or any suggestions if it could achieve what I need.

Thanks again but I was able to exclude the active compare string by simply changing it to nothing during the y comparison. Did exactly what I was asking for by excluding the active compare string temporarily.