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
shashidharga Posts 4 Registration date Sunday May 25, 2014 Status Member Last seen May 26, 2014 - May 26, 2014 at 06:49 AM
Related:
- Finding duplicate value and display the duplicate cell address
- What's the address - Guide
- Free fire gmail address - Guide
- Dns address could not be found android - Guide
- How to duplicate a google doc - Guide
- Phone ip address - Guide
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
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)
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
shashidharga
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014
May 26, 2014 at 02:11 AM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 26, 2014 at 05:01 AM
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.
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.
shashidharga
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014
May 26, 2014 at 06:14 AM
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
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
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
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.
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.
shashidharga
Posts
4
Registration date
Sunday May 25, 2014
Status
Member
Last seen
May 26, 2014
May 26, 2014 at 06:49 AM
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
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