Excel 2003 extract repeating cells

[Nit_Wit_400]

I have successfully sorted my data to show repeating entries in
relation to two specific columns. I can't seem to figure out how to
select these repeating entries (without doing it manually, of course)
and putting them either into their own column(s) or an entirely
different spreadsheet altogether.

The goal is to save time in managing THOUSANDS of documents in this
manner so that my colleagues can easily pick up repeating entries and
take according action. Doing it manually is very time-inefficient.

 

[Bernie Deitrick]

Nit Wit,

You don't really describe enough of your layout or what you actually mean by 'repeating entries' to
allow a final working example, but the code below will copy repeated rows in column A and B (where
the values in, for example, A3:B3 are repeated in cells A10:B10) to column J and K of the same
sheet. The important part is to come up with a formula for column C that will return TRUE or FALSE
depending on your repeating criteria...

HTH,
Bernie
MS Excel MVP


Sub NitWitMacro()
Dim LRow As Long
Dim myR As Range

Set myR = Selection

Application.ScreenUpdating = False
Application.EnableEvents = False

LRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("C1").EntireColumn.Insert
Range(Cells(2, 3), Cells(LRow, 3)).FormulaR1C1 = _
"=SUMPRODUCT((R2C1:R" & LRow & "C1=RC[-2])*(R2C2:R" & LRow & "C2=RC[-1]))>1"
Range("A:C").AutoFilter Field:=3, Criteria1:="TRUE"
Range("A:B").SpecialCells(xlCellTypeVisible).Copy
Range("K1").PasteSpecial Paste:=xlPasteValues
Range("C1").EntireColumn.Delete

Range("A:C").AutoFilter

myR.Select

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[Bernard Liengme]

Not too clear; please expand.
Do you mean that your rows have say 5 columns of data and you consider there
to be a repeat if say row 4 and row 10 have the same value in column A and
the same value in column C?
best wishes?

 

[Nit_Wit_400]

Mr. Deitrick

You said:

You don't really describe enough of your layout or what you actually mean by 'repeating entries' to
allow a final working example[...]

And you're right, I should have explained it further.

By repeating entries, I mean identical cells within two columns. Your
example covers this as you said "(where
the values in, for example, A3:B3 are repeated in cells A10:B10)."

I should have added that I want each repeating cell's corresponding
previous row to also be included in the new set of columns (P should
have corresponding previous row, Q and R should have repeating
information). For example D10:E10 repeats in D20:E20 so I want not
only that to show in column Q and R, but also I want B10 and B20 to be
in column P.

Not sure I'll need:

The important part is to come up with a formula for column C that will return TRUE or FALSE
depending on your repeating criteria...

Thank you so much for your time.
NitWit

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Mr. Deitrick

You said:

You don't really describe enough of your layout or what you actually mean
by 'repeating entries' to
allow a final working example[...]

And you're right, I should have explained it further.

By repeating entries, I mean identical cells within two columns. Your
example covers this as you said "(where
the values in, for example, A3:B3 are repeated in cells A10:B10)."

I should have added that I want each repeating cell's corresponding
previous row to also be included in the new set of columns (P should
have corresponding previous row, Q and R should have repeating
information). For example D10:E10 repeats in D20:E20 so I want not
only that to show in column Q and R, but also I want B10 and B20 to be
in column P.

Not sure I'll need:

The important part is to come up with a formula for column C that will
return TRUE or FALSE
depending on your repeating criteria...

Thank you so much for your time.
NitWit

 

[Nit_Wit_400]

The firm in which I work disallows me to disclose specific information
such as is on that sheet.
I wish I could fabricate an example, but I'm not sure it would be
specific enough.
I'll have to take what information Mr. Deitrick gave me and expand
from there.

Thank you all for your time and concern
NitWit

 

[Nit_Wit_400]

The firm in which I work disallows me to disclose specific information
such as is on that sheet.
I wish I could fabricate an example, but I'm not sure it would be
specific enough.
I'll have to take what information Mr. Deitrick gave me and expand
from there.

Thank you all for your time and concern
NitWit

 

[Nit_Wit_400]

Forgive my above repeated message...

I hope this will help you to understand.

I have many columns of information in my spreadsheet. The first
column (A) refers to a physical page on a physical document, the
second (B) refers to the document itself, and the third (C) correlates
to the second (B). Column E has the type of reference book the
information on the page (column A) came from and Column F has the book
and page number of the reference book (column E).

In this spreadsheet, there are unfortunate repeat entries of Column E
and F. I need to group them together with Columns A,B, and C so that
an analyst can pick them out and prevent redundant work on those
files.

I hope this is specific enough, and I apologize if I have confused any
of those who were trying to help my jumbled self.

 

[Bernie Deitrick]

Select your entire data base and sort it based on column E as the first sort criteria, and column F
as the second.

Insert a new column G, and use this formula in G2:

=SUMPRODUCT(($E$2:$E$10000=E2)*($F$2:$F$10000=F2))>1

Change the two $10000 to the actual row count.

Then copy G2 down to match your data. Then use Data / Filter... Autofilter and select TRUE from
the dropdown at the top of column G. That will show only the repeated values, along with the other
data associated with it.

HTH,
Bernie
MS Excel MVP

 

[Nit_Wit_400]

Thank you all very much.

Mr Deitrick,

As you already probably know, your example worked exquisitely.


Always,
NitWit