Excel and Search

[ilkertanli]

i need to compare 2 columns (consist of strings(company names) and not equal in size column A has 2500 entries, column B has 4000 entries) in excel to check whether the string in column A is in the column B or not, i tried some function like search and find and they didnt work, i also tried to write a macro.....

i was unable to use =search(a1, b1:b4000) it doesnt search through b1 to b4000 if functions finds a1 on b1 return the value of 1 otherwise returns #VALUE! which means a1 couldnt find on b1 and doesnt look for b2, b3 and so on.

i need a function which takes a1 searches through b1 through b4000 if found returns the value of 1 otherwise 0 then takes a2 searches ......

thnx for help!!

Ilker TANLI

http://www.tanli.net

 

[KenWright]

In C1 put the following and copy down to C2500

=COUNTIF($B$1:$B$4000,A1)

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[ilkertanli]

Thnx Ken it really works, you saved my live but now i have another problem:

Is there any chance to take A1 up to left parantesis and than compare with the entire column B because some of my data includes parantesis and i dont wanna take them for accuracy.

For Example i have got "Acta Metallurgica Sinica (English Letters)" on A120 and "Acta Metallurgica Sinica" on B25 and COUNTIF returns 0.

I hope u can help me, thnx...


Ilker TANLI

http://www.tanli.net

 

[xlbo]

Could only really do this via code, using the FIND functionality and choosing PARTIAL match - but then you may get slightly dodgy results for other items

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[GlennUK]

You could use an array formula to do it, like this:

=SUM(IF(TRIM(LEFT(A2,IF(ISERROR(FIND("(",A2)),LEN(A2),FIND("(",A2)-1)))=$B$2:$B$4000,1,0))

and enter it by pressing Ctrl-Shift-Enter. This will put curly brackets round the formula to show that it's an array formula.

Then copy it down as required.

Cheers, Glenn.

 

[GlennUK]

Duuh, of course, you could use part of my formula in a COUNTIF too, like this:

=COUNTIF($B$2:$B$4000,TRIM(LEFT(A2,IF(ISERROR(FIND("(",A2)),LEN(A2),FIND("(",A2)-1))))

Cheers, Glenn.