I have to perform the sum of some values. In particular I want to sum only values that belongs to the same group (indicated by a value in a column) (see the attached file). how can I do it avoiding the pivots?
Attachment 324006
I am operating Excel for Mac 2011 version 14.4.2
I have a list of 1500 addresses in a column, something like, 5VistaTerrace, and I need to insert a space between the number and each of the words so that it reads 5 Vista Terrace in the cell.
All of the addresses are different, some with 4 or 5 words like 5ABrownBayCourt... that particular one I would want to read 5A Brown Bay Court in the end.
Is there a formula or excel trick I can perform to save me the time of manually adding the spaces?
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.
I would like to know how to sort a group of values/scores (values from row 1 belong together etc) ....
View 9 Replies View RelatedI want to do is insert a new blank line after each group of values eg
Column A
Dave
Dave
Dave
Richard
Sam
Sam
In this I want a new blank line to run in between the two names. However, the problem maybe when I add in new names because I will want them to be all grouped in eg Adding another Richard in will sort out the group to be this
Column A
Dave
Dave
Dave
Richard
Richard
Sam
Sam
Would I therefore be better off running a simple tidy up macro after new text had been inserted which reordered the text in column a alphabetically, then added a blank row between the names. Does anyone have some VBA code knocking round that does this? I have found this code here as a starting point (though I dont understand it)
Sub InsertBlankRows()
Dim currentCell As Range
Dim s As Integer, i As Integer, e As Long, c As Integer
Range("A4").Select
Set currentCell = ActiveCell
Do While Not IsEmpty(currentCell)
s = currentCell.Value
e = currentCell.Row
c = currentCell.Column
I have a table in format below, I want show a third column with sum of w.r.t the minute, so for time 2:10 ,
i want to show 10 which is sum of first four values.
Started DB request
2:10:00 2
2:10:11 3
2:10:11 4
2:10:13 1
2:11:10 3
2:11:11 2
2:11:12 1
2:12:10 1
2:12:12 2
2:12:12 2
2:12:13 1
2:13:11 1
2:13:12 1
I need to group all records where the values in column A are equal. BUT I needed them sorted by another column (the whole sheet, not just within each group). Currently, all of the values in column A are listed in random order. Is there any way to do this manually?
to elaborate a bit further, consider the following:
Columns: A-B-C-etc.
1-Z...
1-X...
4-X
3-Y
4-Y
2-Z
Basically, I need to group all of the "1"'s and all of the "4"'s so that the extra values will show underneath the other. HOWEVER, I need them to be sorted such that the order in which they appear are XYZ. So the goal would be:
1-X
1-Z
4-X
4-Y
3-Y
2-Z
I have a workbook that has rows of data starting at A5:G5 seperated in groups of 25 rows.
Example: A5 is numbered 1 and A6 is 2 A7 is 3 and so on to 25 then skip a row and start numbering again to 25.
What this is for is employees are placed in groups of 25 to be eligible for an award for safety.
What I want to do is Range G5:G650 is to be a place where an "X" is placed beside an employee who has had an accident then Range H5:H650 would return "Employee had an accident" I already have this part working.
Then I want to code a subroutine that would search for the "X's" and return the entire row data for each name in that group of 25 on a seperate worksheet.
This would then be printed so we would know which group's to exclude.
I know how to make it return the row of data containing the "X" but how can I make it grab the entire group?
There will be multiple groups of 25 each and each time an "X" is found within a group create a new worksheet for that group.
650 employees / 25 = 26 groups
I have groups of data in a spreadsheet, I need to assign a value in column f for each piece of data in column e. The value needs to be a relative value for the rest of the group. For example if one group of data has 10 rows I need values 1 - 10 in column F, 10 being for the largest value in the respective row in column E and 1 being for the smallest value.
Each group does not have the same number of rows.
If need be I can add blank rows so the space between each group is equal, (if you can tell me an easy way to do this I would appreciate it as theres quite alot of data).
I have data that is something like this:
Group Assets
1 200
1 300
2 150
3 50
1 500
3 100
I want to create a scattered plot charts based on the data above but I cant get it to work for some reason. I want to have the "Group" on the x-axis and the "Assets" on the y-axis. I don't want to combine the same group number (for example to have group 1 with total assets of 1000, I want to keep it as 200 300 and 500 because I want to show which group is more represented than other group.). I have tried sorting the data by group and also with out sorting but the data doesn't plot at all (everything looks clustered at the 0 level on the x-axis instead of the respective asset level.
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33
I have a smaller spreadsheet with 40k rows/34 columns of data. Column A has serial numbers and does contain duplicates. The rest of the columns have varying data pertaining to those serial numbers. I'm sure there is an easy way to do this, however I have just not had enough coffee today because it is not coming to me. Here is an example of the data (not all columns obviously) and I put in ZZZZZ for each blank cell in the table and each space you see is the column break just for visual because I couldn't get it to paste here and have the staggering that it is doing.
See the laddering effect that is going on? When the data was imported into excel it created a new row for every value. What I need is all of the values in the same row EXCEPT if there are two part numbers referenced for the same serial number. In that case I would want two rows of the same serial number showing only the part number, description and two dates for that serial number in each row.
12345678SNABCPARTZZZZZZZZZZZZZZZZZZZZZZZZZ
12345678SNZZZZZHARDDRIVEZZZZZZZZZZZZZZZZZZZZ
12345678SNZZZZZZZZZZFALSEZZZZZZZZZZZZZZZ
12345678SNZZZZZZZZZZZZZZZ8/26/2011ZZZZZZZZZZ
12345678SNZZZZZZZZZZZZZZZZZZZZ9/24/2012ZZZZZ
12345678SNZZZZZZZZZZZZZZZZZZZZZZZZZFALSE
[Code] .....
Would like it to be like this (for the above data):
12345678SNABCPARTHARDDRIVEFALSE8/26/20119/24/2012FALSE
87654321SN123PARTPRINTERFALSE8/26/20119/24/2012FALSE
87654321SN987 PARTTONERFALSE1/1/201312/31/2013FALSE
Quick note, each serial number does NOT always have the exact same amount of repetitive rows. So there aren't 10 rows of 12345678, 10 rows of 87654321, 10 rows of 12345679, etc. They can be different. I do not have the original xml file used to import or i would just reimport it correctly this is all i have to work with.
I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:
Sheet2ABCDEFG1Table 1Table 22Domain NameGroup NameDomain NameGroup NameDomain NameGroup Name3NCG-W3K-SAT14-0DOSSNCG-W3K-EEC-SUN16-3EECEXCEEC4SVL-WIN-SAT08-0LABSNCG-W3K-SUN16-0DOSSDCDC5SVL-WIN-SAT06-0LABSNCG-W3K-TUE01-0DOSSNCGDOSS6NCG-W3K-MON04-0DOSSNCG-WIN-EXC-SUN21-0EECDMZETIS7NCG-W3K-EXC-MON04-3EECNCG-WIN-EXC-SAT16-0EECSVLLABS8NCG-W3K-EXC-MON03-0EECNCG-W3K-X64-DC-SAT14-0DC9NCG-W3K-EXC-TUE01-0EECDMZ-NCG-W3K-X64-SUN12-0ETIS10NCG-W3K-EXC-SUN15-0EECDMZ-NCG-W2K-SUN12-0ETIS11NCG-W3K-X64-EXC-SUN16-0EECNCG-W2K-SUN20-0DOSS12DMZ-NCG-W3K-SUN11-0ETISNCG-W3K-MON03-3DOSS13NCG-W2K-DC-SUN11-3DCSVL-DMZ-WIN-SUN12-3LABS14NCG-W3K-X64-DC-SUN10-3DCSVL-DMZ-WIN-SAT10-0LABS15SVL-DMZ-W2K-SUN12-3LABSDMZ-NCG-W3K-SUN12-0ETIS16NCG-W3K-EXC2-MON03-3EECDMZ-NCG-W3K-SUN10-0ETIS17NCG-W2K-SAT17-0DOSSNCG-W8K-X64-DC-SUN10-4DC18NCG-W3K-X64-SUN10-0DOSSNCG-W3K-TUE01-3DOSS19NCG-W3K-TUE00-0DOSSNCG-W3K-DC-TUE02-0DC20NCG-W3K-X64-SAT15-0DOSSSVL-WIN-SAT11-0LABS21NCG-W3K-WED00-0DOSSSVL-WIN-SAT07-0LABS22NCG-W8K-X64-SUN19-0DOSSNCG-W2K-SUN17-0DOSS23NCG-W3K-EXC-SUN20-0EECNCG-W3K-SUN20-0DOSS24NCG-W3K-ULZ-SUN14-0DOSSNCG-W3K-TUE22-0DOSS25NCG-W3K-TUE03-0DOSSSVL-WIN-SAT07-3LABS26NCG-W3K-ULZ-TUE00-0DOSSNCG-W3K-MON05-3DOSS27NCG-W3K-SUN19-0DOSSNCG-W8K-SUN18-3DOSS28NCG-W3K-EEC-SUN19-0EECNCG-W8K-SUN22-0DOSS29NCG-W3K-EEC-SUN20-3EECNCG-W8K-X64-DC-SUN11-0DC30NCG-W3K-EEC-THU00-0EECNCG-W3K-SUN17-0DOSSExcel 2007
My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.
1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2
2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.
That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.
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.
creating a Macro to automatically group rows that has no numerical value, for example:
1. 13.4
2. 0
3. 0
4. 33.3
5. 0
and so on...
So for this exercise I need excel to automatically group row 2, 3 5 with one click, and leave other rows with actual numbers visible on the sheet.
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 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 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?
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 Replies View RelatedConditional 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 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 Related