Remove duplicate rows in Excel

[Susan Bugher]

A table without duplicate entries is simply the table after de-duping.
As I said, I don't want to de-dupe. I want unique entries only.

And that's what you get. but. . . having read your responses to Adrian and Jerry the light has
*finally* dawned here - you want to see only those records that are unique (have no duplicates) in
the original table. "Extract unique" is not the right tool for that task. I'll comment further in
response to your other posts.

Susan
--
Posted to alt.comp.freeware
Search alt.comp.freeware (or read it online):
http://groups.google.no/groups?q=+group:alt.comp.freeware&hl=en
Pricelessware & ACF: http://www.pricelesswarehome.org
Pricelessware: http://www.pricelessware.org (not maintained)

 

[JL Hotz]

I much prefer the latter. How would I do that please ? I want to avoid
copying and paste special. Too time consuming.

< snip >

Regards, John.

Not time consuming at all. Only a few seconds.

Simply select all the cells in column D with the mouse, or what I
normally do: select the first cell in the column, then shift+end+down
arrow. That will select all cells in that column with values *as long
as there are no empty cells*. The end-down combo will stop at the first
empty cell. Mouse may work better for you, but mine gets crazy
sometimes. Anyway once the cells in column D are selected, do a copy
(or ctrl+c). Now since your original selected cell was the 1st in
column D, press the right arrow key one time and you will be in the
first cell of column E (or use the mouse to get there). Now with cell
E1 as the active cell do edit/paste-special/formulas-as-values. The
values calculated by the formulas will be pasted down the column and
their underlying formulas will be gone. Column D & E should be
identical in appearance at this point, but any re-sort will change
column D due to the formulas. Column E will sort without changing.

At this point, you could delete columns B, C, & D - leaving only your
data in column A and the values column (moved to B now if you deleted
the other columns or still in D if you only deleted the data).

Then select everything and do data/sort, using the values column. This
will get all the zeros to the top if sorted ascending. Simply select
everything below that and hit the delete key or edit/delete or right
click/delete, whtever your preferred method is.

good luck!

regards, Jerry

 

[Susan Bugher]

Worked perfectly.


Neat way to see duplicated records.


Okay. Works fine.



I much prefer the latter. How would I do that please ? I want to avoid
copying and paste special. Too time consuming.

I do the same kind of check this way (in QuattroPro): @IF(A1=A2,"DUPE",@IF(A2=A3,"DUPE","UNIQUE"))

That checks for duplicates entries both above and below in one swell foop. . .

Per your reply to Adrian I take it you have a one column table of book titles created by combining
an "old" list of titles and a "new" list of titles. If you add a column that shows which list the
title came (old or new) from and a column with the formula to check for duplicate titles (after
sorting) you can extract the info of interest to new tables.

The criteria for discontinued titles: "list" column cell contents = "old", "dupe" column cell
contents = "UNIQUE"

The criteria for new titles: "list" column cell contents = "new", "dupe" column cell contents = "UNIQUE"

HTH

Susan
--
Posted to alt.comp.freeware
Search alt.comp.freeware (or read it online):
http://groups.google.no/groups?q=+group:alt.comp.freeware&hl=en
Pricelessware & ACF: http://www.pricelesswarehome.org
Pricelessware: http://www.pricelessware.org (not maintained)

 

[Adrian Carter]

"> Thank you for your offer. I was however ideally wanting to do this

in Excel, or similar. If I want unique lines in a text file I just use
the excellent TED Notepad. From ;

tom
tom
dick
harry
harry
harry

I end up with

dick

only.

The reason I want a quick/easy way to get rid of dupes in spreadsheets
is because I can have a booklist for March and a booklist for April.

If I could remove everything that is in both spreadsheets then I am
closer to finding out which are the "new" titles.

What Jerry had to say seems to be getting me closer to what I want.
Here is EXACTLY what I would like ;

Booklist for March :

Book 1
Book 2
Book 3

Booklist for April:

Book 3
Book 4
Book 5

Result : A spreadsheet/list/file giving me discontinued books, and
new books

Eg. Discontinued books Book 1, Book 2.
New book additions Book 4, Book 5

As you can see, I don't want to see duplicates (eg. 3), books that
are listed in both month's lists.

Regards, John.

Jerry's solution will work for you, and I think it could be macro-ized
to do it all by just selecting the relevant cells and pressing a
hotkey. But I don't know enough about VBA to do it myself.
The idea of the tray utility is *not* to use a plain text file. You
would just select & copy the relevant cells, click once on the
tray icon, then paste back to Excel (or word processor table
or whatever).

Cheers,
Adrian

 

[John Fitzsimons]

John Fitzsimons wrote:

Hi Susan,

I do the same kind of check this way (in QuattroPro): @IF(A1=A2,"DUPE",@IF(A2=A3,"DUPE","UNIQUE"))

Many thanks. That is a formulae I have been wanting for years.

That checks for duplicates entries both above and below in one swell foop. . .

Per your reply to Adrian I take it you have a one column table of book titles created by combining
an "old" list of titles and a "new" list of titles.
Yep.

If you add a column that shows which list the
title came (old or new) from and a column with the formula to check for duplicate titles (after
sorting) you can extract the info of interest to new tables.

Interesting. I was just going to delete dups and sort by "old"/"new".
I hadn't thought of extraction.

The criteria for discontinued titles: "list" column cell contents = "old", "dupe" column cell
contents = "UNIQUE"

The criteria for new titles: "list" column cell contents = "new", "dupe" column cell contents = "UNIQUE"

HTH

I couldn't find a "list" function in Excel. The above appears to be
using an advanced filter for extraction. Autofilter makes extraction
easier BUT I couldn't work out the steps involved in using advanced
filtering and only showing rows where columns contain eg. "old" and
"unique". :-(

Thanks anyway. I really like that formulae.

Regards, John.

 

[John Fitzsimons]

John Fitzsimons wrote:

Not time consuming at all. Only a few seconds.

Well, as my spreadsheet may have 40,000+ rows I find going from the
first to the last can be time consuming/tedious. I do know how to get
to the bottom of the selection BUT I often forget it. :-(

< snip description of manual method of pasting results as "values". >

Thanks, but I understood that. I read your comment to mean that the
formulae could result in the values being placed in another column.
Avoiding the need for copy/paste. I guess I misunderstood your
comments. Sorry.

Doing the above the manual way might not take overly long. IF done
once. I was wanting however a system that not only worked but worked
with minimal input from me. As I was wanting a system I could use
quickly/efficiently often.

Thanks again for your comments. You are an excellent trainer. If you
don't do training....you should.

Regards, John.

 

[Susan Bugher]

Well, as my spreadsheet may have 40,000+ rows I find going from the
first to the last can be time consuming/tedious. I do know how to get
to the bottom of the selection BUT I often forget it. :-(

Easier: Select the *entire* column by right-clicking on the column heading. Copy the column and
special paste it as "values" in the same or a different column. Works in QuattroPro - IIRC columns
can be copied and pasted in Excel too.

Susan
--
Posted to alt.comp.freeware
Search alt.comp.freeware (or read it online):
http://groups.google.no/groups?q=+group:alt.comp.freeware&hl=en
Pricelessware & ACF: http://www.pricelesswarehome.org
Pricelessware: http://www.pricelessware.org (not maintained)

 

[Susan Bugher]

On Fri, 01 Jul 2005 13:34:09 GMT, Susan Bugher


Many thanks. That is a formulae I have been wanting for years.

YW. If you have any other such requests on your wish list feel free to post them. I don't claim
to be an expert but I do use a lot of formulas in spreadsheets so I might have an answer for you.
Sloth and indolence => if I have to perform any set of operations more than once I tend to write a
formula to do it for me.

Interesting. I was just going to delete dups and sort by "old"/"new".
I hadn't thought of extraction.

I couldn't find a "list" function in Excel. The above appears to be
using an advanced filter for extraction. Autofilter makes extraction
easier BUT I couldn't work out the steps involved in using advanced
filtering and only showing rows where columns contain eg. "old" and
"unique". :-(

Apologies for the poor explanation - I was using "list" and "dupe" as the "field names" (labels in
the first cell of a column). I couldn't remember what an Excel criteria table looks like - if I ever
knew - my acquaintance with Excel is brief, long ago and far away. . .

In QuattroPro I would create a 2 column, 2 row criteria table that tells QuattroPro what to look
(the table can be placed anywhere on any page). The first row would have the "field name", the
second row would have the criteria. It would look like this:

list dupe
new UNIQUE

OR

list dupe
old UNIQUE

FWIW if I wanted to extract both old and new UNIQUES's at the same time I'd use this as the criteria
table:

list dupe
new UNIQUE
old UNIQUE

I hope that's clearer than the last explanation. . .

Susan
--
Posted to alt.comp.freeware
Search alt.comp.freeware (or read it online):
http://groups.google.no/groups?q=+group:alt.comp.freeware&hl=en
Pricelessware & ACF: http://www.pricelesswarehome.org
Pricelessware: http://www.pricelessware.org (not maintained)

 

[JL Hotz]

Well, as my spreadsheet may have 40,000+ rows I find going from the
first to the last can be time consuming/tedious. I do know how to get
to the bottom of the selection BUT I often forget it. :-(

< snip description of manual method of pasting results as "values". >

Thanks, but I understood that. I read your comment to mean that the
formulae could result in the values being placed in another column.
Avoiding the need for copy/paste. I guess I misunderstood your
comments. Sorry.

Doing the above the manual way might not take overly long. IF done
once. I was wanting however a system that not only worked but worked
with minimal input from me. As I was wanting a system I could use
quickly/efficiently often.

Thanks again for your comments. You are an excellent trainer. If you
don't do training....you should.

Regards, John.

Thanks for the kind words. I don't think I was perfectly clear on
everything, but you seem to grasp. There are other methods as Susan
mentioned (selecting the whole column works the same in excel). Doesn't
matter that there is no data after a certain row.

Important thing: **Don't let excel intimidate you**. If this is a
frequently repeated operation, you may be ready to try your hand at
macros. Unfortunately I'm not so good at that, although I have created
a few. I still use excel at work but have switched to OPenOffice at
home.

regards, Jerry

 

[B. R. 'BeAr' Ederson]

On Sat, 02 Jul 2005 23:06:21 +1000, John Fitzsimons wrote:

[Delete all Dupes]

Well, as my spreadsheet may have 40,000+ rows I find going from the
first to the last can be time consuming/tedious. I do know how to get
to the bottom of the selection BUT I often forget it. :-(

(Nearly) all things which can be done manually can be done by macro, too.
You should ask within Excel related groups if you need an every-day
problem solved. I don't read any. So, unfortunately, I can't recommend
you one. But if you browse through the postings you should quickly get
an idea of the knowledge which can be found inside one or another...

To demonstrate that such things *can* be done by macro I post one which
should successfully remove all dupes of a coherent area. (I've adopted
some code I use for other / similar purposes and hope nothing got
wrong on that way.):

Sub RemoveAllDupes()
'
Dim regA, regB
Dim sFormula
Dim iOffset, iCnt
Set regA = ActiveCell.CurrentRegion
iOffset = IIf(MsgBox("Has table headers?", vbYesNo, _
"Decision needed") = vbYes, 1, 0)
Set regA = regA.Offset(iOffset, 0) _
.Resize(regA.Rows.Count - iOffset, regA.Columns.Count)
regA.Offset(0, regA.Columns.Count).Resize(1, 1).Select
Selection.EntireColumn.Insert
For iCnt = regA.Columns.Count To 1 Step -1
sFormula = sFormula & "RC[-" & iCnt & "]"
sFormula = IIf(iCnt > 1, sFormula & "&""|""&", sFormula)
Next
ActiveCell.FormulaR1C1 = "=" & sFormula
regA.Offset(0, regA.Columns.Count) _
.Resize(regA.Rows.Count, 1).Select
regA.Offset(0, regA.Columns.Count) _
.Resize(1, 1).Activate
Selection.FillDown
regA.Resize(regA.Rows.Count, regA.Columns.Count + 1).Select
Selection.Sort Key1:=regA.Offset(0, regA.Columns.Count) _
.Resize(1, 1), Order1:=xlAscending, _
Header:=xlNo, Orientation:=xlTopToBottom
regA.Offset(0, regA.Columns.Count + 1).Resize(1, 1).Select
Selection.EntireColumn.Insert
Selection.EntireRow.Insert
regA.Offset(0, regA.Columns.Count + 1).Resize(1, 1).Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-1]=R[-1]C[-1],RC[-1]=R[1]C[-1]),1,0)"
regA.Offset(0, regA.Columns.Count + 1) _
.Resize(regA.Rows.Count, 1).Select
regA.Offset(0, regA.Columns.Count + 1) _
.Resize(1, 1).Activate
Selection.FillDown
regA.Offset(-1, 0) _
.Resize(regA.Rows.Count + 1, regA.Columns.Count + 2).Select
Selection.AutoFilter Field:=regA.Columns.Count + 2, Criteria1:="1"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.AutoFilter
Selection.Delete (xlShiftUp)
regA.Offset(iOffset - 1, 0) _
.Resize(1, regA.Columns.Count + 2).Select
Selection.Insert Shift:=xlDown
regA.Offset(-1, regA.Columns.Count).Resize(1, 1).Select
Selection.EntireRow.Delete
Selection.EntireColumn.Delete
Selection.EntireColumn.Delete
regA.Resize(1, 1).Select
End Sub

It's not too beautiful, but it seems to work on Excel 97. Adjustments
for different outcomes should be easy. Ask some Excel Pro's to refine
the code if you need that.

BeAr

 

[Susan Bugher]

Important thing: **Don't let excel intimidate you**. If this is a
frequently repeated operation, you may be ready to try your hand at
macros. Unfortunately I'm not so good at that, although I have created
a few. I still use excel at work but have switched to OPenOffice at
home.

Hi Jerry,

I'm trying to work up my strength to switch to OpenOffice.org spreadsheets. Any minute now. . .
Is working with OO spreadsheets pretty similar to working with Excel? Any tips you care to pass along?

Susan
--
Posted to alt.comp.freeware
Search alt.comp.freeware (or read it online):
http://groups.google.no/groups?q=+group:alt.comp.freeware&hl=en
Pricelessware & ACF: http://www.pricelesswarehome.org
Pricelessware: http://www.pricelessware.org (not maintained)

 

[B. R. 'BeAr' Ederson]

On Sat, 2 Jul 2005 20:26:58 +0200, B. R. 'BeAr' Ederson wrote:

Grmpfl. One reference isn't correct. (Copied from another situation
where it was needed):

Selection.Delete (xlShiftUp)
regA.Offset(iOffset - 1, 0) _
.Resize(1, regA.Columns.Count + 2).Select
Selection.Insert Shift:=xlDown

Selection.Delete (xlShiftUp)
regA.Offset(0, 0) _
.Resize(1, regA.Columns.Count + 2).Select
Selection.Insert Shift:=xlDown

Hope, that's better now.

BeAr

 

[schrodinger's cat]

<snipped>

Hey, c'mon guys and gals, it's long past time to take this to private
email. After all, this ng is about freeware, and Excel is about as far
from freeware as you can get. While I understand and appreciate that
the people who frequent this group tend to be quite knowledgeable
about software, I don't think the ng should be turned into the
Universal Help Desk. I'm beggin' ya, please restrict discussions to
freeware. Thanks.

 

[Susan Bugher]

<snipped>

Hey, c'mon guys and gals, it's long past time to take this to private
email. After all, this ng is about freeware, and Excel is about as far
from freeware as you can get. While I understand and appreciate that
the people who frequent this group tend to be quite knowledgeable
about software, I don't think the ng should be turned into the
Universal Help Desk. I'm beggin' ya, please restrict discussions to
freeware. Thanks.

In defense of the thread (33 posts) - despite the title it started with an accolade to Sam Franke's
CSVEd Freeware app and it's now veering back into the freeware arena via OpenOffice.org. There has
been a bit of a detour along the way. . . but to a large extent a spreadsheet is a spreadsheet
is a spreadsheet. IOW - most of the discussion has been applicable to Freeware spreadsheets too
(Sphygmic Spreadsheet, OO.org etc.)

but you *do* have a point and I hear you.

Susan
--
Posted to alt.comp.freeware
Search alt.comp.freeware (or read it online):
http://groups.google.no/groups?q=+group:alt.comp.freeware&hl=en
Pricelessware & ACF: http://www.pricelesswarehome.org
Pricelessware: http://www.pricelessware.org (not maintained)

 

[John Fitzsimons]

Hey, c'mon guys and gals, it's long past time to take this to private
email. After all, this ng is about freeware, and Excel is about as far
from freeware as you can get. While I understand and appreciate that
the people who frequent this group tend to be quite knowledgeable
about software, I don't think the ng should be turned into the
Universal Help Desk. I'm beggin' ya, please restrict discussions to
freeware. Thanks.

I guess you don't know much about spreadsheets eh ? What has been
discussed here is not limited to Excel use. The ideas/principles can
be applied to other spreadsheets. Including freeware ones.

Had this only been a purely Excel discussion then I/others would have
marked it (OT).

In any case, if you don't want to read this thread then killfile it
and/or me as a poster.

Regards, John.

 

[John Fitzsimons]

John Fitzsimons wrote:

Easier: Select the *entire* column

Yes, thinking about it later I decided that that was the simplest way
to go.

by right-clicking on the column heading.

Normal clicking in the header does "selection" of columns in Excel.

Copy the column and
special paste it as "values" in the same or a different column. Works in QuattroPro - IIRC columns
can be copied and pasted in Excel too.

I just noticed that a right click will give me a context menu
including "Paste Special". Handy.

Lastly, when using autofilter I notice that I don't need the copy/
paste special step at all. I can sort on eg. "old" and "unique" quite
easily. In position.

Regards, John.