Populating 1st And 2nd Highest Occurrence Of Instance Per Category
Feb 13, 2013
I am trying to set up a template that displays (per selected team) the highest and second highest number of employee occurrences per a specific category. My main attempts at solving for this involved using an array formula to return this information, but, I couldn't get one together that worked.. Unfortunately I don't have any vba background or else I'd go that route
I've attached an Excel workbook to this thread outlining what I am attempting to put together on the template (sample outline). Tab 1 is the template which has a drop down in Cell C3:G3 where a user can select a team, Cell columns G6:G10 and H6:H10 (upon team selection) would look to the Data Entry table on Tab 2 and return the employee name (per the selected team on tab 1) and matching the designated category (displayed in B6:B10 on tab1) that has the highest and second highest occurrence of that category "infraction" tied to them.
View 6 Replies
ADVERTISEMENT
Aug 23, 2007
I have a very very long weekly table of, say, sales regions, items sold, revenue, profitability, etc.
(Very long here means 120,000 rows, and in Excel 2007 - please don't hold this against me! )
Let's assume Row 1 and Column A are for headers. So Column B is Sales Region and Column C is Item Name. The first Sales Region, beginning in B2, let's call it "America", will repeat (B3, B4....) until the Item Names are done for that region. Then the next Region starts, "Australia", and the list of Items starts again, etc.
Not all the Items will be sold in every Region each week, and even some Regions may disappear from the table one week only to reappear a few weeks down the road. The table is Sorted alphabetically, though, first by Region, then by Item.
One way to VLOOKUP week over week information from last week's report onto this week's report is to concatenate both weeks' Region and Item information (=B2&C2) seperately, and VLOOKUP by that. This returns only combinations of Region/Item that occured in the current week, and with ISERROR, 0 for combinations that occured this week, but not last week. As far as what it returns, this is fine for our purposes but with several pieces of information to VLOOKUP (#, revenue, profit, etc.) and 120,000 rows, it takes forever. Literally over an hour to copy down the week over week parts.
I had the idea that maybe it would be faster if I could define individual VLOOKUP ranges for each Region, and then look up Item within that range. These ranges would vary in placement in the table and in numbers of rows each week, as the number of Items sold in each Region waxes and wanes, hence "dynamic ranges" - I hope I've used that term correctly.
Is there an effective, efficient way to return the row numbers of the first and last instances of a given Region, and use that range for the Item VLOOKUP?
View 9 Replies
View Related
Feb 7, 2014
I have a list full of different IDs with different version numbers (This format: AKH123.1).
I'm trying to clean the list - removing all duplicate IDs but leaving only the largest version number.
I managed to remove the last digit using LEN/VLOOKUP and removing duplicates, but leaving only the largest version number is too tricky for me. I've thought about trying something with LARGE but can't wrap my mind around it.
My thought process went like this: I want to find all occurrences of "AKH123" and return the largest one.
The list contains about 8000 entries and this would save en enormous amount of work.
Here is a tiny portion of the list:
AKH450.1
AKH451.1
AKH451.3
AKH451.4
AKH451.5
AKH451.6
AKH451.7
AKH451.8
AKH452.1
AKH453.3
AKH454.1
AKH455.1
AKH455.2
View 3 Replies
View Related
Sep 18, 2013
Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
View 2 Replies
View Related
Mar 7, 2014
I need a formula in Column E to find the results for Column E or subtract A2 from C3, C3 from D3, A4 from C3, B5 from D5 and A6 from D6 .
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
[Code]....
View 5 Replies
View Related
Dec 24, 2013
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
View 2 Replies
View Related
Nov 27, 2013
I'm attempting to put together a script which in simple terms finds the 'Highest and Second' highest activity and associated FTE for a given sub group.
To be more specific:
On the "Slide 1" sheet look at the values in column J starting at row 7 until blank;For each value, then search the "All Monthly Direct Activities" sheet in column C for the same value, excluding the 'Subtotal' rows;Where a match is found, compare all the values in column E for that sub group;And Find the highest and second highest figure;
Once these are found copy this figure and associated value in column B and paste onto the "slide 1" sheet.
I'm not particularly well versed in writing VB, but I've been working with the code to see if I can get this work, which I have been unable to do.
I have attached a file which may perhaps provide a greater detail than my description of the problem containing the 'Source' and 'Destination' sheets.
View 9 Replies
View Related
Jan 15, 2008
If I use =MAX(A1:G1) I get the highest value. Is it possible to show the next highest rather than the highest and if so how?
View 9 Replies
View Related
May 8, 2013
I need a sum of top 5 numbers category wise. I have attached a sheet in which I would be requiring Sum of Top 5 for the month of May. I have shaded cell where i would be requiring a formula...
Also I may want to include other category as well.. For e.g. sum of top 5 for the year 2011 as well... I have tried every thing i could from Sumif to sumproduct to Large...
View 9 Replies
View Related
Nov 16, 2008
The title isn't very good I know; I don't know the right terminology to explain the problem properly, but i'll give it a go: In column A I have a list of names, all of them appear multiple times. In column B there is a corresponding percentage. I would like to create a formula that takes the average of all the percentages that have the same corresponding name, or 'category'. I don't want to just go through and do it manually since there are roughly 32,000 different values.
I did have one idea; in column C I pasted in a list of all the possible names in column A and then in column D I used countif to find out how many times each name recurred. If both columns A and C are in alphabetical order, I thought I could use the normal average function, by doing, for example AVERAGE(B1:BD1), but this didn't work. Is there some special way in which you can use a cell name (i.e. D1) instead of a number in a cell range? I tried brackets, speech marks etc. but nothing worked. I have searched around a bit on this forum to see if my question had already been answered, and did some extensive googling, but I couldn't find a solution.
View 3 Replies
View Related
Aug 30, 2007
I have 4 columns:
id (A), number (B), category (C) and month (D)
I think this could be done using VBA or formula's,
I understand how to SUM up based on criteria, but I
haven't a clue where to start to see if something was
present/ not present in the previous month and category and vice versa
If I decide a month to look at in E1, say February.. then i need
to calculate 4 things:
1) was an id in February and not in January (for this category) if so then sum up (B) per category (C) for February
2) was an id in January and not in February (for this category) if so then sum up (B) per category (C) for January
3)was an id in February and not in January (not in any cateogry in jan) if so then sum up (B) per category (C) for February
4)was an id in January and not in February (not in any category in feb) if so then sum up (B) per category (C) for January
I need to do this for each category (C)
My data would be something like this (I cant download addon from this PC, i have it at home)
id____number____category____month
1_____10_________A___________january
2_____20_________A___________january
3_____30_________B___________january
4_____40_________B___________january
1_____50_________A___________february
2_____60_________A___________february
3_____70_________A___________february
5_____80_________A___________february
so if I chose to put February in E1,
I would need the results something like follows:
category__new to category__left category__n__left totally
A___________(70+80)__________none____________(80)________none
B___________none____________(30+40)__________none________(40)
View 9 Replies
View Related
Jun 15, 2007
Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.
Have attached a sample xls..
View 5 Replies
View Related
Jul 29, 2006
I have the following
Private Sub test2()
Dim c As Range, t As Long
With Worksheets(3)
.Unprotect
Set c = Range("AO10", cells(Rows.Count, "AO").End(xlUp))
t = Application.WorksheetFunction.Max((Application.WorksheetFunction. CountIf(c, ">=.01") * 2), 0)
Sheets("Template").Range("D3").value = t
'Range("D3") = t
End With
End Sub
The code is suppose to get the value of >= 1 percent from AO10 then go down the column until it finds the second instance and then add that number to the tile.
Thanks for any help in advance.
Have a great Friday all! : D
Kurt
View 9 Replies
View Related
May 5, 2014
I have a list of patients with different categories. Count values of HBA1c which are less than 6.5 and those greater than 6.5 ONLY and only for those of the Category named "PreGDM" but not for other categories.
View 14 Replies
View Related
Jul 25, 2014
I have created a simple account list with - date - detail - category - amount.
How can I automatically list each transaction in date order by category on other pages. Each category is represented by a letter - e.g. 'T' for travel. In this example I want to list all travel transactions in a separate list in date order on another page for easy printing.
I have made a Summary page for each category but cannot work out how to make a detailed list for each category.
View 13 Replies
View Related
Dec 23, 2008
I need a formula to count list items if the date is between a specified parameter. I am trying to count how many help tickets were logged for a particular group or bureau during a specific week.
How many AA's between 12/1/2008 and 12/8/2008?
Bureau Date Opened
AA12/1/2008
AA12/1/2008
AC12/2/2008
AA12/6/2008
AB12/12/2008
AE12/15/2008
AC12/16/2008
AD12/17/2008.........................
I know how to count a list of items when you identify what you want it to count (I11 (Access)) and where (Application (Named Range)) but I can't figure out how to include the date parameter.
I J
Access23 =COUNTIF(Application,I11)
Acrobat2
ADMIN4
View 4 Replies
View Related
Jul 27, 2009
is there a formula I could use to total for each month all of the e.g. C3303, for each of these categories as detailed in the table at the bottom
View 4 Replies
View Related
Apr 13, 2014
1. In column A, I have models, in column B, I want to place either a "printer" if the model begins with Phaser or Laser, and if it begins with WorkCentre make it MFD. Also, if the Phaser has MFP on the end, make this an "MFD" and If Color 560 or Docucolor, make this "Out of Scope". How do I read the first part of the string in column A to do this, or do I need to make a second column with this Phaser and WorkCentre broken out?
I have this code attached, but only works if I have the word Phaser or WorkCentre.
View 8 Replies
View Related
Mar 1, 2009
I have been strugling this for the last 3-4 days but could not resolve it. I have list of items and i would like to count those items by item type per the user's selection.
For instance, the user selects either Yes or No and i would like to count only the ones that the user set for Yes only.
View 3 Replies
View Related
Oct 29, 2009
I have a product database that contains in column A the product name (eg "Granny Smith"), in column B the category (eg "Apple") and in column C the number of sales (eg 10,123).
I was looking to use code to list the top 10 products for each category, so to show the top 10 names I would enter in something like...
View 10 Replies
View Related
Oct 23, 2007
I want to create a Pivot table, and is now working on the data sheet. In this, every salesman have one row each day containing their sales result that day.
I have data in 3 columns. In column A the date, column B contains the Name of the salesman and column C the sales result that day. Now I want to update column D with each salesman's best result. I want each row to contain the top result for that particular salesman.
Date | Name | Result | Top result
1/10 | Pete I | 11.111 |
2/10 | Pete I | 19.100 |
3/10 | Pete I | 23.408 |
4/10 | Pete I | 16.774 |
1/10 | Sara G | 10.884 |
2/10 | Sara G | 10.739 |
3/10 | Sara G | 36.469 |...................
View 3 Replies
View Related
May 27, 2008
In sort of a follow up to the Condensing A List Thread
[url]
I would like to condense the list into a series of list for each category.
The example below is a synoptic view with some data and does not include the full table.
I would like to do it with functions, i can do it with vb, at this point, I'm more on a mission to do it without it.
View 9 Replies
View Related
Jul 16, 2009
i've been having for quite some time now. I need to input quantity of items into the existing database that holds all the items names which is sorted by category. Below is a sample of the problem:
Table 1 - Items with quantity that is waiting for input:
Quantity
Product
5
Button A White
7
Ribbon B Blue
8
Thread A Black
10
Cloth A White
3
Button B Blue
4Button C White
9Ribbon A Pink15Button A White
Above is just a part of it, the list goes on to over 200 rows long.
Table 2 - Existing database of all products:
Products
Quantity
View 9 Replies
View Related
Jun 4, 2007
I have an Excel sheet which has Category Column, I have set an auto filter for it, When I click a particular Category data pertaining to that Category is visible.
But my problem is I need help (VBA), that loops category autofilter, through each Category item and capture( sum number of items in that particular category) and put it in an other excel sheet. Kindly Help.
View 9 Replies
View Related
Jun 5, 2007
I have an Excel Sheet which has the Products column. I need to insert Category beside every products.
View 7 Replies
View Related
Apr 13, 2008
I'm attaching a spreadsheet in order for you to understand my question, and I will explain it based on that, ok? I apologize in advance for english mistakes, since it's not my 1st language. I want to search the salary value in column B and, in Column C, I need the job title to be posted based on what is written in Column J.
The table h2:i8 contains the maximum salary a person on that position can earn. So when you look the salary on column B, it should seach in column H and compare to the maximum value, and return the description on Column I that corresponds to that salary range. So if somebody earns 6500, it should be qualified as a supervisor, because its salary maximum is 7100. I have searched the forum here and found similar problems, with suggestions to use vlooup and Index. But I just can not figure it out.
View 2 Replies
View Related
Jan 8, 2009
I am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.
View 2 Replies
View Related
Jul 10, 2009
I need a simple formula which does the following: I have a column of numbers, and I want to find the first instance which is equal or greater than a specific number. For example,
Column A
23
41
67
89
51
21
If the specific value is 55 the formula needs to return: 67
if the specific value is 10 the formula needs to return: 23
View 2 Replies
View Related
Aug 11, 2009
Using Excel 2003 SP2. The formula =vlookup("apple",table,2,false) would return the data in the second column of the array named "table" for the FIRST INSTANCE it comes across for the word "apple." Is there a formula to have it return data for the LAST INSTANCE? This is one where I say to myself "Self, there has got to be a way to do this," but I just can't figure out what it might be.
View 4 Replies
View Related
Oct 24, 2013
I have a cell..say A1...which contains a value...now I have a row say A2:A15 which contains 14 values and I want to highlight the first instance of cell A1.
View 4 Replies
View Related
