Solved Excel Help: Eliminate Duplicate Data Entries

September 23, 2013 at 07:53:03
Specs: Windows XP
I have a excel file showing mulitple names having multiple entries to site per day, I need to reduce the entries per person to one and per day.

See More: Excel Help: Eliminate Duplicate Data Entries

Report •

#1
September 23, 2013 at 10:23:02
This is just a posting tip...

When you ask for help in a forum such as computing.net, please try to use a subject line that is relevant to the contents of your post.

If everyone used a generic subject line such as "I need excel help" we wouldn't be able to tell one thread from another. Imagine an entire forum full of threads like:

I need excel help
Help With Excel Please
Please Help With Excel
etc.

I have edited your Subject Line to be more relevant to your question. Please try to follow this advice in the future...it will help make this forum a lot more efficient. Thanks!

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 23, 2013 at 10:31:52
It might help if we had an example of your data, before the "reduction" and what you want it to look like afterwards.

If you are going to post example data, plick click on the blue line at the end of this post and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#3
September 23, 2013 at 11:06:50
✔ Best Answer
Here's something that might work for you:

Let's say my data looks like this, with 3 entries for each name and date pair:

	  A	  B
1	Name	Date
2	Tom	1/2/2013
3	Dave	1/3/2013
4	Fred	1/2/2013
5	Bill	1/3/2013
6	Tom	1/2/2013
7	Dave	1/3/2013
8	Fred	1/2/2013
9	Bill	1/3/2013
10	Tom	1/2/2013
11	Dave	1/3/2013
12	Fred	1/2/2013
13	Bill	1/3/2013

Insert a new Column A, put this formula in A1 and drag it down:

=B1&C1

You should get this:

	A	 B	 C
1    NameDate	Name	Date
2    Tom41276	Tom	1/2/2013
3    Dave41277	Dave	1/3/2013
4    Fred41276	Fred	1/2/2013
5    Bill41277	Bill	1/3/2013
6    Tom41276	Tom	1/2/2013
7    Dave41277	Dave	1/3/2013
8    Fred41276	Fred	1/2/2013
9    Bill41277	Bill	1/3/2013
10   Tom41276	Tom	1/2/2013
11   Dave41277	Dave	1/3/2013
12   Fred41276	Fred	1/2/2013
13   Bill41277	Bill	1/3/2013

Now, Select A1:A13, do a Copy...PasteSpecial: Values

With A1:A13 still Selected, Click on Data...Advanced Filter.

Choose:
"Copy to another location"
"Copy To: D2"
"Unique Values".

You should get this:

	A	 B	 C	     D
1    NameDate	Name	Date	   NameDate
2    Tom41276	Tom	1/2/2013   Tom41276
3    Dave41277	Dave	1/3/2013   Dave41277
4    Fred41276	Fred	1/2/2013   Fred41276
5    Bill41277	Bill	1/3/2013   Bill41277
6    Tom41276	Tom	1/2/2013	
7    Dave41277	Dave	1/3/2013	
8    Fred41276	Fred	1/2/2013	
9    Bill41277	Bill	1/3/2013	
10   Tom41276	Tom	1/2/2013	
11   Dave41277	Dave	1/3/2013	
12   Fred41276	Fred	1/2/2013	
13   Bill41277	Bill	1/3/2013	

In E2, enter this formula:

=LEFT(D2,LEN(D2)-5)

In F2, enter this formula:

=TEXT(RIGHT(D2,5),"mm-dd-yyyy")

Drag this down to the bottom of the list in Column E and you should have this in Columns E & F:

      E          F
1		
2    Tom     01-02-2013
3    Dave    01-03-2013
4    Fred    01-02-2013
5    Bill    01-03-2013

Now you can do a Copy...PasteSpecial: Values on Columns E & F and you should be all set.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •
Related Solutions


Ask Question