Sort A Group Of Values

Mar 31, 2007

I would like to know how to sort a group of values/scores (values from row 1 belong together etc) ....

View 9 Replies


ADVERTISEMENT

Sort And Group Names Then Sum Associated Numerical Values For Each Group - Sort Total

Apr 18, 2014

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.

View 1 Replies View Related

Sum Values Belonging To The Same Group?

Jun 9, 2014

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

View 3 Replies View Related

New Line After Each Group Of Values

Mar 28, 2007

I 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

View 6 Replies View Related

How To Group Values Related To Each Minute

Apr 16, 2014

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

View 4 Replies View Related

Group All Records Where The Values In Column A Are Equal

Dec 24, 2008

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

View 9 Replies View Related

Search And Return Values In A Range Group

Feb 15, 2008

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

View 14 Replies View Related

Give Relative Values To Numbers In A Group

Jan 27, 2007

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).

View 9 Replies View Related

How To Create Scattered Plot With Repeating Values In The Same Group

Mar 20, 2014

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.

View 1 Replies View Related

Finding Top 2 Values Per Group Based On Multiple Criteria

May 21, 2007

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

View 9 Replies View Related

Group Multiple Rows Of Data That Have Staggering Values In One Row

Jan 17, 2013

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.

View 8 Replies View Related

Assign Group Names Based On Cell Values

Feb 19, 2010

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.

View 9 Replies View Related

Count Empty Column Cells For Each Group Of Values

Jun 11, 2009

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.

View 4 Replies View Related

How To Create A Macro To Auto Group Rows That Has No Numerical Values

Apr 17, 2014

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.

View 2 Replies View Related

VBA To Search Group Of Cells And Input Message If Value Is Missing From Group?

Mar 17, 2014

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 Related

Pivot Table Group :: Group By Integers And Not By Months, Years Etc?

Nov 12, 2009

I 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.

View 9 Replies View Related

Formula To Count Unique Values Within A Group For Pivot - Paste Special

Jun 26, 2013

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.

View 9 Replies View Related

Insert Group Header Based On Consistent Value Within The Group?

Feb 10, 2014

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 Related

Use The Group Box To Group A Number Of Option Buttons Together?

Jan 17, 2013

how 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?

View 2 Replies View Related

Convert A Group Of Numbers To A Group Of Letters

Aug 9, 2006

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?

View 9 Replies View Related

Apply Row Count Of Sub Group In Each Row Of Sub Group

May 28, 2014

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 Related

Sort Numerically By The Values

Oct 23, 2009

I have 9 columns of data in C2-C10 that I can sort numerically by the values in C2. All the numeric values in C2 are also in C1 but C1 has additional numbers not in C2.
I want to sort C2-C10 such that each row in C2-C10 aligns with its respective value in C1.

e.g. If C1 contains 1,2,5,8,34,42,56 and C2 contains only 2,5,34,56 I want the row (of C2-C10) with C2=2 to align with the cell where C1=2. And where C2=34 I want the row (C2-C10) with C2=34 to align with the cell where C1=34.
Is this possible though i'm not sure my explanation of what I want to do is good enough!

Note that my dataset is going to be very large (500,000 rows).

View 6 Replies View Related

How To Sort Values To Target

Apr 24, 2012

I'm trying to sort values in order by how close they are to a target, whether they are above or below the target.

Example:

Target = 24

17
22
25
16
30

Result:

25
22
30
17
16

View 2 Replies View Related

Sort By Absolute Values

Nov 10, 2006

Is there an easier way to sort a column full of numbers by their absolute value without having to create an additional column, use formulas to get the absolute, then sort that column and then delete it.

View 5 Replies View Related

Sort The Data :: Column B And E Should Have Same Values

Oct 28, 2009

I have about 3000 of these values per column. I need to sort the data so that column B and E are the same values. But the cells to the left and right of each B and E need to sort with it. Plus where there is a missing consecutive value I need a blank cell added...

View 8 Replies View Related

Sort Weekly Values Into Months

Jan 29, 2014

I have this table which I fill out each week. How I would get these values to automatically populate my other table which is displayed in months? (this table is wider than shown with more columns for other equipment and goes down for whole year).

#
Week Commencing
Available Machine Mins
Total Mins
OVERALL DOWNTIME
Can 61
Available Machine Minutes
Can Downtime to Process

1
30/12/2013
10090
199
1.97 %
24
2800
0.86 %

[Code] ..........

View 6 Replies View Related

Sort The Data :: Count The Zero Values

Mar 12, 2009

In a column I have loads of numbers like this except there are 5000 of them.

3
2
0
0
0
0
2
0
3
0
0
2
0
9

how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.

3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9

View 9 Replies View Related

Sort Multiple Values In A Cell

Jan 12, 2010

Need code of function that can sort the values in a cell?

For example:

Cell A1 contains 67,87,1,45,109 (note the commas), is there a quick way to change this to 1 45 67 87 10?

This is only a snapshot of the data I'm working with, I have 400 cells each containing between 10 - 50 numbers.

What I'm doing so far is, for each cell:

Text to Columns
Side Ways Sort A-Z
Copy all values to notepad
Copy all values from notepad directly back into the cell

View 9 Replies View Related

Auto Sort A List By Values

Jul 9, 2003

Is there a way in Excel to have a list of names with values automatically re-sort whenever any of the values change.

The default sort I have in mind is ascending by values.

eg.

CITY VALUE
Sydney 100
Boston 200
London 300
Moscow 400
Houston 500

The values change to:

Sydney 5000
Boston 250
London 8000
Moscow 300
Houston 50

I now want Houston 1st, Boston 2nd etc - automatically - without me having to do a sort again by Value.

View 9 Replies View Related

Sort Table (Names And Values) From Cell

Jun 7, 2014

I am keeping a spreadsheet for an auction

We have 20 people and each person gets a total bid for 48 items.

if person1 = 55,person2 = 100,person3 = 76,person4 = 67,person5 = 49 etc.

The person's names will be in column C while values in D in cell G3 will hold the total bid for the items each week updating.

What I am trying to do is sort the table (names & values) from cell G3.

So if each week G3 total gets bigger then the table sorts to match.

If the overall total bid after 48 weeks (G3) was 52 then the table sorts with 55 on top (because it's closer) 49 next.

The closer the person's bid to the total bid the further up the table the person will be.

I have uploaded an example with before and after sample.BidExample.xlsx

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved