Find

qcd

New Member
Joined
Feb 27, 2004
Messages
21
Book1
ABCDEFGHIJK
1
2ACE
3BDF
4
5ABCDE110
6FGHIJ
7
8BCDEF011
9GHIJK
10
11CDEFG011
12HIJKL
13
14ZYXWV000
15UTSRQ
16
Sheet1


I need formulas in the shaded cells.

Cell H2 contains "A" and H3 contains "B". The formula in H5 should look for "A" and "B" both in the range B5:F6. If found, show "1" else show "0".

The same thing should happen with all the shaded cells.

Can this be done?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

qcd

New Member
Joined
Feb 27, 2004
Messages
21
Got it !

The formula in H5 should be =IF(COUNTIF(B5:F6,$H$2)+COUNTIF(B5:F6,$H$3)=2,1,0)
 

qcd

New Member
Joined
Feb 27, 2004
Messages
21
dddddddd.xls
ABCDEFGHIJK
1
2ACE
3BDF
4
5ABCDE124
6FGHIJ
7
8BCDEF
9GHIJK
10
11CBEFG
12HIJKL
13
14ZYEFV
15UTSRQ
16
Sheet1


A little tough one this time:

Cell H5 will look for "A" and "B" in all the ranges that is, B5:F6, B8:F9, B11:F12 & B14:F15 and will tell the count how many times it appeared together. In this case only once.

Similarly, Cell I5 will look for "C" and "D" in all the ranges that is, B5:F6, B8:F9, B11:F12 & B14:F15 and will tell the count how many times it appeared together. In this case only twice.

Similarly, Cell J5 will look for "E" and "F" in all the ranges that is, B5:F6, B8:F9, B11:F12 & B14:F15 and will tell the count how many times it appeared together. In this case four times.

I am finding very tough to perform this task. Any help will be appreciated.
 

qcd

New Member
Joined
Feb 27, 2004
Messages
21
Somebody please help me. I am not getting the desired results. I have tried a lot of things to no avail.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

what dictates what goes into range of B5:F15??
 

qcd

New Member
Joined
Feb 27, 2004
Messages
21
I didn't get you Zack. Those are just random alphabets. Thats it.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

something like this??...
WTR03.xls
ABCDEFGHIJ
1
2ACE
3BDF
4
5ABCDE124
6FGHIJ
7
8BCDEF
9GHIJK
10
11CBEFG
12HIJKL
13
14ZYEFV
15UTSRQ
Sheet2
 

qcd

New Member
Joined
Feb 27, 2004
Messages
21
Exactly Zack but there is a problem. The data that I have posted is just a sample data, I have 500 ranges like those, if I use your logic, the formula will be as big as an essay.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,523
Members
425,479
Latest member
Neerajcool

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
Top