Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
An Excel Spreadsheet is set up like this with hundreds of duplicate names and
addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Insert a new column B between the name and address columns and put
this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Hi Roger,
I don't understand. How come you posted before me, but your time says 10:44 when mine says 10:17 and it is only 10;20 now? Pete On Jul 6, 10:44 am, "Roger Govier" wrote: Hi One way (work on a copy of your data, just in case you get something wrong!!) Insert a new column at B, so you have a blank column to the right of the Name. Mark column A, DataText to ColumnsDelimitedmark Comma as DelimiterFinish. Delete column B Mark your range of data, DataFilterAdvanced FilterCopy to new location and choose a column beyond the end of your existing dataclick Unique values onlyFinish. Delete the original columns -- Regards Roger Govier "Baffy" wrote in message ... An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Hi
One way (work on a copy of your data, just in case you get something wrong!!) Insert a new column at B, so you have a blank column to the right of the Name. Mark column A, DataText to ColumnsDelimitedmark Comma as DelimiterFinish. Delete column B Mark your range of data, DataFilterAdvanced FilterCopy to new location and choose a column beyond the end of your existing dataclick Unique values onlyFinish. Delete the original columns -- Regards Roger Govier "Baffy" wrote in message ... An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Hi Pete
I'm ahead of myself!!!<bg Twice recently, my time clock has got out of sync, and set itself ahead of current time. It was reading 11:52. I forced it to do a time sync with time.windows.com and it has returned it to the correct time. Apologies for the confusion. -- Regards Roger Govier "Pete_UK" wrote in message oups.com... Hi Roger, I don't understand. How come you posted before me, but your time says 10:44 when mine says 10:17 and it is only 10;20 now? Pete On Jul 6, 10:44 am, "Roger Govier" wrote: Hi One way (work on a copy of your data, just in case you get something wrong!!) Insert a new column at B, so you have a blank column to the right of the Name. Mark column A, DataText to ColumnsDelimitedmark Comma as DelimiterFinish. Delete column B Mark your range of data, DataFilterAdvanced FilterCopy to new location and choose a column beyond the end of your existing dataclick Unique values onlyFinish. Delete the original columns -- Regards Roger Govier "Baffy" wrote in message ... An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Ah, so the time of posting is picked up from each poster's system
time, not from the time maintained by the newsgroups? Learn something new everyday ... Thanks Roger. Pete On Jul 6, 11:16 am, "Roger Govier" wrote: Hi Pete I'm ahead of myself!!!<bg Twice recently, my time clock has got out of sync, and set itself ahead of current time. It was reading 11:52. I forced it to do a time sync with time.windows.com and it has returned it to the correct time. Apologies for the confusion. -- Regards Roger Govier "Pete_UK" wrote in message oups.com... Hi Roger, I don't understand. How come you posted before me, but your time says 10:44 when mine says 10:17 and it is only 10;20 now? Pete On Jul 6, 10:44 am, "Roger Govier" wrote: Hi One way (work on a copy of your data, just in case you get something wrong!!) Insert a new column at B, so you have a blank column to the right of the Name. Mark column A, DataText to ColumnsDelimitedmark Comma as DelimiterFinish. Delete column B Mark your range of data, DataFilterAdvanced FilterCopy to new location and choose a column beyond the end of your existing dataclick Unique values onlyFinish. Delete the original columns -- Regards Roger Govier "Baffy" wrote in message ... An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Thanks Pete. But nothing is moving I get the column copied all the way down
and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
I assumed from your example that the names were in column A, but if
they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Hi Pete:
Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Hi again:
I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Yes. Something is really screwed up. I keep filtering it, I notice names are
disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
When I do Data | Filter | Advanced Filter I usually select in the pull-
down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Here's how it looks/what I do:
I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Actually what the message says prciselyis "not a valid reference or defined
name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
You need a cell reference in the Copy To box - leave yourself a blank
column and make it F1. Hope this helps. Pete On Jul 7, 3:24 pm, Baffy wrote: Actually what the message says prciselyis "not a valid reference or defined name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
I'm going to throw in the towel on this one Pete. It just is not
transferring to a new column no matter what I do. But I do have one question which continues to baffle me. If I have 920 records before I execute the formula, how can I have 920 reocords AFTER I execute the "date/Filter etc" formula. After I execute the formula that duplicate names have disappeared. And so, how on earth can the record number count be the same? "Pete_UK" wrote: You need a cell reference in the Copy To box - leave yourself a blank column and make it F1. Hope this helps. Pete On Jul 7, 3:24 pm, Baffy wrote: Actually what the message says prciselyis "not a valid reference or defined name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
I'm not sure where you are getting the record number count from. How
many rows did you have before, and how many rows do you have after applying the advanced filter? If they are the same then either you didn't apply the filter correctly or you had no duplicates. Hope this helps. Pete On Jul 8, 3:06 pm, Baffy wrote: I'm going to throw in the towel on this one Pete. It just is not transferring to a new column no matter what I do. But I do have one question which continues to baffle me. If I have 920 records before I execute the formula, how can I have 920 reocords AFTER I execute the "date/Filter etc" formula. After I execute the formula that duplicate names have disappeared. And so, how on earth can the record number count be the same? "Pete_UK" wrote: You need a cell reference in the Copy To box - leave yourself a blank column and make it F1. Hope this helps. Pete On Jul 7, 3:24 pm, Baffy wrote: Actually what the message says prciselyis "not a valid reference or defined name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Upon closer look, there are few rows after the "advanced filter". I followed
the numbering down the left had side and noticed that some numbers were missing, such as 1-2-4-7-8-11- and so forth. It was just that, once you get to the bottom, the total number was the same. I'll let you know if I ever figure out how to successfully transport the "advanced filter results" to a different column. Thanks for all your help and your patience. "Pete_UK" wrote: I'm not sure where you are getting the record number count from. How many rows did you have before, and how many rows do you have after applying the advanced filter? If they are the same then either you didn't apply the filter correctly or you had no duplicates. Hope this helps. Pete On Jul 8, 3:06 pm, Baffy wrote: I'm going to throw in the towel on this one Pete. It just is not transferring to a new column no matter what I do. But I do have one question which continues to baffle me. If I have 920 records before I execute the formula, how can I have 920 reocords AFTER I execute the "date/Filter etc" formula. After I execute the formula that duplicate names have disappeared. And so, how on earth can the record number count be the same? "Pete_UK" wrote: You need a cell reference in the Copy To box - leave yourself a blank column and make it F1. Hope this helps. Pete On Jul 7, 3:24 pm, Baffy wrote: Actually what the message says prciselyis "not a valid reference or defined name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
One final attempt to describe it to you, then...
Suppose you have data in columns A to D extending to row 4000 with a header row in row 1. You need to highlight all the data including the header (i.e. A1:D4000) then click on Data | Filter | Advanced Filter. In the pop-up, the "List range" should already be filled in from your highlighted range (NOTE: not always, if you have used advanced filter on that sheet before). You need to click "Unique records only" and "Copy to another location", and in this case you must tell Excel where - just type F1 in the "Copy to" panel. Then when you click OK you should see a reduced set of data from A1 onwards - you might only have 2000 records in this set. You can then delete the original columns A to E to leave you with your reduced set. Hope this helps. Pete On Jul 8, 4:44 pm, Baffy wrote: Upon closer look, there are few rows after the "advanced filter". I followed the numbering down the left had side and noticed that some numbers were missing, such as 1-2-4-7-8-11- and so forth. It was just that, once you get to the bottom, the total number was the same. I'll let you know if I ever figure out how to successfully transport the "advanced filter results" to a different column. Thanks for all your help and your patience. "Pete_UK" wrote: I'm not sure where you are getting the record number count from. How many rows did you have before, and how many rows do you have after applying the advanced filter? If they are the same then either you didn't apply the filter correctly or you had no duplicates. Hope this helps. Pete On Jul 8, 3:06 pm, Baffy wrote: I'm going to throw in the towel on this one Pete. It just is not transferring to a new column no matter what I do. But I do have one question which continues to baffle me. If I have 920 records before I execute the formula, how can I have 920 reocords AFTER I execute the "date/Filter etc" formula. After I execute the formula that duplicate names have disappeared. And so, how on earth can the record number count be the same? "Pete_UK" wrote: You need a cell reference in the Copy To box - leave yourself a blank column and make it F1. Hope this helps. Pete On Jul 7, 3:24 pm, Baffy wrote: Actually what the message says prciselyis "not a valid reference or defined name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Any chance of emailing it to you as an attachment?
"Pete_UK" wrote: One final attempt to describe it to you, then... Suppose you have data in columns A to D extending to row 4000 with a header row in row 1. You need to highlight all the data including the header (i.e. A1:D4000) then click on Data | Filter | Advanced Filter. In the pop-up, the "List range" should already be filled in from your highlighted range (NOTE: not always, if you have used advanced filter on that sheet before). You need to click "Unique records only" and "Copy to another location", and in this case you must tell Excel where - just type F1 in the "Copy to" panel. Then when you click OK you should see a reduced set of data from A1 onwards - you might only have 2000 records in this set. You can then delete the original columns A to E to leave you with your reduced set. Hope this helps. Pete On Jul 8, 4:44 pm, Baffy wrote: Upon closer look, there are few rows after the "advanced filter". I followed the numbering down the left had side and noticed that some numbers were missing, such as 1-2-4-7-8-11- and so forth. It was just that, once you get to the bottom, the total number was the same. I'll let you know if I ever figure out how to successfully transport the "advanced filter results" to a different column. Thanks for all your help and your patience. "Pete_UK" wrote: I'm not sure where you are getting the record number count from. How many rows did you have before, and how many rows do you have after applying the advanced filter? If they are the same then either you didn't apply the filter correctly or you had no duplicates. Hope this helps. Pete On Jul 8, 3:06 pm, Baffy wrote: I'm going to throw in the towel on this one Pete. It just is not transferring to a new column no matter what I do. But I do have one question which continues to baffle me. If I have 920 records before I execute the formula, how can I have 920 reocords AFTER I execute the "date/Filter etc" formula. After I execute the formula that duplicate names have disappeared. And so, how on earth can the record number count be the same? "Pete_UK" wrote: You need a cell reference in the Copy To box - leave yourself a blank column and make it F1. Hope this helps. Pete On Jul 7, 3:24 pm, Baffy wrote: Actually what the message says prciselyis "not a valid reference or defined name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma for all records, i.e. ,Joe and , Mary. 2: How do I eliminate duplicate addresses. My goal is to have one entry as follows: Name Address Smith 10 S. Oak And eliminate duplicate addresses and first name leaving only one last name to each address. Can it be done?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Yes, go on then. My address is as follows:
pashurst [at] auditel.net Pete On Jul 10, 10:36 pm, Baffy wrote: Any chance of emailing it to you as an attachment? "Pete_UK" wrote: One final attempt to describe it to you, then... Suppose you have data in columns A to D extending to row 4000 with a header row in row 1. You need to highlight all the data including the header (i.e. A1:D4000) then click on Data | Filter | Advanced Filter. In the pop-up, the "List range" should already be filled in from your highlighted range (NOTE: not always, if you have used advanced filter on that sheet before). You need to click "Unique records only" and "Copy to another location", and in this case you must tell Excel where - just type F1 in the "Copy to" panel. Then when you click OK you should see a reduced set of data from A1 onwards - you might only have 2000 records in this set. You can then delete the original columns A to E to leave you with your reduced set. Hope this helps. Pete On Jul 8, 4:44 pm, Baffy wrote: Upon closer look, there are few rows after the "advanced filter". I followed the numbering down the left had side and noticed that some numbers were missing, such as 1-2-4-7-8-11- and so forth. It was just that, once you get to the bottom, the total number was the same. I'll let you know if I ever figure out how to successfully transport the "advanced filter results" to a different column. Thanks for all your help and your patience. "Pete_UK" wrote: I'm not sure where you are getting the record number count from. How many rows did you have before, and how many rows do you have after applying the advanced filter? If they are the same then either you didn't apply the filter correctly or you had no duplicates. Hope this helps. Pete On Jul 8, 3:06 pm, Baffy wrote: I'm going to throw in the towel on this one Pete. It just is not transferring to a new column no matter what I do. But I do have one question which continues to baffle me. If I have 920 records before I execute the formula, how can I have 920 reocords AFTER I execute the "date/Filter etc" formula. After I execute the formula that duplicate names have disappeared. And so, how on earth can the record number count be the same? "Pete_UK" wrote: You need a cell reference in the Copy To box - leave yourself a blank column and make it F1. Hope this helps. Pete On Jul 7, 3:24 pm, Baffy wrote: Actually what the message says prciselyis "not a valid reference or defined name" "Baffy" wrote: Here's how it looks/what I do: I click on Copy to Another Location The List Range box is auto-filled The Criteria Range box is blank In the Copy To box I type in E Note that I have reduced the spreadsheet to four columns A - B - C - D I click on UNIQUE I get this message: Not a valid record...... "Pete_UK" wrote: When I do Data | Filter | Advanced Filter I usually select in the pull- down to filter to another location (eg M1 in the same sheet). That way the original data remains and if I'm happy with the reduced data set then I can delete the original columns of data and use File | Save As to save the new dataset with a new name. As regards one of your earlier postings, I presume you discovered how to fix the values with Edit | Paste Special before deleting the column with the combined names in. Hope this helps. Pete On Jul 7, 8:42 am, Baffy wrote: Yes. Something is really screwed up. I keep filtering it, I notice names are disappearing, but the file count remains the same on the before and after filter databases. Frustrating. "Baffy" wrote: Hi again: I figured the last one out. I was entering the equation on the wrong line. However, I have yet another strange outcome. I can plainly see that many of the entries have been deleted once I do the Data - Filter - Advanced Filter etc. However, when I compare the number of entries (files?) on the pre-filtered database and the post filtered database, they equal one another.....23,760 to be exact. How can this possibly be? "Baffy" wrote: Hi Pete: Believe it or not, I actually figured it out before you got back to me. I'm a little slow on the switch, but the switch does eventually get pulled. This time I have a rather unusual an unexpected problem. When I double click on that little black box to which you referred, all the last names immediately appear in the column. I don't have to go to EDIT etc. It all happens immediately. The problem is, however, I can't get rid of the column with both the first and last names in it. If I try to delete it, it also deletes the new column with all the last names in it AND replaces all the names with #REF. What do you think that is all about? "Pete_UK" wrote: I assumed from your example that the names were in column A, but if they are actually in column B then you will need to change the formula to: =LEFT(B2,FIND(",",B2)-1) and put it in C2. With the cursor in C2 once you have entered the formula, then you can double-click the fill-handle and the formula will be copied down column C for as many entries as you have in column B (assuming contiguous data). I'm not sure what you have in column A, but the rest of it should work if you remember to transpose the columns in my description to your reality. Hope this helps. Pete On Jul 7, 12:06 am, Baffy wrote: Thanks Pete. But nothing is moving I get the column copied all the way down and follow your =LEFT(A2,FIND(",",A2)-1) and the rest of your directions and a small outline of a cross appears and nothing happens. Possibly it has something to do with the fact that when I insert a column between the names and the address which creates a new column C. The names continue to appear under column B and the addresses shift to column D when I create the new column C. Do you suppose the equation needs to be changed based on this fact? "Pete_UK" wrote: Insert a new column B between the name and address columns and put this formula in B2: =LEFT(A2,FIND(",",A2)-1) This will give you the name to the left of the first comma. Copy this formula down column B by double-clicking the fill handle (the small black square at the bottom right of the cursor), then fix the values by <copy then Edit | Paste Special | Values (check) | OK and <Enter. You can then delete column A and put "Name" as a header in A1. Now highlight all the data and headings from A1 to D-whatever and click on Data | Filter | Advanced filter and in the pop-up you can check Unique Records only (and I prefer to select a different location - eg F1). Click OK and you will have your reduced list (no duplicates) in columns F onwards. If you are happy with this then you can delete columns A to E and then do File | Save As to save the file with a different name (so you still have the original if you want to get back to it). Hope this helps. Pete On Jul 6, 9:52 am, Baffy wrote: An Excel Spreadsheet is set up like this with hundreds of duplicate names and addresses: Name Address City Zip Etc. Smith, Joe 10 S. Oak Smith, Mary 10 S. Oak QUESTIONS: 1: How do I eliminate the comma and everything to the right of the comma ... read more »- Hide quoted text - - Show quoted text - |
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Spreadsheet Manipulation
Hi Pete:
New Question. I'm hoping you can guide me through the solution. I have two columns (A and B) on my excel sheet (13000 records) Column A is: John Smith I want to put the John in Column A and Smith in Column B Column B is: 1240 Main Street I want to put 1240 in Column C and Main Street in Column D Do-able? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Manipulation - Excel 2000 | Excel Discussion (Misc queries) | |||
Help Excel Data manipulation Pros: Something like a Vlookup with a Sum Function | Excel Worksheet Functions | |||
Complex calculation and manipulation in Excel | New Users to Excel | |||
Excel Worksheet manipulation | Excel Discussion (Misc queries) | |||
Excel Time Manipulation | Excel Discussion (Misc queries) |