In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.
View 2 RepliesI would like to group some columns to all the sheets that I will have selected. Unfortunately the below code only apply the code to the sheet I am looking at.
Code:
Sub Group()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws.Range("F:Q").Group
End With
Next ws
End Sub
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies View RelatedI have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies View Relatedhow to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
I'm working with a large database with hundreds of values that I need to have a table that looks like this:
A B C D E No class code Total 1.89 1.9-4.99 5 5.01-10 10.01-20 20.01-30 30.01-45 45.01-60 60.01-80 80.01-100 100.01-130 130.01-150 150+
I need each category to read another workbook page and count anything in x-price category with y value (being the A,b,c,d,e, or null)
I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
I would like to get the number of countries that a given "Name" has Partners in. (See example sheet for an illustration)
I have the ID of a group in column A. Column B represents Names. Column C gives Info about the country of "Name".
In column D I would like to get the information how many unique countries a "Name"'s group members are from. This means, not only within a single group, but in total over all groups. This means, that every same Name has the same number of countries in column D.
Formula in words:
"Sum of all unique countries in column C, if ID in column A matches any ID of Name in column B".
I have excel file in the below format.
Sheet1 ABC1NamegroupGroup22A A13AA 4A B25BB 6CC2 7AC4 8D 9E D1 Excel tables to the web >> Excel Jeanie HTML 4
I want count data Based on Group.
The answer is below.
Sheet1 ABCDEF11NameaBCDBlanks12A211 13B 1 14C 1 15D 116E 1 Excel tables to the web >> Excel Jeanie HTML 4
I have a column of equipment average utilization percentages. This column is a dynamic name range called "Avg_Util".
Values in this range were created by a calculation that results in a value of "", for equipment which have no utilization as yet, to those with a utilization of anywhere between 16% to 160% for those with actual hours in service.
I need to count how many have a utilization of <=50% and those that have a utilization of >=150%.
COUNTIF didn't work because it apparently counts the "" values (as zeros?)? I didn't fare much better with SUMPRODUCT either, but that could just as easily be the fact that I'm still new to that function. I get the feeling I'm overlooking something regarding working with percentages here to be having so much difficulty with something that just doesn't seem like it ought to be that hard.
Using Excel 2013.
Is it possible to get a unique count in a group subtotal though the elements in the group may not be unique using Pivot Tables w/o resorting to Power Pivot?
Ex
Group 123
Group 456
Are both members of NorthDivision
If someone is in Group 123 for 9 months, then Group 456 for 3 months, that membership should be reflected in the correct group accordingly.However, that person should only be counted once in the NorthDivision, not twice.
Output:
Group 123 0.75
Group 456 0.25
NorthDivision 1
At first glance, it appears easy, however, what if membership is only total 3 months?Then Group would increment 0.25, but I need NorthDivision to increment 1
I am looking to count group numbers that are bound to a nomenclature number. So I have made an example below. The nomenclature number is bound to 1. So I want to count all the 1's that are bound to 0912, which would be 5. and the same goes for 1234. the 2 is bound to the nomenclature 1234, so i want to count all the 2's that are bound to 1234. Which would be 3.
Group
nome
1
0912
1
1
2
2
2
1234
1
1
see attached workbook.
I'm attempting to count the empty cells in column C which correspond with the project reference shown in column A. I only want to perform this calculation where the project numbers change (see desired results in column D).
Am struggling with finding a way to define the different range of rows for each project ref within the formula. The full sheet has over 6000 rows and 1500 project refs.
I attached a print srceen of the excel worksheet.
In E5 i want it to show the count of records that belong to group 1 (C5) with category 1 value="1"
In F5 i want it to show the count of records that belong to group 1 (C5) with category 2 value="2"
I use the following formula to count unique values within a group for a pivot:
=IF(SUMPRODUCT(($CO$2:$CO55681=CO55681)*($E$2:$E55681=E55681))>1,0,1)
When I paste values over the formula it takes around 1 hours to do the 70k cells which is excessive. I want to know if there is a way to speed it up?
I tried doing an =cell and then paste special that and it works really fast but when I delete the actual formula's again the whole sheet locks up.
I am currently using Excel 2010. How to Count Singles, Doubles, Triples and Quadruples in a group of number.
Frankie
0
1
2
9
Count how many single numbers (numbers all different). Example: 0129
7
8
8
8
4
8
3
6
[Code] .......
Count how many double numbers (two numbers the same in a draw). Example: 8830
9
2
6
2
8
3
5
4
[Code] ......
Count how many triple numbers (three numbers the same in a draw). Example: 7888
8
6
1
2
9
5
9
6
[Code] ......
Count how many quadruple numbers (all numbers the same in a draw). Example: 2222
6
4
5
1
5
1
0
2
[Code] .........
How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
298
2/9/2014 12:28:02 PM
37
77
[Code]....
I have multiple names in each of 10 groups. Each name will have a number beside it. I wish to have a separate worksheet that will contain only the name with the highest number beside it, from each group.
View 5 Replies View RelatedI have an Excel sheet with one column (column A) and I have 503 000 data rows. In the first 503 rows I will need to insert the data group1. The next 503 rows, I will need to insert the data group2. The data always increment by 1 every 503 rows.
I could do it manually for few groups, but I need to create 1000 groups (503 000 rows). So you can imagine the load of work if I would do that manually.
Does anyone can explain me how to create a macro or anything that could automate this?
Im using Excel 2007 that support more than 1 million rows vs the old Excel versions which is a max of 64 000 rows
If you have 5 option boxes in the group at the top of a form and want to make sure the user of the form selects at least one box before continuing on, how do you do that?
View 9 Replies View RelatedI created a pivot table with daily dates listed from 1/1/2009- 3/31/2009. 3 months worth. I see i can group this data by hours, days, months, quarters and years but what i really need to do is group this by WEEK. Does anyone know a formula I could use to accomplish this?
View 3 Replies View RelatedHow to insert a group between another - see att. picture.
I have tried several solutions but havent yet found the right one.
The rows are not locked.
Picture: Capture.PNG
How to group different record and name the group. Attached file
View 14 Replies View RelatedI have a big database that has different name in column A(something like Y1, Y2, Y3, Y4, Y5, Y6, Y7, Y8...). What i'm trying to do is to group them! In group A i have Y1,Y7, in group B i have Y2,Y3, in group C i have Y4, Y6 and etc! I think the solution is something like index...but i never worked with that in VBA! So each name of group A should copy on a new sheet, the next rows should come group B and etc! Is there a way to do that in VBA?
View 11 Replies View RelatedI have uploaded 100,000 of record which is already sorted by group.
Now I have to insert balnk row or line that differentiate data ...
To do it manually is to much time consuming ..so can anybody help how to insert line or blank row between sorted group ..
Example...
111 dsdas
111 gdfg
111 sdfsf
222 sfsdf
222 cvx
222 vcxvx
222 vxcv
Desired result:
111 dsdas
111 gdfg
111 sdfsf
-------------------
222 sfsdf
222 cvx
222 vcxvx
------------------
Looking for quick reply.
222 vxcv
I have some VBA code which inserts a new row at the bottom of my data and copies the formulas down from the row above. This all works fine. However I now have the need to insert a new row within the data into the correct group.
So within the data there are 4 product groups (sorted).
So column A looks something like this (but much bigger):
Product1
Product1
Product1
Product2
Product3
Product3
Product4
Product4
Product4
I want the user to be able to select "Product1" from a drop down box (which I've set up). Then click the button called "New_Data"
So "New_Data" runs the code and inserts a new row at the bottom of all the other Product 1's (in this example it would be between row's 3 and 4).
Here is my existing code for just inserting a line at the end:
With Sheets("Sheet1")
.Unprotect
With .Range("A1").End(xlDown)
[Code]....