Keeping duplicate rows
 

I have a spreadsheet that has 15 columns and about 5400 rows. Column A has
the part number. Is there a function or macro that can run though the
spreadsheet and keep the whole row based on the fact that there are duplicate
part numbers in column A and delete the rows that are not duplicate. All I
want to see are the complete rows of all duplicate rows.

Thanks in advance.

Try a formula approach ?

Assuming sample table below is
in Sheet1, cols A to D, Part# in col A
data from row2 down

Part# Desc1 Desc2 Desc3
1111 Data1 Data1 Data1
1112 Data2 Data2 Data2
1112 Data3 Data3 Data3
1113 Data4 Data4 Data4
1114 Data5 Data5 Data5
1113 Data6 Data6 Data6
etc (till row 5500?)

Using an empty col to the right, say col Q?

Put in Q2:
=IF(A2="","",IF(COUNTIF(A$2:A$5500,A2)1,ROW(),"") )

Copy Q2 down to Q5500

In Sheet2
----------
Paste the headers over from Sheet1 into A1:D1, viz.:
Part# Desc1 Desc2 Desc3

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$Q:$Q,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$Q:$Q,ROWS($A$1:A1)),Sheet1!$Q:$Q,0)))

Copy A2 across to D2*, fill down to D5500
(cover the same range as was done in Sheet1)
*or across as many cols as your actual table contains

Sheet2 will return the desired results,
i.e. the "complete rows of all duplicate rows"

For the sample data above, you'll get:

Part# Desc1 Desc2 Desc3
1112 Data2 Data2 Data2
1112 Data3 Data3 Data3
1113 Data4 Data4 Data4
1113 Data6 Data6 Data6
(rest are blank rows)

(Rows for unique Part #s: 1111 and 1114 will be excluded)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Daniell" wrote in message
...
I have a spreadsheet that has 15 columns
and about 5400 rows. Column A has
the part number. Is there a function or macro
that can run though the
spreadsheet and keep the whole row
based on the fact that there are duplicate
part numbers in column A and delete the
rows that are not duplicate. All I
want to see are the complete rows of all duplicate rows.

Thanks in advance.

Hi!

Here's one way:

Assume your data is in the range A1:Q5400

Select column A and insert a new column.

In A1 enter this formula:

=COUNTIF(B$1:B$5400,B1)1

Copy down to A5400. If there are no blank cells within the column B range,
B1:B5400, you can just double click the fill handle for quick copying of the
formula. (beats the heck out of dragging!)

With the range A1:A5400 still selected goto EditCopy. Then EditPaste
SpecialValues.

Now, hit function key F5 and enter the range A1:Q5400. OK

Now goto DataSort

Sort the range on column A descending.

Any duplicates will have a value of TRUE in column A and all of the TRUE's
will now be at the top of the range.

You can either delete all the rows that show FALSE or copy all the rows that
show TRUE to another location.

Biff

"Daniell" wrote in message
...
I have a spreadsheet that has 15 columns and about 5400 rows. Column A has
the part number. Is there a function or macro that can run though the
spreadsheet and keep the whole row based on the fact that there are
duplicate
part numbers in column A and delete the rows that are not duplicate. All
I
want to see are the complete rows of all duplicate rows.

Thanks in advance.