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


Sum Duplicate Descriptions When Using Array?

Dec 5, 2013

I am currently using the below formula to extract various fields from a spreadsheet:-


Which would be fine if the description in column B of my data only appeared once.

How do I sum the total of all those that have the same description?

Data Example

Column A Column B Column C

Code Description Value
SL10107 Test Data £100.00
SL10107 Test Data £100.00
SL10107 Test Data £100.00

Using the formula {=IFERROR(INDEX(Column C,MATCH(1,(Column A=SL10107)*(Column B=Test Data0,0)),"0.00")} I get the answer £100, i.e. it is Matching the Code SL10107, and then matching the Description of Test Data and then returning the first value, £100. However I would like to sum these entries and get the answer of £300.

View 1 Replies View Related

Copy And Add The New Descriptions Into The Master

Sep 16, 2009

I have a site that sells over 5000 products.

I have two sheets.
One is a Master which has all of the following information in each column.
Product ID = A, Product Name=B, Price=C, Category=D ,Product Description =E,

The other sheet Is a updated product description.

Now I want to Be able to copy and add the new descriptions into the Master but not sure how to add them so they will fall into the proper spot according to the Product ID... There are not descriptions in the Update sheet for all the Product IDs in the Master.

View 9 Replies View Related

Macro To Extract Specific Descriptions?

Aug 28, 2013

I have decriptions in Col B on sheet1. I would like a macro to do the following

1) Extract descriptions that contain the following text to sheet2 (including Cols A:B)

sale, sales, fact assist , discount, DIC & rebates, F & I (for eg sale pine desk, fact assist pine desk, discount retail desk, F & I sales)

2) Extract descriptions that contain the following text to sheet3 (including Cols A:B)

COS, O/allowance (for eg COS pine desk, O/allowance,

View 5 Replies View Related

Remove Descriptions On Pivot Chart

Apr 9, 2008

I have a chart which has two labels at the bottom under the x axis. Is there any way to remove these without removing the data?

View 9 Replies View Related

Removing Text From Item Descriptions On Mass?

Oct 28, 2013

I have about 10,000 part numbers all with descriptions. These descriptions do not follow a uniform logic;

Part 1: 5x10 Red Cotton candy
Part 2: Yellow 6/18 x7 TTC x11 Picture Frame

My dilemma is that I need to figure out a way to remove everything aside from the "5x10" and "6/18 x7 TTC x11" from these descriptions. I just need the the item sizing.

What I've come up with so far is sorting items by description and working with batches of similar descriptions, then doing replace "Red*" with "blank". It works but it still takes me too much time and it's not perfect.

My question is there something else I can do that is easier and more accurate?

View 2 Replies View Related

Inconsistent Text Lookup - Return Postcodes From A List Of Descriptions

May 21, 2013

I'm trying to write a formula which will return postcodes from a list of descriptions which aren't consistent in their layout.

For example, I need this to happen

UB3 3NQ - APR13
SW3 5RQ - APR13
Jul 12 - apr13 accrual - ME9 4FW
Mar 12 - apr13 accural - SO14 7P2

Returned to another column as,

SO14 7P2

The issue I'm having is that the postcodes aren't in the same place in order to use LEFT, RIGHT or MID functions, and they aren't always proceeded or followed by dashes or spaces in the same way.

I need the returned postcodes to come back in a uniform way so that any duplicates are grouped by the relevant pivot table.

View 4 Replies View Related

IF Calculation: Calculating Costs Of Products Ordered Which Imports Quantity And Descriptions From Another Sheet

Feb 9, 2010

I have a form for calculating costs of products ordered which imports quantity and descriptions from another sheet. The imported info and basic calculation of appropriate cost based on ordered quantity seems to work okay. These formulas are as follows:

First column: =IF(ORDER!A5>0, ORDER!A5, "") inserts the quantity ordered
Second Column: =IF(ORDER!A5>0, "Part Description and Part Number", "") inserts the product description

Both those formulas return a blank cell if nothing is ordered. Then using an outlying cell (G3) I enter the following formula to determine appropriate cost based on total order quantity e.g. $2.57ea if total ordered quantity is less than 50 and $2.37 if total quantity is greater than 50.. {Q: How do I put a third pricing level in here??}


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

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

Change Color Of Group Of Cells Based On Data In Another Group Of Cells

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

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

Copyrights 2005-15, All rights reserved