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: http://www.excelforum.com/member.php...o&userid=32667
View this thread: http://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: http://www.excelforum.com/member.php...o&userid=30107
View this thread: http://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: http://www.excelforum.com/member.php...o&userid=32667
View this thread: http://www.excelforum.com/showthread...hreadid=524734

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 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: http://www.excelforum.com/member.php...o&userid=31214
View this thread: http://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: http://www.excelforum.com/member.php...o&userid=32667
View this thread: http://www.excelforum.com/showthread...hreadid=524734

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.

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: http://www.excelforum.com/member.php...o&userid=32667
View this thread: http://www.excelforum.com/showthread...hreadid=524734

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"),"Possible
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 something
better


--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: http://www.excelforum.com/member.php...o&userid=31214
View this thread: http://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: http://www.excelforum.com/member.php...o&userid=32667
View this thread: http://www.excelforum.com/showthread...hreadid=524734

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 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: http://www.excelforum.com/member.php...o&userid=32667
View this thread: http://www.excelforum.com/showthread...hreadid=524734

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

On Tue, 21 Mar 2006 05:43:58 -0600, Squeaker1066
wrote:


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?

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