MS Excel Question

Factual Questions
1

I need to paste one large group of cells onto the end of another, but I want duplicate cells ignored in the pasting. In other words, want to paste Sheet 1 into Sheet 2:

Sheet 1:
Jack 3 14
Mary 8 17
Sue 1 4

Sheet 2:
Andy 5 1
Mitch 12 3
Mary 8 17

I want it to paste the Jack and Sue rows onto the end of sheet 2, but I don’t want it to duplicate the Mary row since it’s already there. It’s a substantial group of cells, so pulling out the duplicates by hand would be pretty time-consuming. Any way to do this?

2

I’d paste it all and then use the remove duplicates function. In 2010 Excel that’s under the Data tab.

3

What I often do would be this:
copy all the sheet 1 rows
paste at the end of sheet 2
sort sheet 2 byt the column you don’t want duplicated (let’s call it column A)
Insert a column to the right of column A
in column B1 (or whatever the first row is) make this statement: =if(a1=a2,1,0), copy and paste this cell into the remainder of column B.
Copy column B and paste it over itself as paste special…values.
sort by column B ascending
Delete all rows where column B=1
Now delete column B.

4

I think you’ll have to identify the duplicates and exclude them from the source selected or delete them post copy.

You can identify them pre-copy by trying a lookup into the target and only copying where the lookup returns an error.

In Excel 2007 and later there is a remove duplicates function that you could use post copy.

5

If you have Microsoft Access, the easiest thing would be to paste all of sheet 2 at the bottom of sheet 1, import the entire list into Access, build a query on that table using Group By to eliminate duplicates, then copy/paste the query results back into Excel.

6

Eesh, that’s a lot of steps for what you would think would be a standard function in Excel!

7

If it’s something you need to be doing a lot of, Excel isn’t really designed for that sort of thing. Keyed entries are database work.

On the other hand, BobLibDem’s method will work just fine, and if you need to consolidate 20 sheets into one, you can just paste them all into one sheet at the start and knock it all out at once.

If it’s something you need to be doing every day and you absolutely must use Excel, then I suppose you’ll have to get it down to muscle memory.

8

I use this exact method if I have to remove duplicates and have no other reason to put the data into Access.

Except my formula looks like this: =if(a1=a2,“del”,"") to eliminate the possibility that in the 30 secondes from typing the formula and doing the deleting I would forget which ones to delete.

9

You could use a formula in Sheet 2 to skip dupicate entries by using forumlas:

For the first Column, assuming you start at A1 in all sheets (pay close attention to the dollar sings):

=IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)-1=0,Sheet1!A1,"")

For the second columns and beyond use:

=IF($A1="","",Sheet1!B1)

You can drag the second column out to as many columns as you need and then you can drag the whole row down the entire sheet. If you don’t want to see the blank rows, you can simply copy and paste all data from Sheet 2 into Sheet 3 using Paste as Values, and then sort the data, eliminating any bank spaces.

It sounds tricky, put all you need to do is set it up once, enter data in Sheet 1, then copy and paste “as values” from 2 into 3 and sort. Pretty easy.

10

BobLibDem’s method is the one I would use first. It’s a 30 second task if you’re familiar with the process.

Did some testing with the remove duplicates function that jonesj2205 mentioned and it seemed to work fine.

I’m personally just more comfortable knowing what I’m deleting than letting excel decide. Must be getting old :rolleyes:

11

A good chunk of the work I do doesn’t allow for anything but assuming that formulas work as intended. I’m just used to it, I guess.

12

I’d use the BobLibDem method, too. Another option is Advanced Filter.

13

I use the BobLibDem method all the time. I also use Excel’s remove duplicate function if I’m working with a single column.

I guess with multiple columns I, like FasterThanMeerkats, want to see what I’m deleting in the adjoining columns.

And get off my lawn!

Nobody asked this but a pivot table variation of the method BobLibDem described is very useful for comparing two lists of unique names.
You take the first list (Ex Bob, Tim, Grace, Earl, Nancy) and the second list (Ex Bob, Donna, Ellen, Earl) and do the following
List 1 in column A and then in column B give a value of 1.
List 2 right below List 1 in Column A and in column B give a value of 2
So

Bob 1
Tim 1
Grace 1
Earl 1
Nancy 1
Bob 2
Donna 2
Ellen 2
Earl 2

If you put that through a pivot table and tell it to sum column B and then sort by B your results will be

Tim 1
Grace 1
Nancy 1
Donna 2
Ellen 2
Bob 3
Earl 3

Which gives you a continuous list holding three lists - the Names with 1 identify the members that exist only in group 1, Then names with 2 identify the members that exist only in group 2, and the names with a 3 identify the members that exist in both groups.

I have a monthly list of 2000 nodes that I have to update and this method quickly identifies new nodes, dropped nodes and continuing nodes.