Hiding Non-Duplicated Entries

[Lana]

Dear All!!!

It's very urgent, please help!!!
I've got to edit duplicated entries but not remove them - how can
hide NON-duplicated rows?

Resulting column should contain even number of duplicated entries.

Thanks in advance!!!
Lana :eek

 

[Lana]

I've created two columns with formulas that display 1 if preceding o
following rows are identical, and in the next column 1 for those wh
have ... etc

anyone with a more elegant option

 

[dominicb]

Good afternoon Lana

This code will do the trick and hide any row containing unique data.
To use it select the column of data and run the macro.

Sub HideUnique()
On Error Resume Next
Set UsrRng = Selection
For Each UsrCel In UsrRng
Dupd = UsrCel.Address
Dupd = UsrRng.Find(What:=UsrCel, After:=UsrCel, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Address
If Dupd = UsrCel.Address Then UsrCel.EntireRow.Hidden = True
Next UsrCel
End Sub

I've deliberately kept the code fairly simple, so if you are using this
for a huge range (your post doesn't state) and it seems a little slow
you could turn off the screen updating
(Application.ScreenUpdating=False) and automatic calculation
(Application.Calculation = xlCalculationManual) at the start of the
code and turn them back on at the end (Application.ScreenUpdating=True,
Application.Calculation = xlCalculationAutomatic).

HTH

DominicB

 

[greg7468]

Hi,
you could also use a helper column with

=IF(COUNTIF($A$1:$A$1000,A1)>1,"DUPLICATE","")

Then autofilter to show cells with duplicate in them.

HTH.

 

[Lana]

Now that I've sorted and edited them, how can i replace old values?

Table 1: sorted data with duplicates:

__ col A __Col B
1 w DupText
2 m DupText
------------------
Table 2: I've changed the entries:

__ col A __Col B
1 w DupText123
2 m DupText456
------------------
Table 3:

__ col A __Col B
1 w DupText .........
2 m DupText .........
3 m DupText
4 m DupText
5 w DupText
6 w DupText


*Now, what I need is replace values form the third table by thos
amended. Lookup won't work because there can be tens identical values.


I'm writing a bulk macro (not really excellent a programmer yet ;o))
), but maybe someone has an option?
Thank you both for your help, the deadlines are just unreasonable thi
time and I'm messed up ;o)))

x
Lan

 

[dominicb]

Hi Lana

Sorry, but I don't quite understand what you are trying to say. Could
you clarify what it is you need?

Thanks

DominicB

 

[Lana]

I thought I was being so organized

I'm lost, don't know how to explain

 

[dominicb]

Lana

If you're really stuck and your spreadsheet isn't highly confidential
you can e-mail it to me with an explanation of what you need if that
would help...

DominicB
(e-mail address removed)