• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1936
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# Excel problem I’m pretty smart.. but I need a fast solution

### Customer Question

Excel problemI’m pretty smart.. but I need a fast solution to a problem I haveThe basic premise is a slightly more complicated version of a “combinations without repetition” code I found on google.The ProblemI have 9 lists of values all of variable lengthCall them, A B C D E F G H IEach column contains integer values which are all unique to their column and all other columns except:B = C (all values, same row numbers)D = E = F (all values, same row numbers)H = B + D + G (concatenated, so if B has 27 rows H28 = D1)I need every 9 value combination, order is irrelevant, but that will only allow a value to be used once in combination with the remaining values (for example B1 – C2 - H3 should eliminate any otherwise duplicate combinations that would only get those same value from the columns that contain identical values.i.e A1 - B1 – C2 – D1 – E1- F1- G1- H3 – I1 = A1 – B2 – C3 – D1 – E1- F1- G1- H1 – I1 and should be eliminatedI can supply the base data
Submitted: 6 months ago.
Category: Microsoft Office
Customer: replied 6 months ago.
Sample Dataa b c d e f g h i
7742 7770 7770 7918 7918 7918 7855 7770 7716
7743 7771 7771 7919 7919 7919 7856 7771 7717
7744 7772 7772 7920 7920 7920 7857 7772 7718
7745 7774 7774 7921 7921 7921 7858 7774 7719
7746 7775 7775 7922 7922 7922 7859 7775 7720
7747 7776 7776 7923 7923 7923 7860 7776 7721
7748 7777 7777 7924 7924 7924 7862 7777 7722
7750 7778 7778 7926 7926 7926 7863 7778 7723
7751 7779 7779 7927 7927 7927 7865 7779
7752 7781 7781 7928 7928 7928 7867 7781
7753 7782 7782 7929 7929 7929 7870 7782
7754 7783 7783 7930 7930 7930 7871 7783
7755 7784 7784 7931 7931 7931 7882 7784
7756 7785 7785 7932 7932 7932 7785
7757 7787 7787 7933 7933 7933 7787
7758 7788 7788 7934 7934 7934 7788
7759 7789 7789 7935 7935 7935 7789
7760 7790 7790 7936 7936 7936 7790
7762 7791 7791 7939 7939 7939 7791
7763 7792 7792 7940 7940 7940 7792
7764 7793 7793 7941 7941 7941 7793
7765 7794 7794 7944 7944 7944 7794
7766 7798 7798 7945 7945 7945 7798
7767 7810 7810 7947 7947 7947 7810
7768 7813 7813 7952 7952 7952 7813
7814 7814 7953 7953 7953 7814
7954 7954 7954 7918
7955 7955 7955 7919
7956 7956 7956 7920
7958 7958 7958 7921
7961 7961 7961 7922
7974 7974 7974 7923
7978 7978 7978 7924
7986 7986 7986 7926
7927
7928
7929
7930
7931
7932
7933
7934
7935
7936
7939
7940
7941
7944
7945
7947
7952
7953
7954
7955
7956
7958
7961
7974
7978
7986
7855
7856
7857
7858
7859
7860
7862
7863
7865
7867
7870
7871
7882
Customer: replied 6 months ago.
that didn't go through accurately. I can attach a file
Customer: replied 6 months ago.
the spreadsheet is accurate
Customer: replied 6 months ago.
hello?
Customer: replied 6 months ago.
i'm about to outsource this somewhere else as no one has even replied in any fashion to say that they are capable of solving the problem, interested in doing so much less working on it yet.
Expert:  Bhavik Joshi replied 6 months ago.

Hi, I'm Bhavik. Welcome to JustAnswer. I'm reviewing your question now, and will reply back ASAP.

Expert:  Bhavik Joshi replied 6 months ago.

I can give it a try, If you wish.

Customer: replied 6 months ago.
I need this in the next couple hours. Or I will need to withdraw my request. my Account has already been charged
Expert:  Bhavik Joshi replied 6 months ago.

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.

Customer: replied 6 months ago.
I don't understand why I've already been charged when I haven't been provided a solution? How does this work?
Customer: replied 6 months ago.
I'm capable of working on this together with you to get this completed quickly. I don't need you to connect to my device
Customer: replied 6 months ago.
The excel spreadsheet I provided contains the full data set
Customer: replied 6 months ago.
my deadline is in 3 hours otherwise I'll have a week to be able to complete it myself so if you don't want to try i need my fee refunded please. Otherwise let's get going
Expert:  Bhavik Joshi replied 6 months ago.

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 ?

Customer: replied 6 months ago.
You are working off the attached excel spreadsheet correct ?
Expert:  Bhavik Joshi replied 6 months ago.

yes correct !

Customer: replied 6 months ago.
The 9'columns will always be dynamic in length, as stated. Columns B & c contain identical values as doncoljmns D, e & f
Customer: replied 6 months ago.
Column h is comprised of b&&d&h concatonated
Customer: replied 6 months ago.
The columns relate to the following:A = Quarterback
B / C running backs
D / e / f wide receivers
G tight ends
H is a flex position that can be a RB, WR or TE
Customer: replied 6 months ago.
I is defense
Expert:  Bhavik Joshi replied 6 months ago.

Ok, Got it . One more thing when you say dynamic length, Do you have any upper and lower limit on the lenght

Customer: replied 6 months ago.
Each lineup requires, 1 qb, 2 rbs, 3 WRs, 1 TE and a flex that can be any of those 3 positions, plus 1 defense
Customer: replied 6 months ago.
A player can only be used once
Customer: replied 6 months ago.
all the same running back values are in columns b and c , but using it in b will elimante being able to use it in c or h
Customer: replied 6 months ago.
No I do not each week I will a variable number of eligible players per position
Customer: replied 6 months ago.
However I can run a count of each position that contains a number after I import this list and use that count as opposed to a row count
Customer: replied 6 months ago.
The data you will be in the format you see I will manually populate those lists each week so there are no dynmalic issues in that sense to deal with
Expert:  Bhavik Joshi replied 6 months ago.

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.

Customer: replied 6 months ago.
Ok. Just remember I am looking for unique combinations of values the not permutations
Customer: replied 6 months ago.
If you want to put them all in one column I can break it up afterwards if it is easier to code than 9 columns that have to test each other
Customer: replied 6 months ago.
Just add a character separator between the numbers
Expert:  Bhavik Joshi replied 6 months ago.

I am doing this using a formula in excel so I would not need a character separator in it.

Customer: replied 6 months ago.
IF are creating the output in one column instead of 9 will need a character separator as the id numbers range from 1-5 characters in length so there's would be no way for me to tell now to separate them without a character separator
Customer: replied 6 months ago.
9 separate columns would be idea but I believe also much more intensive?
Expert:  Bhavik Joshi replied 6 months ago.

Yes, I will give you it in 9 different columns.

Expert:  Bhavik Joshi replied 6 months ago.

Please see this and let me know if it requires any correction :-

Customer: replied 6 months ago.
I'm not sure what you're done there. The values are IDand are unique. They currently range from 1-8000 something but the data I gave you was all 7****. Why am I seeing 5 digit numbers and 4 digits numbers starting with 4
Customer: replied 6 months ago.
I don't want any calculations performed on the these values I want unique combinations of them
Expert:  Bhavik Joshi replied 6 months ago.

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.

Customer: replied 6 months ago.
No
Expert:  Bhavik Joshi replied 6 months ago.

Do all of them should be 5 digit number starting with 7 ?

Customer: replied 6 months ago.
No they won't necessary start with a 7. What they start with doesn't matter. I need the numbers that I gave you in the spread sheet to be combined to create unique combinations with the other rules that I already covered
Expert:  Bhavik Joshi replied 6 months ago.

oh, Okay. I will do it. I though it is just a sample and you need some thing similar.

Customer: replied 6 months ago.
I need the code because the values are gonna change weekly.'so don't hard the code the list lengths
Expert:  Bhavik Joshi replied 6 months ago.

Sure, I will give you the excel sheet, Which will have formula written in it.

Customer: replied 6 months ago.
remember values from some of the columns are identical but can still be only used once with the 8 values in the row
Expert:  Bhavik Joshi replied 6 months ago.

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 .

Customer: replied 6 months ago.
Alright I'll give you another shot at it.'i just got a anreply with free answer on a forum But I'll give you the chance to get it working as you've already spent time on it
Customer: replied 6 months ago.
No the lists are varying lengths
Customer: replied 6 months ago.
Except that B will always equal c
Customer: replied 6 months ago.
D will always equal e and f
Customer: replied 6 months ago.
h is b + d + g
Customer: replied 6 months ago.
I thought I put all that in the problem description
Expert:  Bhavik Joshi replied 6 months ago.

Please see the attached sheet.

Expert:  Bhavik Joshi replied 6 months ago.

Yes, all these were there.

Customer: replied 6 months ago.
Expert:  Bhavik Joshi replied 6 months ago.

Customer: replied 6 months ago.
Still an error message please send it to my email
Customer: replied 6 months ago.
Expert:  Bhavik Joshi replied 6 months ago.

Not sure what is wrong with it. I have uploaded it to :-

https://www.dropbox.com/s/6cac3g0i16008xd/sample%2Bdata_new.xlsx?dl=0

Customer: replied 6 months ago.
No not right
Customer: replied 6 months ago.
I need 9 values per list
Customer: replied 6 months ago.
each list is a unique combination of values derived from the columns that they are listed under
Customer: replied 6 months ago.
Unless I'm looking athe wrong thing?
Expert:  Bhavik Joshi replied 6 months ago.

Okay. With below rules how you can get 9 values per list.

• Except that B will always equal c
• D will always equal e and f
• And h is b + d + g
• No the lists are varying lengths
Expert:  Bhavik Joshi replied 6 months ago.

Apply above rules, If any of the rule voilets then let me know.

Customer: replied 6 months ago.
A1 b1 c2 d1 f2 e3 g1 h3 i1
A1 b1 c3 d1 f2 e3 g1 h4 i1
Expert:  Bhavik Joshi replied 6 months ago.

Did not get you ? what is the above series ?

Customer: replied 6 months ago.
No don't ADD the values together. Look at the sample data man. B1 is the same value as c1 (it's the same id# ***** corresponds to the same player) that player can only play once. He has 3 "slots" in a lineup he can be played, either of the RB spots (b or c) or and as flex player (which also allows you to play a WR or TE spot. But regardless of spot he is used in. I only want to see one lineup he should only be combined with the same other players once
Customer: replied 6 months ago.
B1 data is the same as C1 data because it is the same running back list because you have to select a minimum of 2 and optionally. 3rd one as a flex.
Customer: replied 6 months ago.
In the above serious qb1, RB 1, RB2, wr1, wr2, wr3, te1, rb3, and d1 are selected
Customer: replied 6 months ago.
I'm the second series it's qb1, rb2, rb3, wr1, w2, wr3, te1, rb4, d1
Customer: replied 6 months ago.
Good? Is this something you can do?
Customer: replied 6 months ago.
I mean I specified I need a Cartesian combination matrix without repetition not adding sum numbers together
Customer: replied 6 months ago.
Still with me?
Expert:  Bhavik Joshi replied 6 months ago.

I think, I won't be able to help you in it. I am opting out.

Customer: replied 6 months ago.
Yeah I already called to cancel
Expert:  The-PC-Guy replied 6 months ago.

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

A2,B2,C3,D2,E3,F4,G2,H5,I2

then

A2,B2,C3,D2,E3,F4,G2,H5,I3

A2,B2,C3,D2,E3,F4,G2,H5,I4

A2,B2,C3,D2,E3,F4,G2,H5,I5

A2,B2,C3,D2,E3,F4,G2,H5,I6

A2,B2,C3,D2,E3,F4,G2,H5,I7
A2,B2,C3,D2,E3,F4,G2,H5,I8
A2,B2,C3,D2,E3,F4,G2,H5,I9
A2,B2,C3,D2,E3,F4,G2,H6,I3

A2,B2,C3,D2,E3,F4,G2,H6,I4

A2,B2,C3,D2,E3,F4,G2,H6,I5

A2,B2,C3,D2,E3,F4,G2,H6,I6

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.

Customer: replied 6 months ago.
you have the right output but it's still just one formula. I think \$40 for a formula is probably pretty sufficient. Also the length of columns in the sample data I provided is about what I expect to be deal with on a weekly basis, (and 5 of the 9 columns contain duplicate data). The output is more in the region of thousands.
Expert:  The-PC-Guy replied 6 months ago.

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.

Expert:  The-PC-Guy replied 6 months ago.

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

Customer: replied 6 months ago.
No you're missing that 5 of the columns have duplicate ids which are still unique to each entry
Customer: replied 6 months ago.
It's A*b*d*g*i
Customer: replied 6 months ago.
Well we're both wrong it's 184million
Expert:  The-PC-Guy replied 6 months ago.

A2,B2,C3,D2,E3,F4,G2,H5,I2

then

A2,B2,C3,D2,E3,F4,G2,H5,I3

A2,B2,C3,D2,E3,F4,G2,H5,I4

A2,B2,C3,D2,E3,F4,G2,H5,I5

A2,B2,C3,D2,E3,F4,G2,H5,I6

A2,B2,C3,D2,E3,F4,G2,H5,I7
A2,B2,C3,D2,E3,F4,G2,H5,I8
A2,B2,C3,D2,E3,F4,G2,H5,I9
A2,B2,C3,D2,E3,F4,G2,H6,I3

A2,B2,C3,D2,E3,F4,G2,H6,I4

A2,B2,C3,D2,E3,F4,G2,H6,I5

A2,B2,C3,D2,E3,F4,G2,H6,I6

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

Expert:  The-PC-Guy replied 6 months ago.

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

Customer: replied 6 months ago.
Sorry 2.3 million
Expert:  The-PC-Guy replied 6 months ago.

im not trying to discourage you from doing this I am just telling you what is involved.. hopefully you will find that information valueable

Expert:  The-PC-Guy replied 6 months ago.

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

Customer: replied 6 months ago.
Ok I will shorten my lists but I still need the algorithm. I will be furthering filtering it based on additional criteria but I intended to do that after the master list was generated in order to simply the forumla
Expert:  The-PC-Guy replied 6 months ago.

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

Expert:  The-PC-Guy replied 6 months ago.

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.

Customer: replied 6 months ago.
But I don't have the pull the values from the other columns think of it as the values in b can be put into c andh and pretend they don't have any values in them at all
Customer: replied 6 months ago.
They are players in a fantasy football lineup. So you can't have a player twice
Expert:  The-PC-Guy replied 6 months ago.

just send a new example file and Ill let you know if it is possible.

Expert:  The-PC-Guy replied 6 months ago.

it will still cost extra to write the code

Customer: replied 6 months ago.
Just pretend c e f are empty
Customer: replied 6 months ago.
I saw a formula using mod but I can't find it again
Expert:  The-PC-Guy replied 6 months ago.

ok now your down to 167 million combinations

Expert:  The-PC-Guy replied 6 months ago.

you will now need about 3 months to complete the output, about 300TB of ram, and about 60000 Exebytes of hard drive space

Expert:  The-PC-Guy replied 6 months ago.

not to mention excel has resources limits so it will crash long before that

Customer: replied 6 months ago.
I don't want duplicate combinations I'm not looking for every permutation
Expert:  The-PC-Guy replied 6 months ago.

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?

Customer: replied 6 months ago.
Alright dude I'll get back to this later. I've got a 1pm deadline we're getting closer but were not quite on the same page yet
Expert:  The-PC-Guy replied 6 months ago.

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.

Customer: replied 6 months ago.
Well you're getting a better rating than the other guy who wasted two hours of my time this morning
Customer: replied 6 months ago.
There's other filter criteria we try and incorporate during the matrix process if it was vba- for example I never want a running back playing against he defense he's going against (b, c) and i
Customer: replied 6 months ago.
I also have weight assigned to player so I could cap the algorithm at the best 150 combinations because that's the maximum number of entries I enter per competition
Customer: replied 6 months ago.
You also wouldn't want more than 3 players of the same team and often only two and where they are beneficial to each other. I.e. A QB (a) a receiver (d-f, h)
Customer: replied 6 months ago.
You don't want to 2 receivers on the same team in the same lineup. So there's lots of on the fly filtering that can be coded to limit the Permutations.
Expert:  The-PC-Guy replied 6 months ago.

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.

Customer: replied 6 months ago.
Well I can do all that code. I can do all the code actually I was just trying to get something ready for today but if I get stuck I'll come back to you
Expert:  The-PC-Guy replied 6 months ago.

ok, sounds good. Please don't forget to rate me 3 stars or better. Thanks

Customer: replied 6 months ago.