 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006Please help, I've been struggling for weeks!
Hi I'm having a bit of a problem with a spreadsheet I'm working on. Th setup is I have two columns of strings and I need to put a differen string in a third column depending on the contents of the first two All this is on 1000+ rows. However, there are many different strings, too many for an IF function and as the results depend on two columns, I don't think I can use VLOOKUP function. The sheets come to me pre-done, and I need to keep the workings all o a single sheet really. Anyone get any ideas? it's stumped me for ages, and currently the onl way to do it is line by line... 1000+ rows per sheet? dozens of sheets I don't think so! I'd really appreciate any help people can give me with this. Thank I think we need more info, what kind of strings and what do you want th final outcome to be Ok, the strings can be numbers, letters, or a combination, but al treated as strings, not values. An example, Predefined Col A: AXUK205805 predefined Col B: (empty) results col C: Possible Circuit Predefined col A: BUSHI LIBA000768 Predefined col B: 01589872568 results in col C: Possible ISDN/Pair Gain prefedined Col A: UNABLE TO LOCATE predefined col B: FAULTY PAIR results in col C: Faulty - Unknown predefined col A: LIC038963 predefined col B: 04898589874 results in col c: 04898589874 I know it seems a little random, but there is a pattern to it! Does this make it clearer or less so Please try to explain the pattern/rules in plain text! Stefi „Squeaker1066” ezt írta: > Ok, the strings can be numbers, letters, or a combination, but all > treated as strings, not values. [quoted text clipped - 20 lines] > > Does this make it clearer or less so? >Ok, the strings can be numbers, letters, or a combination, but all >treated as strings, not values. [quoted text clipped - 20 lines] > >Does this make it clearer or less so? From what you post, the possible results in col C seem to have a random relationship to the contents of Col A. You could set up a lookup table. In the Top Row list the Col A possibilities In the First Column list the Col B possibilities In the remaining cells list the Col C result for each intersection of Col A & B. For example, you could have your list of Col A contents in J1:M1 Col B Contents in I2:I5 Col C contents in J2:M5 Then use the formula: =INDEX($J$2:$M$5,MATCH(B1,$I$2:$I$5,0),MATCH(A1,$J$1:$M$1,0)) Note that you will have to make a special case for the <blank> as that will give an error with the MATCH function. Perhaps substitute a <space> for it. --ron Could you use something like this: =IF(AND($A:$A="Hello",$B:$B="Giles"),"Greeting") You would need a different statement for each pairing of strings yo wish to find, in a different column, so columns C, D, E, ..... Then you could merge the columns at the end. Not very neat I knwo but could wor yes, I can see that could work, but there are dozens of combinations. I was hoping for something... neater. :)  SignatureSqueaker1066 Paste into column C =IF(AND($A:$A="AXUK20580",$B:$B=""),"Possible Circuit") Pull down column C Paste into column D =IF(AND($A:$A="BUSHI LIBA000768",$B:$B="01589872568"),"Possibl ISDN/Pair Gain") Paste into column E =IF(AND($A:$A="UNABLE TO LOCATE",$B:$B="FAULTY PAIR"),"Faulty Unknown") and so on, then merge the columns at the end. Like I said messy, and I'm sure someone else can come up with somethin bette I've been trying to head towards a solution where I can list all th posibilities in a table, then put a formula in the results column tha says if col a and col b on the sheet match col a and b on the table the result is col c from the table. Is that possible Squeaker, I think you might be able to use a Vlookup, you just need to concatenate the two key columns in you original data i.e. c1 = (A1 & B1), do a copy|paste special on c1 and you have your key for searching in your Lookup table. Of course, this does mean that you need to create the entire list of combinations in the lookup :( Obviously if you have a copy of Access to hand, then things would be a lot easier (and you wouldn't be using a spreadsheet as a database!!) Chris I've just re-read my above post, and realised I've left out an importan bit. The letters in the predefined columns are constant, but the number can be anything, so I need a way to just match those constant string (such as BUSHI or AXUK) and check those against a table. Yeah, I know this'd be easier in Access, but you try telling my bos that! Ok, a plain text version of the rules. If column A contains a certai string, and column B contains a certain string, then column C will b another certain string. Is that what you were after Stefi Well, I think you realise now that you will need to build up a table which has the possible strings from column A and the possible strings from column B joined together, and in a column next to this you will need to define the string that you want to return for each combination. Then in your main sheet you will be able to use a simple lookup formula which can be copied down 1000 rows. It would help if the constant strings could all be the same length (eg 4 characters). Hope this helps. Pete |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.