Summarize Groups Of Values

Feb 4, 2009

I have a spreadsheet containing Date, Description and Value in a three column setup.
I wish to generate one summary for each unique description, based on a table of types. I can then say that for instance "Apple", "Oranges", "Lemon" are all part of a "Fruit" group.

The lookup would then find every row containing words in this group and summarize the values for them all.

View 7 Replies


Create Unique List & Summarize All Associated Values

Jan 30, 2009

I have a list of sales events that ran at various venues, together with how many people attended and how much revenue was brought in by sales. This data is arranged by week. One venue will never have more than one event per week, but different venues may have sales events on the same week.

My table looks like:

In reality this list could be thousands of rows long.

“Event week” is the calendar week of the event. “Weeks open” shows for each event how many weeks that venue has been open (may or may not be useful!).

What I need to do is to be able to pick a number of weeks (preferably changeable by entering into a cell) and have a list produced of each venue that has run for at least that many weeks, and the sum of people and sales in those first X weeks. My idea of output, if the user input “2” as the number of weeks would therefore look something like:

Note that
1)only venues that have run at least 2 meetings are shown in the output
2)even if venue has run over 2 meetings, only people and sales figures from the first 2 are summed up and displayed.

In this case. Later on I need to produce a frequency chart/histogram grouping the results into categories, but I know how to do this as long as I can get to the step before – but wanted to mention it in case there are shortcuts.

Have been battling with this with pivots, database functions, array formulae etc. for a long time.

Attached is the spreadsheet with sample data. The red table is the part I am stuck on. The solution has to work in Excel 2003.

View 4 Replies View Related

Adding To Summarize Values After Matching Criteria

Apr 26, 2007

I have three worksheets. The first one has a 20 digit code that is broken into eight sections ( cells) and an amount assign to it. There are currently 150 codes but can grow bigger as new codes are assigned with their own amount. The other two worksheets have the same breakdown for the code but will be filled in as needed. They will record additions or subtractions to the code, like a register. I would like to then summarize (on a fourth worksheet) the +/- activity from worksheet 2 and 3. To do this I will copy the first worksheet then setup three additional columns one will be Increase the other Decrease and the last one Balance. What I will need to make sure that I only pick up the amount for the right code. So I need to create a formula that makes sure that it compares wks 2, cell A1 to wks 4, cell A1 AND wks 2 cell B1 to wks 4, cell B1 AND wks 2, cell C1 to wks 4, cell C1 and so fourth until the eighth breakdown of the code. When this is true then I will need to add the amount that was recorded on wk2 or wk3 for that code on wk4. The codes may be used various times so the sum of the values of wk2 or wks3 will need to be cumulative.

I've been reading about Sumproduct and I have created this formula but the result is # NUM....

View 9 Replies View Related

Filter Data By Different Groups Of Values

May 16, 2014

I am trying to improve a spreadsheet that I have to use everyday. I have a list with varying number of entries (different length every day) that has a number of columns including a column that contains machine codes (say 1,2,3,4,5...).

Each machine is programmed by a different person (say Andy,Ben,Craig,etc.).

One programmer can program more than one machine so for instance Andy is interested in programs for machines 1,3,5 and Ben in machines 2 and 4.

Every day we send a list of jobs that require programming which is pre-filtered for each programmer so if I send the list to Andy he only sees rows of data with machine codes 1,3 and 5.

I am trying to make a drop-down where I pick the programmer's name and the list automatically gets filtered for the machines for that specific person (e.g. 1,3,5).

I believe this could be done with advanced filtering but the problem is that the list length changes every day and the data is pasted into the spreadsheet from a different CSV file so I cannot define a set range for advanced filtering because it does not accept blank rows...

I am attaching a screenshot : list.jpg

View 1 Replies View Related

Conditional Formatting For Groups Of Values

Feb 2, 2010

Is there a way to do the following using conditional formatting?

I need to have all duplicates highlighted.

For example (Since "Ana M." is the first) highlight both rows (or at least the group of "Ana M." cells in the column, entire row not necessary)

Ana M. 12 A St 333-3333
Ana M. 23 Z St 333-3333

Jane S. 12 A St 555-5555
Jane S. 15 Z St 555-5555

Sam A. 55 A St 222-2222

Tony J. 45 A St 444-4444
Tony J. 11 B St 444-4444
Tony J. 66 Z St 444-4444

View 9 Replies View Related

Finding Unique Groups Of Values In Columns?

Dec 17, 2013

I have a file with immunophenotypic markers and their relationship to certain blood cells.

What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.

I have attached the file (I think!)

View 14 Replies View Related

Separating A Range Of Values Into Equal Sum Groups?

Feb 27, 2014

Is it possible to separate a range of cells with numerical values into "n" groups that have equal (or as close to equal as possible) total sums?

Ex. The range A1:A30 includes 30 random numbers between 1 and 12 (obviously there are duplicates). I need excel to auto-generate 6 groups of 5 cells each with each group having the same (again, as close as possible) total sum. Preferably, I'd like the numbers in each group to be similar from top to bottom, but I'm not overly concerned about that.

View 2 Replies View Related

Dividing List Into Equal Groups Determined By Two Values?

Mar 10, 2014

I need to distribute a list of data into 10 even groups of data. Here is the scenario.

I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria

- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible

View 6 Replies View Related

Identify Values Specific To Defined Groups Of Columns?

Sep 2, 2013

I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:

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

For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc):
- In condition (row) 1: "x" is a specific value only found in Group 1
- In condition 1: "y" is a specific value only found in Group 2
- In condition 2: "x" is a value found in majority in Group 1
- In condition 3: no specific value can be associated to Group 1 or 2.

What I would like to get is a measure of whether:
(1) there are values over-represented in one of the groups, or 100% specific to one group.
(2) what are these values
(3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)

View 1 Replies View Related

Use Combobox ListFillRange To List Values From Two Groups Of Cells?

Feb 24, 2010

Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.

combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?

View 8 Replies View Related

Excel 2010 :: Calculate Most Common Text Values By Groups

Mar 14, 2013

I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.

ID Code
1 HB
1 KU
1 KS
1 LV
1 KS
2 LM
2 KS


I want a table like below:

ID Code
1 KU
2 KS
3 KS

How can I do it in Excel 2010?

View 9 Replies View Related

How To Lookup Particular Value And Then Summarize

Dec 15, 2013

build a formula that can lookup and summarize.

I attached file >>>Lookup summary.xlsx

View 4 Replies View Related

Summarize The Data By VBA

Jul 5, 2007

Date ModelStationLot_SizeSampleReject


How can I quick summariz these data by VBA ? as below result.

Date ModelStationLot_SizeSampleReject


This is just my sample data. The real data is more complicate than this. I want someone suggest me about VBA code.

View 9 Replies View Related

Summarize With Group Descriptions..

Aug 25, 2009

I think I need a modified SUMIF formula but I can't figure it. The attached spreadsheet is a sample of what I need. I'm using Excel 2003.

View 3 Replies View Related

Summarize Weekly Inventory

Feb 7, 2010

I having trouble to sort out and summarize inventroy products for each week. I want to sumarize all weekly inventory in summarize table. Please see attached file. Pls keep the formate as i shown in attached spreasheet.

View 9 Replies View Related

Use For Propety To Summarize The Number From 1 To 20

Jun 7, 2009

How Can we summarize the following
I tried to use For propety to summarize the number from 1 to 20 but it didn't work properly.

View 3 Replies View Related

Sumif - Summarize Data

Mar 22, 2007

I am trying to summarize data for my boss,

She has a spreadsheet with all of the employees of the company listed. Each employee is associated with a cost ceter (there are several hundred employees, and about 90 cost centers). Each month we count how many people are in which cost centers. I am using a sumif equation, and that works well, however, we keep the historical data, as well as the budget data in the same spreadsheet, so each month the "sum_range" column needs to change (ie column 4 for feb, 5 for March). I can use a find and replace to address a different column in my equation, but my boss would prefer something simpler.

View 9 Replies View Related

Summarize Results Onto 1 Row Per Person

Feb 2, 2009

I am trying to write a series of macros to automate the creation and population of a summary tab for transmission of patient results to our Laboratory computer system and have hit a bit of a brick wall. One of our analysers creates an Excel workbook for each analytical run with each patient sample analysed on the run assigned to a separate worksheet. These worksheets contain the patient ID (lab number) in cell C1 with results for the different analytes in cells F7 to F56. We need to be able to present this data with each patient having a row to themselves (i.e. Patient ID for patient 1 being in cell A2, test result 1 in cell B2, test result 2 in cell C2 etc, then patient 2 taking row 3 etc). Row one is populated with the field Sample name in cell A1 and the cross-referenced test IDs running across the rest of the row. As analytical runs can contain varying numbers of samples we need the Summary tab to be able to cope with a indeterminate number of worksheets.

View 2 Replies View Related

Summarize A Table Of Data

Jun 20, 2006

I use INDEX and MATCH equations quite frequently now and they are invaluable! However what I'm trying to do now doesn't quite fit. I've attached a small sample of my workbook. The sheet called 'data' has three columns of data that I need to split into a grid (called summary). I'd know how to do the opposite of this, as in changing from a grid to a list by using an index and match. But I'm not sure how to do this the opposite way around?

View 3 Replies View Related

Summarize Data In Another Layout

Aug 25, 2006

I have a data feed that gives me a summary of a set of data. I want to be able to work back to what would be the original data (this unfortunately isn't available). The attached excel file as an example of what I am trying to do.

I am not too sure about the working with the dates etc.

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

Summarize 1 (spreadsheet) To Sheet2

Jul 2, 2007

I am trying to summarize sheet1 (spreadsheet) to sheet2. On sheet1 of my workbook I have row 1 as the part description, row 2 as the part number, and column A as the Door Description. Once the user enters a Door name and a quantity for the part in the associated column and clicks the Summarize button the selection needs to summarize on sheet2. I have the start of a code which gets me what I need however I am looking for a quicker way to do this. With my code I will have duplicate this for every row and column. Also I have attached the file for referance.

If Worksheets("Sheet1").Range("A5") > 0 Then
With ActiveWorkbook.Sheets(1)
Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet1").Range("B5").Offset(0, -1).Value
Worksheets("Sheet2").Range("A2").Value = Worksheets("Sheet1").Range("B5").Value
Worksheets("Sheet2").Range("B2").Value = Worksheets("Sheet1").Range("B5").Offset(-3, 0).Value
Worksheets("Sheet2").Range("C2").Value = Worksheets("Sheet1").Range("B5").Offset(-2, 0).Value.........................................

View 3 Replies View Related

Report & Summarize Counts

Sep 25, 2007

Refer to the attached sample workbook. I need to track daily tasks of our staff and then indicate these tasks on the "Activity Report" sheet.

Each task will have a code and I will indicate; by menas of the code, which activity was done by inserting that code in the time slot provided.

Once each day is completed; I need to tally up the amount of each activity that took place and it will be diplayed in the "Specific Activity Summary" section of the sheet.

Also; once the tally for each activity is completed, the amount of time spent on that activity will be indicated on the "Daily Activities Summary" sheet.

How do I go about linking the specific cells to be able to create this "tally" and "summary."

View 9 Replies View Related

Summarize Data From All Worksheets

Oct 4, 2007

Im creating a workbook for my office. The workbook has a sheet for each user. The user chooses a channel from the drop down list, then complete the next 5 fields (must add to 100). I have a totals sheet where I want to summarise the data from the whole workbook as per the template on the totals sheet. Ive attached my example.

View 3 Replies View Related

Summarize Worksheet Table

Dec 2, 2007

This data consist of a long list of staff id with exception type and points. However, I have only a certain number of staff from the long list which is applicable to my department. For example, I only need data for 4 staff from the list. This list will duplicate the staff id based on dates. Therefore, there might be several cells with exception type and points for that particulat staff . Example is for staff M41 as below:

Date Staff ID Exceptiontype Points Item#
Dec 1 M45 Contract 1.0 44556
Dec 1 M41 Contract 1.0 44578
Dec 2 M43 Contract 1.0 44579
Dec 3 M46 Contract 1.0 22569
Dec 4 M41 Contract 1.0 44572

Based on the above data, I need to summarize as below:

Staff ID Exception Type Points Exception #
M41 Contract 1.0 44578
M41 Contract 1.0 44572

View 5 Replies View Related

Summarize Data By Criteria

Apr 11, 2008

I have the attached spreadsheet that has 122 columns I would like to create several common button at the top left hand side to summarize my information. The columns that I would like to are name, id, partner.

View 2 Replies View Related

Translate Yes Or No To Digit And Summarize In YTD Sheet

Apr 23, 2014

I have excel workwook with Month sheet Jan,Feb,Mar....Dec and YTD

In each monthly sheet i will have either "yes"or "No" in cell B66:K75.

Below is what i need to do in YTD sheet cell B66:K75. Each cell in TYD B66:K75 will have 12 digit number

Need to go thru each monthly sheet for each of the above cell and find if Yes or No and translate that into number (yes = 1, no = 0)


If B66 in Jan, Feb and Mar is Yes and B66 in Apr thru Dec is No then the number in YTD in cell B66=111000000000

View 5 Replies View Related

Summarize The Red Highlighted Parts Of The Code

Jun 26, 2009

I have the following code used to fill a listbox control .. and I added some conditions to the code in order to give special authorities to specific users depending on their position and unit? Authority, UserPositon and UserUnit are functions give the user's position, unit and his authority. Can we summarize the red highlighted parts of the code because I am going to use these parts in many other forms of my project?

View 3 Replies View Related

To Summarize On List Of Data - Can't Pivot

Aug 21, 2007

I have included some example data and what I need to be able to show in the attached file.

I have tried using pivot tables but need to keep the totals static when I hide a column.

Can anybody suggest another way of achieving what I need? I need the solution to be dynamic (like a pivot table) but need to hide certain data and have totals including the hidden data.

View 14 Replies View Related

Compare Two Columns / Summarize Results

Jul 17, 2012

I have Columns A and Column B. I want to find a way to choose two items from Column A. Ex Coats and Shoes and return the values from Column B,but only if they match.

it would look something like this:

Criteria 1 Criteria 2 Results
Shoes Coats New York

Column A Column B
Coats New York
Coats Texas
Shoes Washington
Shoes New York
Coats California
Shoes Ohio
Shoes Texas
Coats Texas

the Results would be dynamic, meaning there is no gaps in the results,I am only interested in the a concise list that summarizes.

View 8 Replies View Related

Copyrights 2005-15, All rights reserved