| Author |
Topic: A little help with Excel if possible |
17 replies
|
|
|
|
#1 posted Oct 31st 2015, 01:11:47
|
Quote
|
Good evening, guys!
I'd like someone to help me with something in Excel, if possible.I have a list of 15 names written in a column.How can I make all the combinations possible of those names to appear in as many columns as combinations exist?
Let's say the maximum number of combinations is 196.How can I make 196 columns with those 15 names to include those 196 possible combinations?
Can this be done, in the first place?
It would really help me a lot if someone could find a solution to this.
Thank you very much!!!
|
|
|
|
|
|
#2 posted Oct 31st 2015, 01:19:11
|
Quote
|
So you want to make a combination of the 15 names with one of the other ones?
Example, say names are a, b, c, d, etc... You want aa, ab, ac, then ba, bb, bc, etc?
|
|
|
|
|
|
#3 posted Oct 31st 2015, 01:39:56 (last edited Oct 31st 2015, 01:43:58 by Nate Lung)
|
Quote
|
You want to have a new column for EACH SINGLE combination of the 15 names?
But... but... but...
That's 15*14*13*12*11*10*9*8*7*6*5*4*3*2*1 = 1,307,674,368,000 possible combinations !!!!
Excel doesn't have that many columns.
Quote ( Robert Ardelean @ October 31st 2015,01:11:47 ) Can this be done, in the first place?
So... no.
Why do you need it? Surely there is some better way to do what you are trying to do.
You can PM me if you want. I am REALLY good with excel as long as you don't ask me to deal with VBA.
|
|
|
|
Quote ( Nate Lung @ October 31st 2015,01:39:56 ) You want to have a new column for EACH SINGLE combination of the 15 names?
But... but... but...
That's 15*14*13*12*11*10*9*8*7*6*5*4*3*2*1 = 1,307,674,368,000 possible combinations !!!!
Excel doesn't have that many columns. I think he means dual combinations, because with that it gives you around 196 combinations, which is the number he gave.
E: if my math doesn't fail, then it's 105 possible combinations
|
|
|
|
|
|
#5 posted Oct 31st 2015, 02:04:18
|
Quote
|
Quote ( Cristian Morales @ October 31st 2015,02:02:19 ) dual combinations
As in matchups(or games/matches)?
|
|
|
|
|
|
#6 posted Oct 31st 2015, 02:07:09
|
Quote
|
Quote ( Nate Lung @ October 31st 2015,02:04:18 ) Quote ( Cristian Morales @ October 31st 2015,02:02:19 )
dual combinations
As in matchups(or games/matches)? yeah, but I'm just guessing, as he didn't specify
|
|
|
|
|
|
#7 posted Oct 31st 2015, 02:27:41
|
Quote
|
Technically, it's 15x14 combos... 14 rows of all 15 names changing one row.
|
|
|
|
|
|
#8 posted Oct 31st 2015, 02:33:43
|
Quote
|
|
|
|
|
|
#9 posted Oct 31st 2015, 09:30:39
|
Quote
|
Quote ( Nate Lung @ October 31st 2015,01:39:56 ) You want to have a new column for EACH SINGLE combination of the 15 names?
But... but... but...
That's 15*14*13*12*11*10*9*8*7*6*5*4*3*2*1 = 1,307,674,368,000 possible combinations !!!!
Excel doesn't have that many columns.
Quote ( Robert Ardelean @ October 31st 2015,01:11:47 )
Can this be done, in the first place?
So... no.
Why do you need it? Surely there is some better way to do what you are trying to do.
You can PM me if you want. I am REALLY good with excel as long as you don't ask me to deal with VBA.
Yes, you are right.A new column for each single combination of 15 names.But if the number is so damn big(I didn't know how to calculate the maximum number of combinations), then it's not worth it.
What did I need it for?For the silly game I'm hosting, to make my life a little bit easier, but with so many combinations(if your calculations are right) it's better not to try it.
|
|
|
|
|
Resuurecting this thread as I need some Excel brains here (mine have stopped working). Will try to simplify as it could get complicated.....
Relative has a stock spreadsheet with items and numbers in the first sheet. Each employee has their own sheet and when they want stuff from the stock they enter it on their own sheet. Once all complete every week he sends out the stuff and updates the spreadsheet..... What he wants to do is 2 things...
Highlight in red on the employee list if someone enters an item that is not on the list
Highlight in red on the stock list if the total number requested is more than the amount in stock..
So for example
Sheet 1 (Stock)
Widgits 42
Gadgets 553
Wotsits 521
Thingumebobs 96....
Sheets 2 (first of 10 Employee sheets)
Widgits 36
Oohjahs 19...
Sheet 3
Widgits 10...
So he wants the Ooohjahs to go red on sheet 2 (as they are not on the stock list) and the Widgits to go red on the stock list as the amount requested is more than the stock
First I have nearly cracked (although LOOKUP was never my strong point so if there is a simple way please tell)
Second one is doing my head in.....
I have said he may need to get someone professional in but he is allergic to paying someone so wants me to try first.....
|
|
|
|
|
Might be as simple as conditional formatting
|
|
|
|
Quote ( Kyle Morris @ November 7th 2018,11:08:08 ) Might be as simple as conditional formatting
Conditional formating is pretty much what looking for, but the necessary formula's to go on top as an additional layer is where it'll likely to get a bit tricky.
|
|
|
|
|
Yeah it is the formulas (formulae??) that I am struggling with......
|
|
|
|
|
Maybe have an equal that takes data from the first sheet into a hidden column in the sheet you need and the conditional format where you can have a >B
|
|
|
|
|
|
Assuming you always have item names in column A and numbers in column B, what I would do is: on the employee sheets, add the available stock for the requested item in column C.
For example, cell C1 would contain:
=VLOOKUP(A1,Sheet1!A:B,2,FALSE)
This will return the available stock for that item, or a #N/A error if the item does not exist.
The next step is to turn that #N/A error into a 0 or maybe a -1 (because if you make it 0, it looks like the item exists, but is out of stock). You can do this by wrapping the IFERROR function around the formula that I showed above, so it will look like this:
=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE), -1)
Now you apply conditional formatting on cell B1: highlight in red if the value entered is larger than cell C1.
(edit: depending on your language/region settings, you may have to change the commas into semicolons for the formulas to work)
|
|
|
|
|
Pretty much what Jasper says.
|
|
|
|
BTW this will not highlight in red if Employee 1 requests 36 widgits and Employee 2 requests 7. Neither of them are requesting more than what is in stock, but their combined request does exceed the stock.
If you want this to show in red, I think you will have to first collect everyone's request on the stock sheet. So Employee 1's requests go in column C, Employee 2's requests go in column D etc. Once again you can do that with VLOOKUP, so cell C1 on the stock sheet will contain:
=IFERROR(VLOOKUP($A1,Sheet2!$A:$B,2,FALSE), 0)
You can drag that formula to the right, replace the sheet names in the other columns, then drag the formula down to complete the table.
Then you add all the requests up to see if they exceed the stock or not. And then using some sort of IF clause, you can display something on each of the employee sheets to show that a certain item is being requested too much.
|
|
|
|
|
Thanks guys... will give that a try......
|
|
Deutsch
Español
Română
Italiano
Português
Português
Français
Polski
Русский
Български
Македонски
Српски
Nederlands
Vlaams
Suomi
Lietuvių
Čeština
Magyar
Eesti keel
Slovenčina
Türkçe
Shqip
Ελληνικά
Hrvatski
Bahasa Melayu
Dansk
Pirate
