Return Top 10 Entries For Each Category

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


ADVERTISEMENT

Sumproduct :: Get A Number Of Entries That Fall Into Category

Oct 6, 2009

I'm trying to make my formula work. It is working now but I need to add one more criteria and I can' figure it out. I need to get a number of entries that fall into this categories:

John Smith09/02/2009Closed

=sumproduct(--(A1="John Smith"),--(B2>=Today()-90),--(C3="Closed"))

Now I need to get a count of entry either if there is a date >= Today()-90 or if it says "Closed" in C1. But I only need one of the two and I can' figure it out.

View 9 Replies View Related

Search Value And Return An Associated Category Name

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

Lookup Text Within String Return Category

Jan 29, 2010

I need to look within column A (which contains text strings), match label in column c (which contains text for label), and return result within column B (titled category label).

Essentially, I want to label my data based upon a specific element found within column A with a label list in column C.

I have attached an excel file that shows the current problem. Nested if(isnumber(search( statements can perform this, but I yield too many arguments error after three labels?

Isn't there a way to do this with Vlookup or Hlookup?

View 9 Replies View Related

Return Cells Relative Position In A Category

Aug 1, 2008

I've got a list of story titles that are organized by genre, and I'd ultimately like to have a formula (not VBA) that automatically generates a "story id" based on the title's position within the genre (not the list overall). Here's a snippet of the list:

CREATE TABLES LIKE BELOW?IDGenreTitle
1.1GeneralTitle 1
1.2GeneralTitle 2
2.1Horror / SupernaturalTitle 3
2.2Horror / SupernaturalTitle 4
3.1Sci-FiTitle 5
3.2Sci-FiTitle 6

So what I'm looking for here is the formula that would give me the ID shown in the first column (I can modify it later for specific purposes). I've tried variations on the MATCH, INDEX, ROW, and VLOOKUP functions but just can't seem to get it quite right. Note: All the items in the Title column are unique - there are no duplicates.

View 3 Replies View Related

Excel 2010 :: Group Table By Category And Put Category In Its Own Row?

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

Return Last & 2nd Last Entries

Aug 6, 2007

I am trying to construct formulas that dynamically extract correct values. I need to construct two formula one in Column I and Column J. Column L and M have solutions that I should have. The formulas need to look for data in columns and extract them. For example, if an entry in E3 then for This week it will extract it and for last week it would extract entry in D3.

View 9 Replies View Related

Formula To Return Unique Entries Only

Nov 6, 2008

I am seeking to pull names from a list on one worksheet in to particular cells on another worksheet using each name only once. I need a function or series of them that can accomplish this. I have been trying to figure this out for a couple of months with no luck as I always get into some form of circular reference....

View 14 Replies View Related

Return 5 Latest Entries For Column

Dec 21, 2012

I'm working on a project with tables. I'm not sure if I can use a function or I have to vba. I want to retrieve the lastest 5 entries for a specific column (column AA), but not blank ones. Then I want to retrieve latest 5 entries for column AC. On a seperate sheet I want the entries to be seen in order and I want it to change as new data is entered into the table. In the end I'll have the latest 5 entries for Internal (Column AA) and then after that the latest 5 entreis for External (Column AC).

View 3 Replies View Related

Lookup And Return To A New List Just The Unique Entries

Dec 11, 2009

product1
product1
product3
product7
product1
product7
product3

I want to do a lookup and return to a new list just the unique entries, such as this:

product1
product3
product7

I have it working now using a pivot table to get me the unique items, but there must a be a more efficient function to do this.

View 9 Replies View Related

Vlookup Data And Return Results On Multiple Entries

Feb 16, 2009

So i have a spreadsheet that has a list of members and how many events they have attended. That is fine because i achieved this by doing a countif function on their account number. The spreadsheet has to sheets Events Attended TOTAl and List. In the list it has their name account number and what event they attended and what date. What i need is to have a function that will lookup their account number and return what event they attended but they might have been to 4 different events.

I have a total of 5 columns dedicated to Event attended so we can tell it to lookup first event and return result then have an if function in the next column to lookup event and if its returned in previous column move onto the next event attended.

View 14 Replies View Related

Sum Of Top 5 Category Wise?

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

Average By Category

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

Check If ID Is New Then Sum Per Category

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

Sum Based On Category

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

Count Items Of Only One Category?

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

Listing Data By Category

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

Sum Data By Category And Date

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

Summing Each Category Per Month

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

Change Model To Category?

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

Count By Category And Insert New Row

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

Find The Top Result For Each Category

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

Condensing A List For Each Category

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

Placing Into The Correct Name And Category

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

Summarize Table By Category

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

Inserting Category Beside Products

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

Horizontal Category Axis Label?

Mar 11, 2014

I have a chart and a number of the data sets have a horizontal data but then some sets don't. How can I add the same horizontal data to the sets that don't?

View 1 Replies View Related

Displaying Data By Category By Year

May 1, 2014

DATA: list of films released by year, with box office totals and genre

GOAL: Function to sort data by year, then displaying how many films in each genre were released each year. E.g. horror comedies, romantic films released in 1981, 82, etc

Is there a function, or series of functions, by which I can do this without having to sort through manually?

View 3 Replies View Related

Splitting Frequency Data By A Category

May 21, 2014

It may be easier to view the attachment to see what I am trying to do.

I would like to split frequency data by groups.

So I have 2 different names in column A, with a bunch of their ratings in column C.

I would like to be able to view the frequency of their individual ratings to see who gave the most types of ratings.

View 3 Replies View Related

Changing Results Depending On The Category?

Jun 18, 2014

I'm trying to figure a function wherein if I change the category, the results will automatically changes. see attached sheet.

View 6 Replies View Related







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