Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate rows then deleting them | Excel Worksheet Functions | |||
Showing Duplicate Rows | Excel Discussion (Misc queries) | |||
How to delete duplicate rows in Excel 2000? | Excel Discussion (Misc queries) | |||
Showing Duplicate rows in excel | Excel Discussion (Misc queries) | |||
Duplicate Rows | Excel Worksheet Functions |