Finding duplicate value and display the duplicate cell address

Closed
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014
-
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014
-
Hi,
I'm a new member. I've data of different roads with their distance data in multiple sheets ie., different sheets for different years. I want check the data in two different columns from multiple sheets for duplicates and highlight the duplicate cells and display the cell address of the duplicate from the other sheets.
thanx in advance
shashi

6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
sample data in col A and B are as given below from A1 B1 down. no header row

a a
s d
d g
f j
g l
h y
j


try this macro (loop over the sheets- write the modification for this)

Sub test()
Dim r1 As Range, r2 As Range, c1 As Range
Dim cfind As Range
Set r1 = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
Set r2 = Range(Range("B1"), Cells(Rows.Count, "B").End(xlUp))
r1.Cells.Interior.ColorIndex = xlNone

For Each c1 In r1
On Error Resume Next
Set cfind = r2.Cells.Find(what:=c1.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
c1.Interior.ColorIndex = 6
Else
GoTo nextc1
End If
nextc1:
Next c1
End Sub
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014

Thanks Venkat, thank you very much for the quick reply.
I'm a Highway Engineer. I've road improvement data of last 5 years. If I propose a highway work of a particular length say, from chainage 2.00 to 3.00 Km. in this year, I've to check whether this chainage is not included in the past 5 years. For this I want to create macro such that when the macro is run, it must highlight the repeated chainage in the past 5 years and the cell address of the repeated cell in the column "Duplicate Status" . I'm attaching the download link of the excel file ;
http://speedy.sh/bMt9E/KRN-Test.xls
In the Sheet :Example 2008-09", I've narrated the desired result after running the macro.
Thanx again
shashi
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
I have scrutinized the various sheets in the workbook. I understand sheet 2008-09,2009-10 etc. but why do you call the first sheet as exaple 2008-09. it is the sheet containing current projects. why not current project. or alternative 2015(see below in naming sheets)
five years is a vague criterion. excel would like to have precise input of data.
present year is 2014-15
5 years before means 09-10,10-11,11-12,12-13,13-14 is it ok.
then there are two other sheets at the end what are they.

one suggestion instead of calling sheet 2008-09 calle the sheet as 2009,2010,2011,2012,2013,2014 and 2015 so that sheet names go up by one number
that is sheet name 2010=sheet name 2009+1 etc

go through your workbook make these minor corection, remove sheets not required and get back to newsgroup. for vba clear and logical instructions must be give. computer is not like a human being to understand instinctively or by guess work.
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014

Thanx Venkat,
The sheet "Example 2008-09" is for your reference to show how I want the result after running the code. This sheet is not included in the project, I'l remove it. But the last two sheets are the data for the pull down for the sheets 2009-10 to 2013-14. If I remove these two sheets the data in the year sheets have to be entered manually. While manually entering data the data entry operator may enter incorrect names and chainages etc.,. Hence we have provided the data. By using this data the road names can be directly selected by the pull down menu.
sHASHI
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
because you are dealing with this file you instintively know what you want. But I do not know your project. so I do not understand completely.

eg.
example 2008-09
row 3 is
2 HA 2 Road 2 171.1 192.5 Reconstruction
you indicated this is duplicate of row no. 5 of 2009-10
row no. 5 of 2009-10 is
5 HA 1 Road 2 171.1 192.5

one is HA2 and theother HA1 though other columns are same. doe it mean HA number is not relevant only road no. and the two values in col D and E of example 2008-09 sheet are relevant
what about other columns F G etc

in example 2008-09 row no 1 is
1 HA 2 Road 1 544.32 576.12 Reconstruction
and in 2008-09 row no.
1 HA 2 Road 1 544.32 576.12 Reconstruction
then is this also not duplicate.

I think you have to convey what you want to me who is unaware of your thinking.
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014

The sheet "Example 2008-09" is to show you how I want the result. The highlighted cells are only to show the result. After running the macro, the result of the repeated cells must show like that in the sheet "Example 2008-09".
I am attaching the work book removing the sheet "Example 2008-09"
I want to compare columns C, G & H for duplicates from the sheets 2009-10 to 2013-14.
shashi
http://speedy.sh/Sg7gU/KRN-Test.xls