Home |
Search |
Today's Posts |
#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 - |
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) |