| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 88175 | 2008-03-17 23:56:00 | Excel Formula to put test in column based on keyword in another coumn | Morgenmuffel (187) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 650330 | 2008-03-17 23:56:00 | Ok i have a speadsheet which has data like this Part no - Description - price - extended description bobknife- Bobs Knife - 2.30 bobfork - Bobs fork - 3.56 bobpick - Bobs pick - 3.56 bobsalver - bobssalver - 9.00 Jimknife- Jims Knife - 2.30 Jimfork - Jims fork - 3.56 Jimsalver - Jimssalver - 9.00 Tomknife- Toms Knife - 2.30 Tomfork - Toms fork - 3.56 Tompick - Toms pick - 3.56 Tomsalver - Tomssalver - 9.00 Joeknife- Joes Knife - 2.30 Joefork - Joes fork - 3.56 Joesalver - Joessalver - 9.00 I want to set it so that where it says fork, the extra description field fills in the fork information, where it says pick it has the extra pick info etc there are 300 or so items in the list and i can do some of them just by sorting on price, however for the fork and pick there is n way i can sort the two except by going manually one by one |
Morgenmuffel (187) | ||
| 650331 | 2008-03-18 00:57:00 | "I want to set it so that where it says fork, the extra description field fills in the fork information, where it says pick it has the extra pick info etc" Can you give an example of what the Extended Description field would contain for Fork? "there are 300 or so items in the list and i can do some of them just by sorting on price, however for the fork and pick there is n way i can sort the two except by going manually one by one" Have you tried sorting by Price then Part No using the Data/Sort menu option? Cheers. Roger |
RogerRamjet (7055) | ||
| 650332 | 2008-03-18 01:07:00 | "there are 300 or so items in the list and i can do some of them just by sorting on price, however for the fork and pick there is n way i can sort the two except by going manually one by one" Have you tried sorting by Price then Part No using the Data/Sort menu option? Cheers. Roger Yes i have, infact thats how i have been able to do some of the other items the problem with sorting by price then part number is that the parts are still mixed eg from my previous example if they were sorted by price and partnumber bobfork - Bobs fork - 3.56 bobpick - Bobs pick - 3.56 Jimfork - Jims fork - 3.56 Joefork - Joes fork - 3.56 Tomfork - Toms fork - 3.56 Tompick - Toms pick - 3.56 |
Morgenmuffel (187) | ||
| 650333 | 2008-03-18 01:13:00 | OK I see. So, can you give an example of what the Extended Description field would contain for Fork? For Knife? |
RogerRamjet (7055) | ||
| 650334 | 2008-03-18 01:24:00 | the knife is blue the fork is red it doesn't really matter what goes in the extended description at this point as long as i put something different for fork and knife, i can do a find replace later to put the correct text in, at the moment i am just trying to figure out a quick way of doing it, I'm sure I used to do this using either filters or else if statements with wildcards but i just can't remember, either way just doesn't work as i remember Thanks |
Morgenmuffel (187) | ||
| 650335 | 2008-03-18 03:22:00 | Hi Nigel Try this. Set up another worksheet in the same workbook and rename it to List. Enter the items in the new worksheet as follows. Col A Col B fork The fork is red Knife The knife is blue pick The pick is green salver The salver is black The items in Col A need to be in alphabetical order. In your original sheet, in Cell D2 under the heading Extended Description, enter the following formula; =LOOKUP(RIGHT(B2,LEN(B2)-FIND(" ",B2)),List!$A$1:$A$4,List!$B$1:$B$4) I'm sure there's a better way but this might do for you. Cheers. Roger |
RogerRamjet (7055) | ||
| 650336 | 2008-03-18 08:19:00 | Hi Nidel The following formula in a helper cell (e.g. column D etc) will help provide a field that you can sort on, assuming your data is as described: =IF(ISERROR(SEARCH(" fork ",A1)), IF(ISERROR(SEARCH(" pick ",A1)), "other", "pick"), "fork") Andrew |
andrew93 (249) | ||
| 650337 | 2008-03-19 01:58:00 | If you do /Data/Filter/Autofilter on your columns of data you can Custom filter on the first column for all the forks (Column A) using the "Contains" qualifier then typing fork in the requester box. All rows with the letters "fork" will be filtered. If all the filtered cells are selected they can be copied to another sheet. If the Custom filter is changed to "Contains" pick - all the cells in Column A containing "pick" will be filtered. These cells can be copied to the bottom of the previously copied "fork" list or to some other destination. HTH |
rad_s4 (7401) | ||
| 650338 | 2008-03-19 18:10:00 | Thanks all I'll try these out later on today, the answers are all making the grey matter remember lessons from the dim dark past |
Morgenmuffel (187) | ||
| 1 | |||||