Please help, I've been struggling for weeks!

S

Squeaker1066

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

intruder9

I think we need more info, what kind of strings and what do you want th
final outcome to be
 
S

Squeaker1066

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
 
C

coa01gsb

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
 
S

Squeaker1066

yes, I can see that could work, but there are dozens of combinations. I
was hoping for something... neater. :)
 
S

Stefi

Please try to explain the pattern/rules in plain text!

Stefi

„Squeaker1066†ezt írta:
 
C

coa01gsb

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
 
S

Squeaker1066

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
 
C

christopher.lepingwell

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
 
S

Squeaker1066

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
 
P

Pete_UK

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
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
Top