# Posts by scratchmaster

Hello NBVC,

Your are my HERO!!!!! Thanks you so much for clearly understanding the need. I have tried multiple combinations and the formula works PERFECT!!!!! I had a good feeling that both the logic from the previous formulas could help make a resolve.

Now this last formula is the right formula: =IF(SUMPRODUCT(--((LEN(E3)-LEN(SUBSTITUTE(E3,CHAR(ROW(INDIRECT("48:57"))),"")))>(LEN(D3)-LEN(SUBSTITUTE(D3,CHAR(ROW(INDIRECT("48:57"))),""))))),"X",E3)=E3

Could you kindly explain the various elements of how it works as it is quite different, especially the part that shows CHAR and" 48:57" ?

Thanks again! :congrats::thumbcoo::thanx::cheers::yourock:

Sincerely,
Scratchmaster

Hi Deciog,

I'm not sure I totally understand your questions, possibly the translator is not providing a good understanding of your questions. Thank you for not wanting to give up and your continue efforts on trying to find a resolve.

What I was trying to make you understand is that your formula works perfect as long as the DRAW 4 or DRAW 3 contains all single digits (e.g. 1,2,3 or 1,2,3,4) no repeat numbers.

Whenever it contains duplicate numbers (e.g. 1,1,2 or 1,2,2,3) the formula does not work properly. The reason why is because your formula looks for only one instance of a number not multiple in the case when a duplicate number exists.

I need the formula to accurately provide a true/false or yes/no on duplicates or single digit DRAW 3 or DRAW 4 for each solution provided.

Hopefully the translator provides you a better understanding of my requirements.

Thanks for your help! Hi NBVC,

Thanks for your reply and formula from the attachment in this thread.

It seems that your formula only provides a correct TRUE most of the times for duplicate numbers, however in some combination variances with duplicate numbers it did not indicate the correct number of TRUE’s at all.
.
For example: If the DRAW 4 # is 1213; 4546; 2991; 1612; or 9151 or any combination of these numbers the formula provides the correct # of TRUE’s.

However, if the DRAW 4 # is 8644 the result should equal two (2) True’s, instead it provides all False.

Next, if the DRAW 4 # is 3325 the result should equal two (2) True’s, instead it provides all False.

Next, if the DRAW 4 # is 1612 the result should equal one (1) True, instead it provides all False.

Furthermore, the formula only provides the first True only when the DRAW 4 # is all single numbers (e.g. no duplicate numbers).

For example: If the DRAW 4 # is 3952; 6894 or 3614 or any combination of these numbers the formula provides the first TRUE only. Each of these numbers should have had two (2) True’s.

Deciog, formula seems to work really well for DRAW 4 with only single numbers (e.g. no duplicate numbers).

Hi Deciog,

My apologies on saying that the formula works perfect as I did not evaluate against duplicate numbers. I just did some random number entries and it does not provide a correct answer of "No" when the number is a duplicate, however the any order part is working properly so don’t change that part of the formula.

As an example:

Draw 4 =1213. The ones are duplicate numbers, so the formula should only provide a “Yes” if it finds all four numbers in any order, (e.g. is must find two (1)’s) to answer correctly, otherwise the result is No).

Solution #8 in cell D10 is 346112; hence a “YES” result is provided because the formula finds all 4 numbers from the Draw 4 in cell E10 “1213” in any order of the solution number.

However, Solution#9 in cell D11 is 33213; it provides a “Yes”, but this is incorrect because the solution number does not contain two ‘1’s as a match to the Draw 4 in cell E11. It does contain 3 of 4 numbers from the solution number in cell E11 (e.g. 2, 1, 3) I think the formula is seeing the number “1” and since it is there it gives the yes, but it should be looking for two number “1’s. Therefore only cell G10 would result in a “Yes”.

As an example:

Draw 3 =121. The ones are duplicate numbers, so the formula should only provide a “Yes” if it finds all three numbers in any order, (e.g. is must find two (1)’s) to answer correctly, otherwise the result is No).

Solution#5 in cell J7 is 21199 and Solution#8 in cell J10 is 346112; hence a “YES” result is provided in cells M7 and M10 because the formula finds all 3 numbers from the Draw 3 “121” in any order of the solution number.

However, Solution#6 in cell J8 is 4125 and Solution#9 in cell J11 is 33213; it provides a “Yes”, for both, but this is incorrect, because neither solution number has two ‘1’s as a match to the Draw 3 in cells K8 or K11. Both solution numbers only contain 2 of 3 numbers from cells K8 or K11 (e.g. 2, 1). I think the formula is seeing the number “1” and since it is there it gives the yes, but it should be looking for two number “1’s. Therefore only cells M7 and M10 would result in a “Yes”.

Sample file attached to illustrate the issue.

## Files

Deciog,

You are the MAN!!! Thanks a million!!!!!!!!!! :congrats::tumble::sing::dance:

The formula works perfectly as I always imagined that it would. I could not have figured this out without your expertise. Would you mind explaining to me how each element of the formula works for future reference?

Hello Fellow Ozgrids:

I have been have a tough time on finding a solution to my problem. I’ve search both Ozgrid Excel help forum and YouTube, but could not seem to find a resolve. Here’s the issue:

I am trying to find matches of any order from the Draw 4 or Draw 3 listed in columns E and K when compared against the solution columns D and J.

The formula that I currently have here is:
=SUMPRODUCT(--ISNUMBER(SEARCH(E3,D3)))=COUNTA(E3)……copied down seems to only work if the solution number is in the exact order of the corresponding draw 4 or draw 3 number.

This is not what I need the formula to do. I need the formula to be able to provide a result if the solution is in any order or exact order of the DRAW 3 or 4. Therefore, the draw 4 and draw 3 results would have also included the highlighted cells as TRUE. When the formula is working like I require the results would show 3 TRUE results for both Draw 4 and Draw 3.

As an example: Solution #4 in cell D4 is 252333; hence a TRUE result is provided only because the formula finds Draw 4 2333 in exact order of the solution number.

However, Solution#9 in cell D9 is 33213; and Solution#11 in cell D11 is 73529335 both results in a FALSE match because it’s not in order of Draw 4, but it does contain all four numbers to yield a resulting match.

As an example: Solution #1 in cell J1 is 45901; hence a TRUE result is provided only because the formula finds Draw 3 459 in exact order of the solution number.

However, Solution#3 in cell J3 is 349654; and Solution#13 in cell J13 is 456894 both results in a FALSE match because it’s not in order of Draw 3, but it does contain all three numbers to yield a resulting match.

Sample file attached to illustrate the issue.

## Files

Re: Extract Records From Different Ranges and Filter Results

Hi Kris,

Thank you for your recommendations. I'm not totally sure what the "=--(E4&F4)" actually does, but it seems to work. I have made the corrections to my workbook based on your findings and recommendations. However, this recommendation only solves problems #1 and #2. I'm still having an issue with the sort, count and correct extraction of records as listed in my original post for problem #3. Any suggestions?

Hello fellow members,

Today I have 3 unique problems with my worksheet project. What I am trying to do is to obtain an accurate count and listing of UI codes by the two digit range (e.g. 00-09, 10-19, 20-29, etc.) whenever I sort by division so that I can assign a workload to my staff. The original worksheet contains over 5000 lines of data with various contract numbers, UI codes, dates and divisions.

Problem #1: =COUNTIFS(\$G\$4:\$G\$13,">="&I17,\$G\$4:\$G\$13,"<="&I18))

The count is not accurately working for the formula in the yellow highlighted row 16 cells I16-Q16.
The results should be I16=1; K16=2; L16=1; M16=2; N16=1 and P16=1, J16 & O16 should =0, but since the formula isn’t working properly it defaults to zero. I populated the same results from cells G4:G13 in cells H3:H13 to illustrate that the formula does work for cell H16 highlighted by green, but I really need it to accurately count from the data in cells G4:G13 for the cell in rows I16:Q16.

Problem #2: =IF(ROWS(H\$23:H25)>\$H\$16,"",INDEX(\$G\$4:\$G\$13,SMALL(IF(\$G\$4:\$G\$13>=H\$17,IF(\$G\$4:\$G\$13<=H\$18,ROW(\$G\$4:\$G\$13)-ROW(\$G\$4)+1)),ROWS(H\$23:H25))))

The correct formula is in rows H23:H25, which references the count in row H16 in order to prevent the further showings of the “#NUM!” results illustrating the UI code results are only 02, then 01 which is accurate. However, if I was to copy this formula to the remaining cells in rows I23:Q25 with the correct formula in cells I16:Q16 it would provide blank results in cells I23:Q25 which would be correct if the counts in I16:Q16 were actually zero, but this is not correct. I intentionally left cells I24, I25 and J23:Q25 formulas incomplete to illustrate what the correct results should show.

Problem #3: No formula for this issue, I was thinking of inserting “Subtotal” instead of “Small”, but that doesn’t seem to work.

If I was to sort by division “North” the correct count in cell H16 should equate to zero, but it remains a count of 2 and cell M16 should equate to 2. Since the count isn’t working properly on the division sort all the other cell counts from I16:Q16 would be incorrect which would also render in correct results for UI-codes in cells H23:Q25.

Attached is the sample worksheet.

I appreciate your review and resolve on this complex formula.

## Files

Re: Random Calulation with F9 Key, without affecting other formulas in worksheet

Roy this macro does not correct my problem. If calculation options are set to manual, column B or D both remain the same, no matter what choice I select from in column C and only changes when I hit F9, which then changes column B again and therefore nulls my selections and will never allow a complete match. If set to automatic, each time I make a selection from column C, both columns B & D change.

What I need is if calculation mode is set to Manual or Automatic then, I need column D to validate "correct or wrong" based on whenever I make a selection from column C. Only when I select F9, column B refreshes with new random numbers so that I can try to match column B to Column C all over again.

Note: The goal is to practice memorization by random selections of column C to match to column B. I hope this makes you better understand.

[TABLE="width: 654"]

[tr]

[td]

Must haves:

Numbers in Column "B" remain random upon selecting F9 function

[/td]

[/tr]

[tr]

[td]

Column "C" contains the drop lists, once choice is selected it causes Columns "E" to perform its function of lookup and validation(e.g. it checks column E and B to verify a match, resulting in a correct or wrong answer)

[/td]

[/tr]

[tr]

[td]

Problem:

[/td]

[/tr]

[tr]

[td]

The current Excel calculation options (e.g. Auto, Auto except data, or Manual) does not prevent Columns "B" or “E” from changing to another random number once you make a selection from the drop list in column "C". However if set to Manual only Column “B” changes to a random number.

Solution needed

[/td]

[/tr]

[tr]

[td]

I don't want another random number generated once I select from the drop list to validate the correct selection was made. I want random numbers generated only after selecting F9, but this cannot change the results in Column “D”.

[/td]

[/tr]

[tr]

[td]

How can I initiate a random calculation by selecting F9, then chose from the drop list in column "C" so that columns "E" will look up and match with column "B" if selection was correct without another random number generated once you make the selection from the drop list?

I have attached a sample sheet for review and more clarity on my problem.

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

The goal is to create a memorization tool, that can generate random numbers, to match the "mammals" to the "type" by validating your selections from the drop list.

Thanks in advance to all who review and attempt to resolve or provide solution.

[/td]

[/tr]

[/TABLE]

## Files

Re: Find and show correct s/n when matching s/n series is discovered

Good Afternoon,

I see that I had several reviews, but no replies, hopefully this is not to difficult to figure out. I looked throughout other related posts, but no joy. I appreciate any help.

Good Morning OZGrid Members,

I'm back again with a troubling issue that I haven't been able to solve.

[TABLE="width: 135"]

[tr]

[td][/td]

[/tr]

[tr]

[td]

My problem is automatically populating the matching serial number in column "O" whenever a match is determined from column "P". (Ex: in cell P4 there is a match because the "90 or 09" matches a number in the range of H4:M9, which corresponds with the serial number in cell B7.) If there is not a matching serial number in the series, then it should auto populate as "None"

I have attached a copy of the file for review.

[/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

## Files

Re: Create multiple worksheets from one worksheet quickly

Thanks, Michael Nicolas for your reply. Still not that keen on entering the VBA formula. I will give it a try in the future for testing.

However, I did figure out a work around to my problem. Since I'm using MS Excel 2010, I chose to go to file>excel options>under the general tab> go to when creating new workbooks>include _ sheets (its defaulted to 3 sheets, so I changed it to 64. I then opened a new work book which opened with 64 new sheets> next on the new workbook I right clicked on sheet 1>choose select all sheets> then I went back to my original template worksheet and copied all the cells required for duplication>next go back to the new workbook (sheet 1) and paste your copied cells to the particular location of sheet 1 that you desire from the original template worksheet and at an instance all 64 sheets will have been duplicated. I hope this may be simple for others to complete.

Thanks for all reviews. Cheers,
Michael Nicolas

Good Morning OzGrid Members,

My problem is that I need to create multiple worksheets from the master sheet for a project. I searched other posts first, but only a couple where similar to my issue but was asking the opposite of what my problem is here.

The master sheet is called “Template” and I need the template to be duplicated onto 64 different worksheets at once instead of the manual copy feature available on each worksheet tab. Note the master sheet (Template) has data in cells A1:BM150 and I need all the formulas, rows and column widths to remain the same when copied to the 64 different sheets. This would be a seriously tedious task of adding each worksheet, then manually copying each and reformatting each one after the other. Re: Find missing number from 0-9

Hello Converteds,

Sorry but I applied your formula as directed and it did not work correctly, not sure why but it kept giving all the numbers or zeros. Thanks for your help, but the previous reply from Trevor76 works great.

Re: Find missing number from 0-9

Good Day Mate,

Robert, your formula works perfect. Sorrry for the late reply, but I didn't want to let your help to me go unnnoticed. Cheers! :facepull:

Good evening OzGrid mates,

I have been having some difficulty trying to get my formula to work. In column A list 9 numbers, these are the (source numbers ranging from zero through nine, however this column will change often base on the data I put in it, but will always contain 9 numbers ranging from zero through nine. The formula is in column B and column C serves as a reference to column b. Based upon the numbers listed in column A, whatever number that is missing should show in column B. For Ex: If Col A shows 1,2,3,3,5,6,7,7,8. Column B should list the following missing numbers: 0, 4 and 9.

I have attached my spreadsheet for review and advice.

Thanks again for all who review and offer a solution in advance.

## Files

Re: rank and reference a cell

Thank you so much smuzoen. Your formula works great.

Good Morning,

I have been working on this and can't seem to get it to work properly as I have tried both the hlookup and vlookup, index and match formula, but no joy.
I need cells B14:D14 to be ranked 1st, 2nd, and 3rd based on the highest occurrences with their respective column headers (e.g. Field A, Field B Field C ...) automatically whenever the data in cells B3:H3 (number of occurrences) changes.

Note: So far I was able to successfully rank the data from cells B3:H3. Refer to attachment.