Count*Find - Need to find 2 different words matching the same cell in other sheet

Leo92

New Member
Joined
Nov 16, 2023
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello ,maybe the title is not well described.

In the first sheet, in the column A, I have a list of companies + services hired in the same cell.

In the second sheet, I have in the column A, each company, and in the row 1 , all the services.

What I need to do in sheet 2,exactly from cell b2, fill each company with a tick or a cross if they have the service or not. I knew there was a way with count*find , but cant manage to do it.

helppppp
thank you !
 

Attachments

  • ExcelForum.PNG
    ExcelForum.PNG
    50.2 KB · Views: 19

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi,

when you say in "Row 1 all the services" - do you mean all the services are in 1 cell, or do you mean the services are across the columns (B1 has a service type, C1, has a different service type ?, C3 etc..?

Unfortunately your copied sheet helps us not at all here ? If you can clarify what your second sheet looks like that would help probably a lot please ?

thanks
Rob
 
Upvote 0
Maybe
Excel Formula:
=IF(SUM((ISNUMBER(FIND($A2,Sheet1!$A$2:$A$100)))*(ISNUMBER(FIND(B$1,Sheet1!$A$2:$A$100)))),"X","")
 
Upvote 0
Hi,

when you say in "Row 1 all the services" - do you mean all the services are in 1 cell, or do you mean the services are across the columns (B1 has a service type, C1, has a different service type ?, C3 etc..?

Unfortunately your copied sheet helps us not at all here ? If you can clarify what your second sheet looks like that would help probably a lot please ?

thanks

Hi RobP!

Thanks for replying. I just uploaded 2 images to describe my issue. The image will the "ok" and "x" was filled manually, i have tons of data, so i need the formula. I hope this clarify and many thanks for reading!
 

Attachments

  • ex2.PNG
    ex2.PNG
    10.1 KB · Views: 18
  • ex1.PNG
    ex1.PNG
    11.7 KB · Views: 13
Upvote 0
Hi,

apologies for my delay - being out for a few days - but try this and see how you get on :

Book1
ABCDE
1ClientBlue Shoesred Shoesred hatblue hat
2NikeOKXOKX
3AdidasXXXOK
4ReebokXOKXOK
5
Sheet1
Cell Formulas
RangeFormula
B2:E4B2=IF(ISNA(MATCH(SUBSTITUTE($A2&B$1," ",""),SUBSTITUTE(Sheet2!$A$1:$A$10," ",""),0)),"X","OK")


of course edit the range references to suit your own data (I have range A1:a10 on second sheet, yours will be longer etc..
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top