| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List out the Duplication data in another worksheet
Hi,
I have a workbook which contains the database of sales Invoices almost about 60000 Invoices Now in another worksheet I want to list out row details of the duplicate Invoices or the details of the Invoices which is more than single invoice no. The data is something like below given table. In sheet1: doc_type doc_no doc_date 46 74000001 01/04/2006 46 74000002 01/04/2006 46 74000003 01/04/2006 46 74000004 01/04/2006 46 74000005 01/04/2006 46 74000006 01/04/2006 46 74000007 01/04/2006 46 74000007 01/04/2006 46 74000009 01/04/2006 46 74000010 01/04/2006 46 74000011 01/04/2006 46 74000011 02/04/2006 46 74000013 02/04/2006 46 74000014 02/04/2006 The answer in sheet2 would be doc_type doc_no doc_date 46 74000007 01/04/2006 46 74000007 01/04/2006 46 74000011 01/04/2006 46 74000011 02/04/2006 i.e. the list of the duplicate doc_no. & its respective row details Is it possible -- Vicky |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
List out the Duplication data in another worksheet
Maybe this will work for you...
Put headers in Row 1 if you don't have them and sort your data by column B (if it's not already sorted). Then add a new column (D?): Put this in D2 and drag down =COUNTIF(B1:B3,B2) Then apply data|filter|autofilter to that column. Filter to show 1 Those will be the duplicates. Select those visible cells and copy|paste them where you want. The =countif() won't show how many times the value is duplicated, but it will show you if it's duplicated from the one directly above or below--and that should be enough to grab the duplicates. The data has to be sorted, though. Vicky wrote: Hi, I have a workbook which contains the database of sales Invoices almost about 60000 Invoices Now in another worksheet I want to list out row details of the duplicate Invoices or the details of the Invoices which is more than single invoice no. The data is something like below given table. In sheet1: doc_type doc_no doc_date 46 74000001 01/04/2006 46 74000002 01/04/2006 46 74000003 01/04/2006 46 74000004 01/04/2006 46 74000005 01/04/2006 46 74000006 01/04/2006 46 74000007 01/04/2006 46 74000007 01/04/2006 46 74000009 01/04/2006 46 74000010 01/04/2006 46 74000011 01/04/2006 46 74000011 02/04/2006 46 74000013 02/04/2006 46 74000014 02/04/2006 The answer in sheet2 would be doc_type doc_no doc_date 46 74000007 01/04/2006 46 74000007 01/04/2006 46 74000011 01/04/2006 46 74000011 02/04/2006 i.e. the list of the duplicate doc_no. & its respective row details Is it possible -- Vicky -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Populating worksheet via a drop down list ! | Excel Worksheet Functions | |||
| Using a data validation list to look up a defined named range in another worksheet | Charts and Charting in Excel | |||
| Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
| Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
| Convert PivotTable data into a worksheet list | Excel Worksheet Functions | |||
Linear Mode
