I have attached a file to better describe my issue, essentially I would like to count all the 'A's, all the 'B's and so on in the file and have the count next to it, so something like
A 6
B 3
D 5
F 1
In the file I uploaded I have column B as a count of the numbers, is there a way I could get just the final number for each letter, that would solve my problem.
sample.xlsx‎
Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.
Once I have a result set, I need to get rid of any groups which have a value contained in another group.
Example:
cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
...
Item100 75
Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.
One possible combination would be:
Item1 Item2 Item100 170
etc...
I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.
I want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals
So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)
I have SEVEN numbers and FOUR groups.
What I would like to do is list ALL the PERMUTATIONS of those SEVEN numbers in FOUR groups.
So basically, the groups would look like...
7000
4210
4201
4111
4003
3400
3310
3301
3301
3220
[code].....
...and EACH group would add up to SEVEN etc.
I think there are a total of 840 different PERMUTATIONS available, from anything from 0 numbers in a group to 7 numbers in a group.
I need ALL these groups listed individually .Is there some code that will do this?
I have a list of 600 figures (this may vary) but i need a formula that will look a one particular figure in this list and return if it is one of 5 equal groups. e.g in this example the lowest 125 figures in numeric order would be in group 1 up to the highest 125 figures would be in group 5.
I realise if I limit it to 4 groups then i could use the 'quartile' functions but i am stuck with 5 groups.
I need to distribute a list of data into 10 even groups of data. Here is the scenario.
I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria
- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible
I'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with.
Secondly, someone appearing in the first or last groups cannot appear in that group again.
I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.
I attached an example dataset : demo dataset.xlsx‎
Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.
combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?
I would like to count the number of time a name occurs in a range. I tried using a simple pivot table but it did not work because I may not always use all of the names from the list. Also I would like to avoid using the count if formula if possible. Is there a way to use a pivot table to do this?
View 6 Replies View RelatedI am currently using a pivot table to display data. At the moment, as you can see from the spreadsheet I have something that shows land use, suites and costs. how to calculate a total cost - but only counting each suite once.
I would like to do similar thing for the bottles required, this is based on information from background! I am hoping get something just like the way costs are generate, by only counting each bottle once.
I'm making a register and need to count the attendences (marked on the register as 1) and the number of authorised abcenses (marked on the register as AA).
I'm using the SUM function to count the attendences but wondered if they was a formula that would allow me to just count the AAs from the list.
I have a column of random names, say 200 of them in column A. I want to have column D show a name if (and only if) it appears more than once and have a count of how many times it appears in column E.
I can't use vba.
I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3
View 8 Replies View Related{=SUM((C2:C8="black")*(B2:B8="sneakers"))}
I’ve got following formula counting different kind shoes that are black and sneakers and above formula work very well. What I wonder is if you can convert above formula to count these items in a filtered list. Could you use the subtotal-function in any way?
Any way to make a formula that would "COUNT" the number of positive values from 0 to 999 (no negatives and nothing higher than 999). Another way of saying it would be values greater than or equal to 0 but not more than 999?
For example. If you had the following numbers in a list, I would like a formula that would only count the 3 numbers that are less than 1000 but equal to or greater than 0.
$370 $1,070 $1,400 $300 $1,160 ($4,720) $1,080 $1,170 $30 $1,050 $1,150
I am trying to make a Macro that will search through a folder on my PC and look for file names with a string such as shown below:
The issue i am facing is that many of the files have duplicates.
I need it to check to see if the number of duplicates in Column E for each file name have the same amount of duplicate files in the actual folder... and if they don't I need the rows(s) highlighted Yellow.
Example: Say I run this macro.. and there are only 2 files in the folder that start with "BH1003".. as there is 3 instances in column E.. the 3 rows that have "BH1003" in them should all highlight..
I have added my code so far below also, but it only highlights each row Yellow if it finds an instance of the string in column E.. I don't know how to get it to count and compare....
Code:
Private Sub CommandButton3_Click()
'Search Folder for files - Highlight Yellow if Exists - Highlight Red if NOT Exists.
Dim r As Long, lastRow As Long
With ThisWorkbook.ActiveSheet
'get last row
[Code]...
I have been struggling for almost 2 days with this problem.
I have a list of data, one column of which is the date displayed in dd/mmm/yy format.
This date will always be entered by users and is variable.
I can't find a way of counting the number of occurences of each month and each day that the date represents.
So, I want to know how many occurences of March, June, May, Septemeber etc are on this list and Mondays, Tuesdays, etc.
I have tried several posssible routes, DCOUNT DCOUNTA COUNT COUNTIF SUM. I've also tried separating the date out into days & months using the MONTH & DAY functions but this didn't work either.
It also apepars (shock horror) that Excel has incorrect date & day values because entering todays date in one cell (11/05/2006) and then using the DAY function to find the day of the week for this date produces WED when it should be THU. (My system date is set as 1 Jan 1900).
I feel that Excel can't separate the months & days away from the date, since the date is stored as a numerical value and not as we humans use dates.
The dates will always be manually entered by users, probably as dd-mm and Excel will automatically add in the year.
How can I count the occurences of each month and days of the week?
the number of days when there were 0 cases
the number of days there was 1 case
the number of days when there were 2 cases.
As yet there are no days in which there were more than 2 cases but there might be in the future.
I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.
What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases.
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13
H1 Total of matching numbers in cell range K1:P11
I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls
View 1 Replies View RelatedI have a sheet of over 60000 rows a small section of which I attach..
I need a formula in cell d2 to give the total for c2 to c10,d3 to d10 will contain this same total...cell a11 starts the next group and the formula for the group total will be in d11 to d18 etc
I have work sheet, ("Sheet1"), there are about 17000 plus entries in two columns A and B,. In Column C, following formula is inserted to show the "number of occurrences" of a Group in Column A.
=IF(ISBLANK(F4),"", IF(COUNTIF($F$2:$F$118,F4)=1, "", COUNTIF($F$2:$F$118,F4) & " occurances"))
Mine active worksheet is Sheet2. In Column A, an entry is made and Column B fetches the equivalent entry from Column Sheet1:B. The formula is :
=IF(ISNUMBER(MATCH(A1, Sheet1!$A:$A,0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$A:$A, 0), 2),
IF(ISNUMBER(MATCH(A1, Sheet1!$B:$B, 0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$B:$B, 0), 1),""))
When an entry will be made in Column A of Sheet 2, naturally it brings the equal of Column B. What I will like that in the subsequent columns, C,D,E,F,G,H, etc. the values may be reflected for all the occurences equal to entered in A1. For example if in Sheet 1, :
1234 xyzxyz
1234 bxyxdx
1234 ffffffff
1234 ggggg
So these are four occurences. in Sheet2, when one entry of 1234 will be made it will show any of the four values, the rest three values may be reflected in subsequent columns.
i am setting up a work sheet on ages for a sporting team
i want to be able to type in the date of birth and have it automatically come up with there age then for it to go to an age group
eg
14-3-00 would go to 7 yrs then go to under 8's
I am trying to sum groups of cells where the groups contain a variable number of cells (within a column). My sheet has the following columns, C = plot#, G = plant#(within the plot), and F = number of tillers(for each plant). I need to get the total number of tillers per plot. Plots have different number of plants, so for instance cells c11:c16 are plot 3, cells g11:g16 are number 1 thru 6 (plant#), so I need the total number of tillers which would be sum(f11:f16). But each plot has a different number of plants. I've been told I'm not the best at explaining things,
View 2 Replies View RelatedHere's one:
Column C (starting at C6) contains the team number. I want column B to find out if the team number is an AM, Mid or PM team.
I have listed the AM teams at J1:U1, the Mid teams at J2:K2 and the PM teams at J3:V3.
How can the formula in column B search those ranges and determine if the team in column C is AM, Mid or PM?
VBA code. My sales data is arranged as months in columns and customers in rows. I have about 36 months of data that must be formatted as a single date column for pivot table manipulation.
View 9 Replies View RelatedWhat I want is to sum any/all groups of numbers larger than 0 that are grouped in 4 or more....e.g. in my example D1:G1. The answer should be 150.... I'm trying this formula: ...
View 9 Replies View RelatedI 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.
View 5 Replies View RelatedI've been trying to rank by groups within one list of data; I can sort by two columns and rank with a simple "if" statement, but this is no longer practical for how often the data is updated and re-sorted. Trying to find a dynamic formula that creates ranks by the groups in a selected column. Example attached.
View 5 Replies View RelatedAttached workbook is self explanatory but Cols B-C have matching data in groups with a value for each row in Col D.
I need a formula to SUM each group and input that SUM value in the adjacent Col/Row E