Please help. Remove dups automaic of same name,price,month.

[Guest]

I have a spreadsheet called Book1 with 2000 lines.

Where there is a dup in rate , name and month I want to remove the dup
lines from the spreadsheet. Keeping just one line.
If the price is diff but same month, name and rate i want to keep in the
spreadsheet.


Please include necessary info to do this.


Column A Column B Column C
Jan Jane Doe $12
Jan Jane Doe $12
Feb Jane Doe $12
Mar 07 Jane Doe $14



Thanks

 

[Guest]

Click Data/Sort and sort by name/price/month. All of your dupes will be
displayed consecutively and you can delete the rows you no longer want.

 

[Guest]

Hi Kevin,

Thanks for your suggestion. I did that takes too long and I have 2000
lines. I'm wondering if there is a shortcut someone can help me out with.

 

[Guest]

Cynthia,

Here's a completely brute-force way that should take less time than deleting
each dup'ed line:

Assuming you only have the 3 columns of data, and they start on row 2, start
by sorting all of your lines as described by Kevin.

In cell D2 put this formula:
=IF(TRIM(A2)&TRIM(B2)&TRIM(C2)=TRIM(A3)&TRIM(B3)&TRIM(C3),1,0)

I used the TRIM function in case you have extraneous spaces in your data.
If the values in each of the cells are in any way different, they will sort
incorrectly - for instance, you have Mar 07 instead of Mar, so it will sort
out of sequence from the rest of the Mar values. It will also make this
method not work, so make sure your inputs are consistent.

In cell E2 put this formula: =IF($D2=1,"",TRIM(A2))

Copy and paste this formula into cells F2 and G2

Then copy cells E2:G2 and paste from cell E3 to the bottom of your data.

All of the dup'ed rows should be blank in columns E - G. Copy columns E - G
and Paste / Special / Values over to columns A - C. Then sort as you did
before. All the blank rows that are the dupes should sort out into one group
that you can delete all at once. That should take less time. Of course,
there are any number of other ways to do this, including filtering, so you
might want to explore the Help section a bit.

HTH...

 

[Dave Peterson]

Check your other post, too.

I have a spreadsheet called Book1 with 2000 lines.

Where there is a dup in rate , name and month I want to remove the dup
lines from the spreadsheet. Keeping just one line.
If the price is diff but same month, name and rate i want to keep in the
spreadsheet.

Please include necessary info to do this.

Column A Column B Column C
Jan Jane Doe $12
Jan Jane Doe $12
Feb Jane Doe $12
Mar 07 Jane Doe $14



Thanks

 

[Guest]

Hi Tom,

Thanks for responding to my email.

I tried the method and I get in one column 0's and 39235 in other column I
don't understand what that means.

I tried =IF(TRIM(A2)&TRIM(B2)&TRIM(C2)=TRIM(A3)&TRIM(B3)&TRIM(C3),1,0) and I
get 0's.

In cell E2 put this formula: =IF($D2=1,"",TRIM(A2)) I get 39235

 

[Guest]

Hi Cynthia,

You got that result because what you have in Column A is not the month , but
a date formatted to show only the month. Remember, I said if any of the
values are different in any way, this method would not work. The values in
Column A are all different because they are really dates, which Excel sees as
a 5 digit number. Check Dave Peterson's response to your other question and
try his method. You might have more luck with his method than with my mine.

Good Luck!