LinkBack URL
About LinkBacks
I have a spreadsheet that have duplicate rows with all column values the same. I would like to extract all those and remove them.
Example is shown in the attached file.
Cheers!
I have a spreadsheet that have duplicate rows with all column values the same. I would like to extract all those and remove them.
Example is shown in the attached file.
Cheers!
do you want to remove them altogether or leave just 1? also i dont see the logic of example 3. best you post a sample workbook with before and after.
but probably using a helper column say f
you could put in =d1&e1
then use advanced filter to extract unique rows
or to remove all duplicates
=SUMPRODUCT(--(INDEX($E$1:$E$100 & $F$1:$F$100, 0)=E1&F1))=1 filter on true
then copy paste to a new sheet.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Sorry, I mistated what makes rows duplicates. Ignore Schema Name and Table Name columns.
Work with only Column Name, Data Type and Qualifier columns - if these three values are the same for all rows containing the same Column Name then I donot want them (either remove or hide).
For all rows where Column Name is the same and any one of the rows containing Data Type and/or Qualifier is different then I would like to keep all rows with the same Column Name.
i thik youd best attach a workbook highlight rows considered duplicate
and you have not said whether if duplicate hide all or show only first
both examples shown see attached the duplicates are highlighted in matching colours
Last edited by martindwilson; 09-20-2009 at 06:38 PM.
I have uploaded revised worksheet and highlighted the rows I would like to keep and ones that I'd like to hide.
i dont understand why hide row 6? it's unique an should be shown
and row 10 is duplicate wirh 11 and thefor should be hidden
Row 6 does not have any duplicates and is not required.
I am trying to find all rows where the first column is duplicate and where either Data Type and/or Qualifier is different. If any found to be in this condiction I want all matching rows. Example,
- Row 4 & 5 needed because Data Type values are different.
- Row 10, 11, & 12 needed because Qaulifier are different.
- Row 16 & 17 needed because both Data Type and Qualifiers are different.
Hope I have explained properly.
Thanks.
nope, makes no sense to me! where are these two different row 10/11
Please Login or Register to view this content.
In example of 'martin 5' rows because row 12 is different from row 10 and 11 all three rows should be kept.
Please Login or Register to view this content.
why? the logic defeats me i'm afraid
Here's the reason. I have a dump of a very large Data Dictionary of our ERP application. Due to legacy reason, there are column with the same name but some with different Data Type and Qualifier.
We need all rows with the same column name (including one that is duplicated) so that we can see the type and qualifier that we should use to make all consistent.
This is part of Data Model Analysis work to make the model consistent before we upgrade our systems.
i think i see ok try this filter on false.
Last edited by martindwilson; 09-23-2009 at 04:21 PM.
Perfect! Thank you very much. I never new there was such a function SUMPRODUCT and that it could be used they way you have.
Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks