Group Data For Ranking & Sorting
Mar 19, 2009
I have a spreadsheet with a list of ID's in column A. In Column B I have a list of ratings. Each ID has more than one rating. For example there may be 8 rows with ID 1, 12 rows with ID 2, 6 rows with ID 3 etc......
I want to rank the ratings in Column B with highest being 1 but only when the ID's are the same. For example there would be 8 ratings ranked for ID 1, 12 for ID 2 etc.....
The problem is I am running a monte carlo simulation on the rating of around 10,000 simulations. Every simulation the rank for each row will of course change because the rating has changed. The spreadsheet has around 70,000 rows and over 3000 ID's.
How can I do this without either the computer becoming a crawl or freezing up and it not taking 30 minutes for each step of the simulation?
View 8 Replies
ADVERTISEMENT
Dec 31, 2012
I have imported data into excel arranged as per the following and there are separate tabs per year. I a trying to summarize and phase the balances per client..i.e 07,08,09,10,11,12.
Therefore if client x balance in 2007 = 10, 2008=11, 2009=10, 2010=2, 2011=3 and 2012 = 5 and so on...then total the balance owed as of now and also phase it as illustrated below. I've tried vlook up's?
Client
Code
01Jan07-
31Jan07
01Feb07-
28Feb07
[Code] .....
View 1 Replies
View Related
May 6, 2008
I have a worksheet that I use on a track and field day to enter times and distances and then can allot points based on performance. That is not the problem.
My question is: Is there a function or formula to then sort through the data and rank the participants in order of their finish in a different worksheet? I imagine I would somehow need to link the data to each name. I have attached a copy of the actual worksheet.
View 9 Replies
View Related
Jan 6, 2009
So, I have a table of scores from players for a category I have. It looks like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------Michael-----------Germany-------------364
2nd--------------Tim---------------USA-----------------364
4th---------------John--------------Norway-------------355
if one of the guys, for example, John were to improve his score to a 369, I'd change the score in Excel, then use the sort feature to put him above Michael and Tim. Only problem is, it would say his place is still 4th place, and it doesn't change to 2nd place.
I want it so the "Place" column automatically changes to the correct rank based on the score the person has. So if I changed John's 355 score to 369 and hit sort by A-Z, Excel would automatically change the "Place" he's in appropriately so it would look like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------John--------------Norway--------------369
3rd---------------Michael-----------Germany-------------364
3rd--------------Tim----------------USA-----------------364
View 9 Replies
View Related
Nov 20, 2007
I 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
View 9 Replies
View Related
Jul 7, 2006
I have a list of students with id number and schools attended. The list is formatted as following:
Student1_ID1_SchoolI_Date
____________SchoolE_Date
Student2_ID2_SchoolA_Date
Student3_ID3_SchoolC_Date
____________SchoolI_Date
____________SchoolB_Date
Could you sort by student's first listed school? The A to Z sort on the school column scrambles everything. I would like to keep together the student's other schools and dates.
View 3 Replies
View Related
Oct 29, 2009
I HAVE THIS CODE THAT SELECTS ALL THE ROWS THAT CONTAINS CORTAR IN "I" COLUMN
View 14 Replies
View Related
Jan 7, 2014
I'm trying to figure out a way to group columns in a bar graph without displaying them in a cluster, but instead in displaying them individually in a specific order. However, I still want to identify each group with a different color. This is hard to describe, so I'll try my best to illustrate what I'm trying to do with an example. Assume I have three groups of data and within each group I have 2 to 3 items in it with different values. In a list the data would look like this:
Group 1 - Item 1 - $2,000
Group 2 - Item 2 - $1,100
Group 1 - Item 3 - $2,200
Group 1 - Item 4 - $3,200
Group 3 - Item 5 - $1,800
Group 2 - Item 6 - $1,200
Group 1 - Item 7 - $2,500
Group 3 - Item 8 - $2,800
What I want to do is create a bar graph that will still allow me to color all group 1 items as red, all group 2 items as blue, and all group 3 items as green while still showing each bar sorted in the order I've shown above and labeled as items 1 through 8. Then, I want a legend to only show the groups 1 - 3 with their colors. To explain, the graph would look like this:
Bar 1 would be labeled as Item 1, with a value of $2,000, and colored red
Bar 2 would be labeled as Item 2, with a value of $1,100, and colored blue
Bar 3 would be labeled as Item 3, with a value of $2,200, and colored red
Bar 4 would be labeled as Item 4, with a value of $3,200, and colored red
Bar 5 would be labeled as Item 5, with a value of $1,800, and colored green
Bar 6 would be labeled as Item 6, with a value of $1,200, and colored blue
Bar 7 would be labeled as Item 7, with a value of $2,500, and colored red
Bar 8 would be labeled as Item 8, with a value of $2,800, and colored green
Then, the legend would only show the groupings of Group 1, 2, 3, 4 and their associated colors.
(By the way... I know I can do this manually by selecting each bar and customizing the color, but I want an automated way of doing this to reduce maintenance time)
View 1 Replies
View Related
Mar 30, 2014
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
Example:
20
18
18
15
12
11
11
9
The ranking I am looking for is:
1
2
2
3
4
5
5
6
View 2 Replies
View Related
Jan 12, 2014
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.
View 2 Replies
View Related
Aug 12, 2014
I am facing some problem with ranking formulae. Attached is a file illustrating them.
Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.
Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.
This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.
Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.
View 1 Replies
View Related
May 22, 2012
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
View 6 Replies
View Related
Jun 24, 2014
I have a workbook of data that I created a pivot table with. From there I need to rank each value of the NES, column C, within the corresponding Reporting Structure, column A. The table that I created will be update with data on a monthly basis and I don't want to have to redo the rankings every month and just have it refresh.
View 2 Replies
View Related
Apr 4, 2014
I am trying to rank via data in two columns to eliminate any tied rankings.
I need to use the data in columns "AW" & "AX" to compile the rankings.
The ranking needs to be calculated by the highest number in column "AW" being ranked #1 and the second highest ranked #2 etc etc......any ties as a result of this ranking need to be broken by the lowest number in column "AX", the lower number being the higher in value and thus being the higher ranked.
For example:
AW AX RANK
13 89 7
13 63 4
14 89 2
14 81 1
13 90 8
13 66 5
13 58 3
13 87 6
I have attached the spreadsheet that I am using.
View 7 Replies
View Related
Aug 19, 2014
here is an example of what my data looks like:
Book1.xls
the numerical data under the colors is the cost to make an item that color. i need a way to say that i have "n" of each color available to make and then a way to tell me which items to make which color for the lowest cost.
View 5 Replies
View Related
Sep 30, 2008
Which formula do you recommend I can use to rank the below fields in yellow. I need to find the following:
1 - Which sales advisor (their name) has got the highest MPPI conversion and place it in cell K32.
2 - Which sales advisor (their name) has got the highest HC conversion and place it in cell K34.
3 - Which sales advisor (their name) has got the highest Highest Apps conversion and place it in cell K36.
View 5 Replies
View Related
Feb 14, 2014
I have a spreadsheet with values as per below
I can rank the data based on descending values in Columns B & C using the formula in cell B2
=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11>C2))
What I trying to do is also rank the data based on the values in column D as an additional tie-breaker
RankScore 1Score 2Score 3
1412315
2402420
2402422
4402030
5392015
View 3 Replies
View Related
Aug 20, 2014
Ranking in excel.
Column A - List of students Names
Column B - Grades of Students
Column C - Rank
The problem is, when I rank the students (order highest to lowest) the data in A and/or in B also moves.
What I want to accomplish is that the data input in columns A and B stay as is and Column C shall indicate what is the ranking of that student according to the number of data entry. i.e. 1st, 2nd, 3rd or 1,2,3 respectively.
View 9 Replies
View Related
Mar 18, 2013
I have 4,300 cells populated with data. I have sorted the data and now I need to rank the data in 1% increments. For example, the first 1% of data (43 cells) will be ranked #1, the second 1% of data (43 cells) will be ranked #2 etc. How could I automate this process? I was trying to figure something out with the "Rank" command but that doesn't seem to work in this situation.
View 3 Replies
View Related
Oct 12, 2011
I'm trying to achieve a table of weighted ranking based on correlations, a table I already have like the one given below
John / Products Owned 1 2 3 4
Shoes Socks Polish Brush Lace
Trousers Shoes Socks Belt Polish
Wallet Belt Trousers Shoes Socks
What I'm trying to achieve is this:
for all the top 4 correlations given for each product owned, i need to consider the ones not owned by John and recommend overall rank of the following nature:
For instance, the rank of socks here will be (1+2+4)/3 = 2.33, the rank of belt will be (5+3+1)/3 = 3 [ I'm considering the rank of belt as 5 for shoes, since it doesn't occur in the top 4 ]. Shoes, here will not be ranked since it is already owned by John.
I'm struggling to translate the logic into code here. Currently I perform it in a cumbersome manner, copying all columns to one column, then performing a pivot to find repetitions and so on.
Perhaps there's a function that could scan a dynamic sized table here, find the unique items and number of times they're repeated?
View 2 Replies
View Related
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
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
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
Mar 2, 2012
I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
View 5 Replies
View Related
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
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
Jan 8, 2008
how to write a macro that will automatically input a sum formula under a range of numbers in my file. An example is below. Every month I get this report and I then true up the numbers which is why I need the sum formula at the bottom of each category. The problem is every month is not necessarily the same length, but the report is a few thousand lines long, so a macro to do it instead of manually doing
Example:
Revenue
Revenue source 1
Revenue source 2
Total Revenue--------SUM(all revenue cells)
Payroll
Tax
Incentives
Vacation
Etc
Etc
Total Payroll---------SUM(All Payroll cells)
Warehouse
Supplies
Maintenance
Etc
Total Warehous-----Sum(All Warehouse)
View 2 Replies
View Related
Jul 26, 2014
I've got a table containing different codes and their values:
AAA 1
AAA 1
AAA 5
XCC 2
XCC 1
WTY 7
WTY 2
...
I'd like to group them so the code shows up just once whith its' value summed up:
AAA 7
XCC 3
WTY 9
...
See the file attached.
View 3 Replies
View Related
Jan 23, 2009
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)
View 9 Replies
View Related
Mar 25, 2009
I have a spreadseet where I am pasting data which all the time change.
I would like to know if there is a way to highlight cells with same value with one colour, other same cells with second colour,3rd....10 colours would be enough i think....
Formula should work for at least 200 rows.
i.e. I have in column D Dates in 20 rows.
1-10 row are same (blue highlighted cells)
11-12 and 15 are same (yellow highlighted cells)
13-14 row are same (red highlighted cells)
16-20 row are same (red highlighted cells)
View 9 Replies
View Related
