Combinations Of Subtraction Between Members Of Same Subgroup In Several Groups
Apr 27, 2014
I have a data that looks like:
1
a
data_1a
1
b
data_1b
1
c
data_1c
[code].....
Is there a way to automatically make all possible combinations of deduction between members of group 1 only then of group 2 only etc? So it would look like:
I have the following worksheet, which lists the pickup groups and members of each group from a phone system. Another worksheet lists all the exts and I want to have some sort of VLOOKUP, MATCH function that will look at the ext number and come back with the pickup group number.
I've attached a copy of the spreadsheet to show you what I'm talking about. So looking at the spreadsheet if I have an ext number of 8001 I want to return the value of 2 as ext 8001 is a member of pickup group 2. Also of an ext is not a member of any group, then I want to return nothing.
I have a set of 13 groups, each with anywhere from 1-9 different variables for a total of 56 variables. I want to find all the possible combinations of 1-5 variables. Only one variable can be used from each group in a combination. Please see attached data sheet.
I need to figure out how I can get excel to write me a list of permutations / combinations for the Group (column a) and Item (column b) columns in the example. The group designates the items that should be inlcuded in the combination / permutation in Related Items (column c). I have thousands of lines to do this with so I need to figure a way to automate it. I have started the Related Items column which is the results I need output. If it could format it like this adding in the space between the rows as it outputs the answer that would be perfect. Anyone know how to do this? I've found many answers, but none that have 2 columns with separate grouping within the column.
I have a Lookup table with a Group and Subgroup field. I have created a range for the Group and also for the Subgroup. On my main page, I have created a list option which allows the user to select the items under Group (Day, Colour and Time).
What I want next to do is based on the primary selection for Group; I want it to filter it on the specific Subgroup. For Example,
The User selects Colour from Group, the Subgroup will subsequently only show the appropriate selection which is Green, White and Yellow, and no further selection.
I would like to assign listbox members to a public variable (quite the opposite of what you normally do). I receive the error message "type mismatch".
Public MemberLB as Variant Public Sub ListBoxTest() n = ListBox_target.ListCount For iCnt = 1 To n MemberLB(iCnt) = ListBox_target.List(iCnt - 1) Next iCnt End Sub
my sumproduct function is adding up all items that fall on that date which is fine, but i would love to calculate an average so divide by the number of items that were used in the calculation, is that possible?
I have created a UDF which determines what the name of the day is.
Public Function Dagnaam(daydate As Date, Optional Language As String = "Dutch") weeknl = Array("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag") weeken = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday") dayno = Application.WorksheetFunction.Weekday(daydate, 2) - 1 Select Case Language Case "Dutch" Dagnaam = weeknl(dayno) Case "English" Dagnaam = weeken(dayno) End Select End Function
What i would like is when the user uses this function in the worksheet the field Language holds a dropdown list with predefined languages.
I have roster that has 12 teams on it, each team has their own column with the supervisors name in the first row. I want to look up the supervisor and count the number of team members listed under that supervisor.
I use Excel to query a SSAS OLAP Cube, the return of which I then model into various outputs. I do not have access to amend the cube.
I am hoping it is possible to be able to add custom calculated members to some dimensions in excel rather than on the server, but I'm struggling to find any resources that explains how it is done or if it is even possible. For example, the cube will return the dimension 'Source Country' with members 'England', 'Northern Ireland', 'Scotland', 'Wales', I would like to add a calculated member 'United Kingdom' to consolidate all those members.
Is this possible or should I just look to calculate this consolidation by formulas in the model.
to calculate training shifts spent in my training centre. We have 2 sets of occupational groups, those of underground & those of surface. I need to subtract 1 shift from the formula but only for the underground guys.
I have data that when added together will have decimals of .1 or .2. Any decimal great than .2 becomes an integre of 1 and is added to the other integres. for example: 1.1, 2.0, 3.2, 5.1=12.1.
I now want to subtract this total from another amount that will give me values with decimals of .1 or .2, example: 59-12.1=46.2 not 46.8. The same if 62-8.1=53.2.
What is the formula for taking two times, and finding the minutes between them. E.g. 12:35 PM-12:28 PM=7. I know i can use (A1-A2)*1440, but is there an easier way?
cell A1 contains the numeric value: 3961.3 cell A2 contains the follwing: ="z equals "&B3-3933.7
which i would expect to give me: "z equals 27.6"
BUT, it gives me: "z equals 27.600000004"
which is awefully strange becos the 4 comes form nowhere...
what makes some sort of sense is that if the value in A1 is changed to 3961.4 or 3961.5, then the final decimal 4 in A2 changes into a 3, and then a 2.
when the value in A1 is changed to xxxx.7 the number in cell A2 becomes what one would expect it to be.
how do i get rid of these strange decimals???
(formatting cells, setting number of significant figures does not work. i naturally already tried that)
I have Time Data in one column, and Values in the next, and then a blank column (for calculations, like subtraction/time).
Is there any way to automate the =Cell2-Cell1 command for regions of blue cells and have it paste the value in the blank column lined up with the first blue cell of the region?
The attachment will make more sense, but this is what I'm talking about:
Before subtraction macro:
BlueTime1 Value Blank BlueTime4 Value Blank BlueTime2 Value Blank BlueTime5 Value Blank BlueTime3 Value Blank BlueTime6 Value Blank Blank Blank Blank Blank Blank Blank BlueTime7 Value Blank BlueTime10 Value Blank BlueTime8 Value Blank BlueTime11 Value Blank BlueTime9 Value Blank BlueTime12 Value Blank
After subtraction macro:
BlueTime1 Value BlueTime3-BT1 BlueTime4 Value BlueTime6-BT4 BlueTime2 Value Blank BlueTime5 Value Blank BlueTime3 Value Blank BlueTime6 Value Blank Blank Blank Blank Blank Blank Blank BlueTime7 Value BlueTime9-BT7 BlueTime10 Value BlueTime12-BT10 BlueTime8 Value Blank BlueTime11 Value Blank BlueTime9 Value Blank BlueTime12 Value Blank
1st problem concerns entering data as text and it being converted to time. I have found the formula 00:00 which does an excellant job of converting. However, when I want to subtract, (=a2-a1) for example, it treats the time as a number. Sometimes it works, but if the hours are different, say 23:30- 22:10, it will subtract 2330-2210! Is there a way to make this work?
2nd problem, related to the first is when I try to avg a column of times, I get a similar effect.
Included is an example of a spreadsheet I am working on. There are multiple choices within several different drop-down menu's. As of right now I have the 1st menu as the stage of completion of a car. Within the next few menu's are options.
If welded chassis is chosen, none of these options are included. However if roller or turn-key are chosen then some of these options are included. But then there are also upgrades to these parts that are included as well. Is there a way to make 1 option included when a roller is chosen, but then if you want the 2nd option in the menu, you click on it and it automatically updates the price next to it, therefore subtracting the cost of option 1 from the cost of option 2?
