Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help. Remove dups automaic of same name,price,month.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help. Remove dups automaic of same name,price,month.
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. -- Kevin Backmann "CYNTHIA" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help. Remove dups automaic of same name,price,month.
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. "Kevin B" wrote: 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. -- Kevin Backmann "CYNTHIA" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help. Remove dups automaic of same name,price,month.
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)&T RIM(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... "CYNTHIA" wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "CYNTHIA" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help. Remove dups automaic of same name,price,month.
Check your other post, too.
CYNTHIA wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help. Remove dups automaic of same name,price,month.
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)&T RIM(C3),1,0) and I get 0's. In cell E2 put this formula: =IF($D2=1,"",TRIM(A2)) I get 39235 "Tom Hayakawa" wrote: 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)&T RIM(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... "CYNTHIA" wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "CYNTHIA" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help. Remove dups automaic of same name,price,month.
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! "CYNTHIA" wrote: 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)&T RIM(C3),1,0) and I get 0's. In cell E2 put this formula: =IF($D2=1,"",TRIM(A2)) I get 39235 "Tom Hayakawa" wrote: 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)&T RIM(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... "CYNTHIA" wrote: 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. "Kevin B" wrote: 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. -- Kevin Backmann "CYNTHIA" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
Remove old month from chart add new/no manual reference change | Charts and Charting in Excel | |||
calculate/convert volume price to monthly average price | Excel Worksheet Functions | |||
automaic "nice" display of the labels in a pie chart | Charts and Charting in Excel | |||
How do I get the average price per bid for an individual month? | Excel Discussion (Misc queries) |