Shorty Posted July 2, 2011 Share Posted July 2, 2011 (edited) Hi guys, I'm looking for a way to do the following: 1. Assign a code in one column based on another, eg: Food | Food_Code ----------------------- Chips | 42 Chips | Fish | 43 Fish | Peas | Chips | Fish | Peas | So lets say I have 2000 of these, I want to give all the Chips the code 42, all the fish 43 etc., all at once. 2. [sOLVED] Split a column at a forward slash, eg Old food name | Food 1 | Food 2 --------------------------------------- Fish/Chips | Fish | Chips Bread/Butter | Bread | Butter Bread/Butter | | Fish/Chips | | Orange/Lemon | | It can be done in a way which just splits the first column, rather than keeping it. Any experts about? Edited July 2, 2011 by Shorty Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 Hi guys, I'm looking for a way to do the following: 1. Assign a code in one column based on another, eg: Food | Food_Code ----------------------- Chips | 42 Chips | Fish | 43 Fish | Peas | Chips | Fish | Peas | So lets say I have 2000 of these, I want to give all the Chips the code 42, all the fish 43 etc., all at once. 2. Split a column at a forward slash, eg Old food name | Food 1 | Food 2 --------------------------------------- Fish/Chips | Fish | Chips Bread/Butter | Bread | Butter Bread/Butter | | Fish/Chips | | Orange/Lemon | | It can be done in a way which just splits the first column, rather than keeping it. Any experts about? I'm not 100% sure what you want but for 1 I would use "vlookup" and for 2 I would use "Data>text to columns" splitting on the slash character. Maybe I've oversimplified what you need? Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 (edited) Thanks, will try vlookup. To clarify, I know very little about excel. Did manage to find this formula on google: =LEFT(L4,FIND("/",L4,1)-1) to get the part left of the slash (L4 being the first cell with the slash), but the one for the right hasn't worked yet. Edit: this worked for the right side =RIGHT(A1,LEN(A1)-SEARCH("/",A1,1)) left with some errors I will have to look up to clean, but no problem How do I do this vlookup? Edited July 2, 2011 by Shorty Link to comment Share on other sites More sharing options...
Cube Posted July 2, 2011 Share Posted July 2, 2011 (edited) I'm not 100% sure what you want but for 1 I would use "vlookup" and for 2 I would use "Data>text to columns" splitting on the slash character.Maybe I've oversimplified what you need? For the first one, vLookup is perfect. I've done an example in Google Docs: Sample for 1. I'm not so sure about the second one. =VLOOKUP(A2,$H$6:$I$8,2) A2 = Cell with the text you're looking for $H$6:$I$8 = Where all the values are located 2 = The column in that array of values which you want to place in the cell. Edited July 2, 2011 by Cube Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 Thanks, will try vlookup. To clarify, I know very little about excel. Did manage to find this formula on google: =LEFT(L4,FIND("/",L4,1)-1) to get the part left of the slash (L4 being the first cell with the slash), but the one for the right hasn't worked yet. With the right one, it counts characters from the right so you'd have to use the Len function to find the length of the string. It would be something like =RIGHT(L4,LEN(L4)-FIND("/",L4,1)-1) or close to it anyways - you might have to adjust the -1 to +1 or not have it at all but I can't be bothered to work it out in my head! Edit: You fixed it yourself. I hate you. For the first one, vLookup is perfect. I've done an example in Google Docs: Sample for 1. I'm not so sure about the second one. =VLOOKUP(A2,$H$6:$I$8,2) A2 = Cell with the text you're looking for $H$6:$I$8 = Where all the values are located 2 = The column in that array of values which you want to place in the cell. Also stick a false in there as the last argument so that it only gives exact matches. Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 Can you explain that false thing please? :3 Remember: absolute rookie! Thanks for your help so far guys I didn't explain every aspect of my original problem and have run into a new issue - didn't account for foods which don't have a / in... really want to just copy those over to the adjacent cell (along with the ones split from the left). Can't find a good way to do it so just working through and dragging manually. Will try that vlookup when done with that! Link to comment Share on other sites More sharing options...
Cube Posted July 2, 2011 Share Posted July 2, 2011 Would it work if you added a / at the end of ones that don't have a /? Edit: And I realise doing so is lazy/bad coding. Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 It would but I don't know how to quickly do that to many cells anyway. It's ok, I'm at 1400 of 2500 now anyway Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 Have you finished your manual task yet? =IFERROR(LEFT(L4,FIND("/",L4,1)-1),L4) LOL. Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 Can't see what I'm doing wrong with the vlookup :3 Have added my data to that google doc, for the sake of simplicity using the same cells https://spreadsheets.google.com/spreadsheet/ccc?key=0AhHLWEzQopJOdGdxMmtYdmNHS2N3a2F3cFp2cy01YVE&hl=en_GB#gid=0 Link to comment Share on other sites More sharing options...
Cube Posted July 2, 2011 Share Posted July 2, 2011 (edited) The thing returning the wrong number is very strange. Do you have Excel and does it do the same in that? Edit: Oddy's thing above about the false is what was needed to be done. Edited July 2, 2011 by Cube Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 In Excel it seems to be ok. Main problem I'm having is that when I try to drag the formula down to every row, it changes the vlookup range down one. eg if I'm doing =VLOOKUP(N5,$U16:$V23,2) I drag it down and the next one is =VLOOKUP(N6,$U17:$V24,2) Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 Use VLOOKUP(N5,$U$16:$V$23,2) then drag. Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 (edited) That worked for a second set of data but for some reason still can't get it to work on the original ones Just getting #N/A Edit: Aha, done it. Thanks to whoever edited the google doc last, that ",FALSE" at the end seemed to solve my problem. I'm not clear on why, but thanks! Edited July 2, 2011 by Shorty Link to comment Share on other sites More sharing options...
Cube Posted July 2, 2011 Share Posted July 2, 2011 Edit: Aha, done it. Thanks to whoever edited the google doc last, that ",FALSE" at the end seemed to solve my problem. I'm not clear on why, but thanks! Oddy mentioned it above so I tried it. I haven't got a clue why it makes a difference, but it does. Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 Because I'm awesome. Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 (edited) Well thanks to you both you've helped me a lot! Got a new problem though I've got four columns of dates. Some have just year, some have year and month, some have year, month and date There are also some cells which have one of three types of non-date data, I have find-replaced them to AAA, BBB, CCC to keep them out of the way for now And I need all the dates to change to: 2007 2008-05 2008-05-14 example data Edited July 2, 2011 by Shorty Link to comment Share on other sites More sharing options...
Cube Posted July 2, 2011 Share Posted July 2, 2011 (edited) =DATE(LEFT(A1,4),RIGHT(A1,2),MID(A1,5,2)) works year, date and month but not the year/month and year values. Edit: Month/date were wrong way round, changed that. Edited July 2, 2011 by Cube Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 Yeah excel can just change format to date and leave me with similar, but it changes such as 2007 to 29/06/1905! Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 Use the if function to with the len function to determine how to treat it. Sorry, on my phone now so can't be much help. Link to comment Share on other sites More sharing options...
Rummy Posted July 2, 2011 Share Posted July 2, 2011 Late to the party but recently re-learned VLOOKUP and it's very handy, not sure if that's the true/false thing you didn't understand but FALSE forces and exact match, where TRUE somehow approximates(which i find paradoxical myself, and also I never use TRUE...yet). Yeah excel can just change format to date and leave me with similar, but it changes such as 2007 to 29/06/1905! I think that's because dates are essentially stored as a number, a number counted up from 01/01/1900 with an increment of one for each day, so to just change 2007 to a date it doesn't understand and works as if it's the date number, if that makes sense. I realise this isn't an answer/solution, but it made so much more sense to me once I understood. Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 The vlookup syntax is VLookup( value, table_array, index_number, not_exact_match ), so that's why you use false for exact matches. Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 OK guys, thanks for all your help again, couldn't have done this without you and you can believe me it's something important Just run into one last problem. The way a table exports is very different to how it needs to be imported. This one's a real stumper I don't know if there's any quick fix for it I have a table like this id 25 26 27 30 32 33 34 5 dog red 2 random 6 cat red 4 thing 7 mouse red 1 here 8 monkey blue 4 fish 9 rat blue 6 chips 10 tiger green 8 beans 11 horse red 2 bread but it needs to be id custom_field value 5 25 dog 5 26 red 5 27 2 5 30 random 5 32 5 33 5 34 6 25 cat 6 26 red 6 27 4 6 30 thing 6 32 6 33 6 34 Any ideas if it's possible to do this quickly at all? Link to comment Share on other sites More sharing options...
Mr_Odwin Posted July 2, 2011 Share Posted July 2, 2011 On my phone still. You need to reshape the data from wide to long format. using those key words might help you in google. Btw, I don't know if you can do this in excel, but I do it in my statistics packages loads. Had a look myself: http://mobile.experts-exchange.com/Q_26549515.html That may help. Link to comment Share on other sites More sharing options...
Shorty Posted July 2, 2011 Author Share Posted July 2, 2011 I might install Access, see if that helps. Thanks for the keyword prompt, will take a look at that tomorrow. Now stop helping from your phone! I feel like I'm disrupting your Saturday Link to comment Share on other sites More sharing options...
Recommended Posts