Finding duplicate value and display the duplicate cell address

Closed
shashidharga Posts 4 Registration date Sunday May 25, 2014 Status Member Last seen May 26, 2014 - May 25, 2014 at 11:05 PM
shashidharga Posts 4 Registration date Sunday May 25, 2014 Status Member Last seen May 26, 2014 - May 26, 2014 at 06:49 AM
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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 26, 2014 at 12:22 AM
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
0
shashidharga Posts 4 Registration date Sunday May 25, 2014 Status Member Last seen May 26, 2014
May 26, 2014 at 02:11 AM
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
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 26, 2014 at 05:01 AM
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.
0
shashidharga Posts 4 Registration date Sunday May 25, 2014 Status Member Last seen May 26, 2014
May 26, 2014 at 06:14 AM
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
0

Didn't find the answer you are looking for?

Ask a question
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 26, 2014 at 06:41 AM
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.
0
shashidharga Posts 4 Registration date Sunday May 25, 2014 Status Member Last seen May 26, 2014
May 26, 2014 at 06:49 AM
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
0