Formula/Array to return results in a certain range

**B_Riz** · 01-30-2011, 12:06 AM

Hello all. I'm new here and have been trying to figure out the best way to write this formula.
It's been giving me a headache trying to figure this out.
The rundown:
I have a simple sheet which shows 3 columns: "Date", "Contract#", & # of Contracts"
(Please click link to see simple spreadsheet showing example)
http://img10.imageshack.us/img10/49/excelsshot.jpg

The contracts all contain 5 or 6 #'s which contain numbers from 1-30. Each # in the contract represents different stages in the billing process.

I need a formula/array that shows which #'s are not appearing within the range 1-30 for the entire group of B2:B9 (not individually, but for the entire range).

So, please see below list in BOLD RED in the range B2:B9... I've highlighted numbers below are not appearing:
1-2-3-4-5-6-7-8-9-10-11-12-13-14-15-16-17-18-19-20-21-22-23-24-25-26-27-28-29-30

I need a formula which will shows the #s in BOLD RED since they are not appearing in the range 1-30 in the cells B2:B9

I hope I didn't make the questions confusing.

Thank you in advance, I really appreciate the help!

**royUK** · 01-30-2011, 04:43 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**B_Riz** · 01-30-2011, 02:17 PM

roy - I wasn't certain how to title this as I was not sure exactly if it would be considered an Array or a standard formula. I thought I was being direct in the title by explaining "Need help with formula" as that is exactly what I need...Help with a formula.

Thanks for the heads up, I have changed the title and hopefully it meets your standards.

**daddylonglegs** · 01-30-2011, 02:40 PM

You can do it with a formula but you'd have to list each missing number in a separate cell, and the formula will be quite complex. Perhaps easier to use conditional formatting, e.g. list numbers 1 to 30 in E2:E31 and then select that range and use this formula in conditional formatting

=COUNT(SEARCH("-"&E2&"-","-"&B$2:B$9&"-"))=0

Missing numbers are highlighted, see attached

**B_Riz** · 01-30-2011, 08:30 PM

Originally Posted by daddylonglegs

You can do it with a formula but you'd have to list each missing number in a separate cell, and the formula will be quite complex. Perhaps easier to use conditional formatting, e.g. list numbers 1 to 30 in E2:E31 and then select that range and use this formula in conditional formatting

=COUNT(SEARCH("-"&E2&"-","-"&B$2:B$9&"-"))=0

Missing numbers are highlighted, see attached

Daddylonglegs - thank you very much for the help & info. I tried that formula you listed but for some reason it is getting an error & not working for me. Any ideas why this isn't working? I created separate column (E) containing the values 1-30 as you mentioned (E1:E30).

Thanks again!

**nikka123** · 01-31-2011, 03:51 AM

Hi B_Riz,

Just wanted to check where you have applied your formula? I tried solution Dadylonglegs have given and got the results, told by him.

I think you have doubt in that.
What you need to do is, after filling values in E column, select the range, go to conditional formatting. Click on "Use a formula to determine which cells to format" under select a rule type,

Put the formula =COUNT(SEARCH("-"&E2&"-","-"&B$2:B$9&"-"))=0 under "Format vales where Formula is True," category, then click "Format" button on the lower RHS. Click OK to apply the formatting.

"Daddylonglegs" good efforts.

**daddylonglegs** · 01-31-2011, 10:19 AM

As nikka123 says, the formula doesn't go on the worksheet but within conditional formatting, that's how I applied it on the attached worksheet (see my last post).

I think those instructions are for 2007, nikka, assuming you are using Excel 2003 and that you have the numbers in E1:E30 then try this

Select E1:E30

Format > Conditional formatting > select "formula is" from the first dropdown and paste the formula in the box, i.e.

=COUNT(SEARCH("-"&E1&"-","-"&B$2:B$9&"-"))=0

click on format and select required formatting > OK > OK

**B_Riz** · 01-31-2011, 02:12 PM

Originally Posted by daddylonglegs

As nikka123 says, the formula doesn't go on the worksheet but within conditional formatting, that's how I applied it on the attached worksheet (see my last post).

I think those instructions are for 2007, nikka, assuming you are using Excel 2003 and that you have the numbers in E1:E30 then try this

Select E1:E30

Format > Conditional formatting > select "formula is" from the first dropdown and paste the formula in the box, i.e.

=COUNT(SEARCH("-"&E1&"-","-"&B$2:B$9&"-"))=0

click on format and select required formatting > OK > OK

Ahh got it! thank you so much for your time & efforts. I really appreciate it. That's exactly what I needed

Cheers to you!

& Nikka thank you as well