?
Solved

How to compare a set of number for a draw in Excel 2007

Posted on 2011-10-07
5
Medium Priority
?
188 Views
Last Modified: 2012-05-12
I would like to compare 6 digit data stored in six columns with a string of data.
The input digits range 1 to 49.

Then matched numbers are then displayed in one column and the number of
digits match in the next.

Any solution which could produce the expected results will be fine with me.
Please see attached excel 2007 file for the details.
ComparesetofNumbers.xlsx
0
Comment
Question by:asidu
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 36935047
Hi, asidu.

Please see highlighted cells in the attached.

The formulas are ...
=IF(ISERROR(FIND(","&B8&",",","&H8&",",1)),"",B8&",")
&IF(ISERROR(FIND(","&C8&",",","&H8&",",1)),"",C8&",")
&IF(ISERROR(FIND(","&D8&",",","&H8&",",1)),"",D8&",")
&IF(ISERROR(FIND(","&E8&",",","&H8&",",1)),"",E8&",")
&IF(ISERROR(FIND(","&F8&",",","&H8&",",1)),"",F8&",")
&IF(ISERROR(FIND(","&G8&",",","&H8&",",1)),"",G8&",")

Open in new window

{=SUM(IF(ISERROR(FIND(","&B8:G8&",",","&H8&",",1)),0,1))}

Open in new window


(Please note that the latter formula should be array-entered, i.e. drop the {} and use CTRL-SHIFT-ENTER.

Regards,
Brian.
ComparesetofNumbers-V2.xlsx
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 36935212
asidu,

Small tweak to get rid of the trailing commas...
=SUBSTITUTE(IF(ISERROR(FIND(","&B8&",",","&H8&",",1)),"",B8&",")
&IF(ISERROR(FIND(","&C8&",",","&H8&",",1)),"",C8&",")
&IF(ISERROR(FIND(","&D8&",",","&H8&",",1)),"",D8&",")
&IF(ISERROR(FIND(","&E8&",",","&H8&",",1)),"",E8&",")
&IF(ISERROR(FIND(","&F8&",",","&H8&",",1)),"",F8&",")
&IF(ISERROR(FIND(","&G8&",",","&H8&",",1)),"",G8&",")&" ",", ","")

Open in new window

Regards,
Brian.
0
 
LVL 12

Author Comment

by:asidu
ID: 36935395
Excellent !
Thanks.
0
 
LVL 12

Author Closing Comment

by:asidu
ID: 36935400
Thanks again works perfectly.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36935405
Great! Thanks, Asidu.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question