Hi, I'm Bhavik. Welcome to JustAnswer. I'm reviewing your question now, and will reply back ASAP.
I can give it a try, If you wish.
I haven't even started working on it. Don't think this can be done in couple of hours.
But will still try to see if I can complete it.
I am trying to understand your question correctly.
How many rows of such records you need ? I can see few column like G and I contains only few rows in it. Any reason ?
yes correct !
Ok, Got it . One more thing when you say dynamic length, Do you have any upper and lower limit on the lenght
Okay, Give me a moment, I will prepare one smaple data and show it to you. You can examine it and let me know where I am going wrong.
You can expect a sheet from me in 15 mins from now.
I am doing this using a formula in excel so I would not need a character separator in it.
Yes, I will give you it in 9 different columns.
Please see this and let me know if it requires any correction :-
I didn't know if they all should start with 7. These are all the random dynamic numbers which can be between 1 to 99999. As you said these integers can be of length 1 to 5.
I am not doing any calculation on them.
Do all of them should be 5 digit number starting with 7 ?
oh, Okay. I will do it. I though it is just a sample and you need some thing similar.
Sure, I will give you the excel sheet, Which will have formula written in it.
There are few rows missing do you want me to add data in those rows like , Column G has only 14 rows while column F has 35 rows in it .
Please see the attached sheet.
Yes, all these were there.
Please try this..
Not sure what is wrong with it. I have uploaded it to :-
Okay. With below rules how you can get 9 values per list.
Apply above rules, If any of the rule voilets then let me know.
Did not get you ? what is the above series ?
I think, I won't be able to help you in it. I am opting out.
not sure if you are still looking for help on this but based on the data, you are looking to have the output combined into a single column. where only unqiue values are used. Correct.
so the first combination would be cells
And so on.
unless I am missing something, You realize that there are something like billions of possible combinations and that any program will take about 100,000 years to run.
Also this will be very complicated and not inexepnsive and not something that can be done too quickly.
Let me know what you want to do.
actually no i'm sorry, given that you want all possible combinations listed, that is actually billions. You arrive at that number by counting the number of rows in each column and multiplying them by each other.
you arrive at the maximum number of calculations by using sum product
=SUMPRODUCT(COUNT(A:A),COUNT(B:B),COUNT(C:C)) ect... for all the columns just add a count for each column.
Anyway there is no formula that will do what you want anyway it will require massive amounts of programming. There are $Billion computer systems that are used for this kind of statistical analysis for this very reason, it is rarely done in excel as the CPU ussage is very slow in excel it will take thousands of years to write all of that output for billions of combinations. OR to put it another way it will take about 600 seconds for excel to do about 1000 rows of output.
This is based on my experience.
and long before its completed your computer would actually run out of memory, and the file size would be ginormous. You would need a million Terabyte sized hard drives to store it
given that though you are stilll getting unique values of 9 columns. And that is only a small portion of possible combinations. Even if we divided in by a 5th given that some columns repeat, you would still have a lot of possible combos
what ever the value it would still take about 500 years to output all that data, and excel would crash long before it was complete. Probably within the first 24 hours
im not trying to discourage you from doing this I am just telling you what is involved.. hopefully you will find that information valueable
and your calculations are way off because remember you still have to pull values from the 5 duplicate columns even if you only use about 1/5 of them
even if you divide colums B and C totals by 2 and D E F totals by 3 and subtract 20 rows from H you still get about 33 billion
again no formula will work this will have to be vba, and it will cost about $300 to do it based on your current criteria.
just send a new example file and Ill let you know if it is possible.
it will still cost extra to write the code
ok now your down to 167 million combinations
you will now need about 3 months to complete the output, about 300TB of ram, and about 60000 Exebytes of hard drive space
not to mention excel has resources limits so it will crash long before that
so inother words each number is ***** 1 time and only one time
so even though
7742 7770 7918 7855 7771 7716 and
7742 7770 7918 7855 7771 7717 are both valid possible combinations, you only would want to use the first one so after 7742 7770 7918 7855 7771 were used those could never be used again?
no rush, no way I could complete it by then anyway. I just wanted you to have all the information. Hopefully whatever the outcome you will at least give me a positive rating for trying and for explaining how everything works in excel upfront and what is involved. Hopefully that information is valuable to you.
keep in mind the more complicated the longer it takes to run. And the greater the cost.
And you are getting a great deal more complicated now.
ok, sounds good. Please don't forget to rate me 3 stars or better. Thanks