Hi,
I have many lists of number which are entered in the format in a single cell:
1,2,3-6,7,10,11,12,13 all the way to 100, where 8 and 9 are missing
1, 2, 3-9,6,7, 10-55, 67, 99,100
Also there are 14 lines
Line 1: 1,2,5,6,7, 10-66, 100
Line 2: 8,9, 76-79
Line 3: 81,82
I have a cell that says what the count should be, lets call it 100
How can I get back the missing numbers and find any duplicate numbers?
Thanks
JDI
Hi JDI,
Is a "line" a row or column or a single cell?
Can you supply a sample workbook to explain how you see the data and how you want your answer to appear?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Line is a merged cell, line 1 is actually Y13:AI13, technically Y13 since its merged, line 2 is Y14:AI14 technically Y14 since its merged.
Hi,
Merged cells are a nightmare for doing formulas in Excel. We really need to see a sample of the data. I also don't understand how you want the results to be displayed.
Here is a sample, the row placement and the merged cells is an unchangeable issueI put the data is Y13-17 and the desired results/request next to it
numberstrouble.xlsb
Thanks
JDI
Bump
Hi JDI,
Your data that has comma separated numbers with an occasional span of numbers (like 5-8) is a nightmare. I started writing a User Defined Function in VBA but need more incentive. Tell me this work has some value more than simply a fun game for you. Why do you get your data in this way? How important is needing to find missing and duplicate numbers? Can you get the data in a different way?
Your last request to have all 4 number strings used for the missing and/or duplicate numbers makes this problem much harder also.
Perhaps one of the good Excel Gurus will take a look at this and find an easy solution. For me, I need more reasons to work on this puzzle.
Hi Marvin, this is no joke, I am converting an old approach database. This is for my job and I just can't get a handle on it. There are hundreds of thousands of lines like this, the old users manually entered the data and the numbers are very very important. The data comes as is. I can not think of a way to separate it... We can leave out the last request. If it can't happen it can't happen.
Thanks for your time.
I solved the first part where you can find the missing numbers. See attachment. In cell B16 put the row that contains your text (put 3 or 5 or 7 or 9 or 11) and the missing numbers will show up in cell C16.
Finding the duplicates can be done in a similar way. The same is true with the latter part of your question but is more difficult to do.
The best way to do this is a very simple macro that reads the string and separates the numbers (recognizes intervals and generates numbers within intervals). Then in the macro, sort the separated numbers and find missing/duplicate values.
It takes about 20 minutes for me to write the macro. If you don't know Excel macros, then it is time to learn it!
Good Luck!
F
This works perfectly, I will attempt to use the vba to solve the other problems. In the meantime, any good book recommendations to learn VBA?
I think that I have a solution for all the questions that you asked. I used a table filled in with the numbers that existed in each string. From that I determined the missing numbers.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi newdoverman,Originally Posted by newdoverman
That almost works except that 1,2,5-8,9 finds 1,2,5,8,9 when in fact 5-8 actually means 5,6,7,8. Also in the case of 1,3,20 it is finding 2 instead of 20. This has been my headache.
OK JDI,
Find the attached with two User Defined Functions (UDF). One is to find what is missing and the other to find dups.
You can use these like normal formulas. In your examples if you had "1,2,3-7,6-9,12,15" in cell H3 you would use the formulas like this:
=MissingNums(H3) and after pressing enter it would show what numbers were missing. I have the defaults set to low of 1 and a max of 20 but you can change that.
=DupNums(H3) would show the duplicate numbers in that cell.
I have some optional parameters allowed in both of the above UDFs. If you want to specify a low of 5 and a high of 100 you would do it like this:
=MissingNums(H3,5,100)
Now if you want to do a bunch of strings all together, you will need to separate them with commas like this:
=dupnums(G3& "," &G5& "," &G7& "," &G9,1,30)
You will need to insure commas are places between each number and a real dash is between the span numbers.
If something doesn't work correctly let me know by a private message and tell me what went wrong.
JDI,
I basically self-instructed myself to learn VBA. It is a powerful tool and you need to learn that to also write Excel functions pretty much similar to what MarvinP did, which is nice. Just Google Learn VBA and there should be many free online courses. I also used Excel Forum and Stackoverflow to learn issues with more complicated problems. This will open doors to a new Excel world.
Good luck!
I think it has trouble recgonizing instances with no commas like 1-85, I understand the lower and upper arguments in A1, I though it wasn't working but I think it is this error instead.
UDF MissingNums and DupNums with Optional low and high range.xlsm
OK JDI,
In the MissingNums UDF you still need to separate the separate strings by a &","&. See the example attached.
In the DupNums I didn't handle a BLANK cell that was being added to the string.
Give this next one a try and tell me if it works for you.
Everything is working perfectly, you are indeed a Guru.
Thanks, very very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks