software

Hi

I'm having a bit of a problem with a spreadsheet I'm working on. The
setup is I have two columns of strings and I need to put a different
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 a
VLOOKUP function.

The sheets come to me pre-done, and I need to keep the workings all on
a single sheet really.

Anyone get any ideas? it's stumped me for ages, and currently the only
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.

Thanks--
Squeaker1066
------------------------------------------------------------------------
Squeaker1066's Profile: www.excelforum.com/member.php...oamp;userid=32667
View this thread: www.excelforum.com/showthread...hreadid=524734
I think we need more info, what kind of strings and what do you want the
final outcome to be?--
intruder9
------------------------------------------------------------------------
intruder9's Profile: www.excelforum.com/member.php...oamp;userid=30107
View this thread: www.excelforum.com/showthread...hreadid=524734
Ok, the strings can be numbers, letters, or a combination, but all
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?--
Squeaker1066
------------------------------------------------------------------------
Squeaker1066's Profile: www.excelforum.com/member.php...oamp;userid=32667
View this thread: www.excelforum.com/showthread...hreadid=524734
Could you use something like this:

=IF(AND($A:$A=quot;Helloquot;,$B:$B=quot;Gilesquot;),quot;Greetingquot;)

You would need a different statement for each pairing of strings you
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 work--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: www.excelforum.com/member.php...oamp;userid=31214
View this thread: www.excelforum.com/showthread...hreadid=524734
yes, I can see that could work, but there are dozens of combinations. I
was hoping for something... neater. --
Squeaker1066
------------------------------------------------------------------------
Squeaker1066's Profile: www.excelforum.com/member.php...oamp;userid=32667
View this thread: www.excelforum.com/showthread...hreadid=524734Please try to explain the pattern/rules in plain text!

Stefi

?queaker1066??ezt ?rta:

gt;
gt; Ok, the strings can be numbers, letters, or a combination, but all
gt; treated as strings, not values.
gt;
gt; An example,
gt;
gt; Predefined Col A: AXUK205805
gt; predefined Col B: (empty)
gt; results col C: Possible Circuit
gt;
gt; Predefined col A: BUSHI LIBA000768
gt; Predefined col B: 01589872568
gt; results in col C: Possible ISDN/Pair Gain
gt;
gt; prefedined Col A: UNABLE TO LOCATE
gt; predefined col B: FAULTY PAIR
gt; results in col C: Faulty - Unknown
gt;
gt; predefined col A: LIC038963
gt; predefined col B: 04898589874
gt; results in col c: 04898589874
gt;
gt; I know it seems a little random, but there is a pattern to it!
gt;
gt; Does this make it clearer or less so?
gt;
gt;
gt; --
gt; Squeaker1066
gt; ------------------------------------------------------------------------
gt; Squeaker1066's Profile: www.excelforum.com/member.php...oamp;userid=32667
gt; View this thread: www.excelforum.com/showthread...hreadid=524734
gt;
gt;


Paste into column C

=IF(AND($A:$A=quot;AXUK20580quot;,$B:$B=quot;quot;),quot;Possible Circuitquot;)

Pull down column C

Paste into column D

=IF(AND($A:$A=quot;BUSHI LIBA000768quot;,$B:$B=quot;01589872568quot;),quot;Possible
ISDN/Pair Gainquot;)

Paste into column E

=IF(AND($A:$A=quot;UNABLE TO LOCATEquot;,$B:$B=quot;FAULTY PAIRquot;),quot;Faulty -
Unknownquot;)

and so on, then merge the columns at the end.

Like I said messy, and I'm sure someone else can come up with something
better--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: www.excelforum.com/member.php...oamp;userid=31214
View this thread: www.excelforum.com/showthread...hreadid=524734
I've been trying to head towards a solution where I can list all the
posibilities in a table, then put a formula in the results column that
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?--
Squeaker1066
------------------------------------------------------------------------
Squeaker1066's Profile: www.excelforum.com/member.php...oamp;userid=32667
View this thread: www.excelforum.com/showthread...hreadid=524734Squeaker,

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 amp;
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 important
bit. The letters in the predefined columns are constant, but the numbers
can be anything, so I need a way to just match those constant strings
(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 boss
that!

Ok, a plain text version of the rules. If column A contains a certain
string, and column B contains a certain string, then column C will be
another certain string.

Is that what you were after Stefi?--
Squeaker1066
------------------------------------------------------------------------
Squeaker1066's Profile: www.excelforum.com/member.php...oamp;userid=32667
View this thread: www.excelforum.com/showthread...hreadid=524734