Extracting Sub-total Results

Jun 5, 2007

I am trying to pull information from one spreadsheet into another. The unique identifier is used several times and I need to pull ALL of that data over. I using a vlookup currently but it is taking only the first line of the unique identifier and not the other lines that are on the original spreadsheet. Again, I need to pull all of the data from the original spreadsheet to the new spreadsheet. What would the formula for that be?

View 2 Replies


ADVERTISEMENT

Extracting A Delimited CSV Gave Weird Results

Oct 21, 2008

Data > Import External Data > Import Data

and specified that its a delimited text and the delimiter is a comma ",". One of the columns in the row has got a pretty long text and contains certain new line characters as well. After extracting the CSV the data in this particular column is treated as a different data entries.

Somebody please help. I have added the file in xls format since csv upload is not permitted.

View 6 Replies View Related

Filter Rows By More Than Two OR Criteria And Extracting Results Into New Sheet?

May 21, 2014

I need to filter a large database using multiple OR criterias.

Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.

I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.

Sheet1
CUSTOMER NAMES---CONSUMED PRODUCT---CONSUMED PRODUCT---CONSUMED PRODUCT
GEORGE--------------SUGAR-----------------SALT
MICHAEL-------------PEPPER-----------------CHILI
JACK-----------------BREAD------------------SALT-----------------COFFEE

View 1 Replies View Related

Total AutoFilter Results

Dec 30, 2006

I have a worksheet of data that I have created by combining several other workbooks into one using a macro. On this worksheet I run AutoFilter to select certain rows and then copy these rows to another worksheet, again using a macro. How can I get the macro to generate a total for one of the columns? I would like the Sum to be two rows after the end of the column with some kind of descriptor preceeding it.

View 6 Replies View Related

Separation Of Results Depending On + / - Total

Jun 20, 2014

I have a grand net income and net loss rows and underneath those are costs which are like the breakouts of the income and loss. I need a function that spits out a separate total income (meaning sum of all the positive numbers of those breakouts) and separate total loss( meaning the sum of all the negative numbers within those breakouts).

In addition, I have a separate table (on my spreadsheet it starts on cell B13 and that table has the total for each of the breakouts. I want to have a function on that table too that when the number is negative, the number will appear on the Net Loss column. If it's positive, then it appears on the grand income column.

View 1 Replies View Related

Calculate Matches, Total Results

Oct 16, 2008

In the attached sheet, I need to find all items in column D that start with "LAB". For those items, Multiply the corresponding values from column B x column E and return the sum of all to cell T1. Once the total is in cell T1, I need to then delete the values in column E for those items (or change them to zero). I dont even know where to start with this.

View 3 Replies View Related

Grand Total Of The Calculated Field Returns Wrong Results

Apr 24, 2008

i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..

buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..

i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula
"if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..

so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.

View 9 Replies View Related

List Unique Entries And Total Cells Based On The Results

Jun 10, 2008

Basically from Sheet OEE V20:V500 I have a list of problems being selected from a drop down list validation (which users can add to the list for new problems). Along side these "problems" in Sheet OEE U20:U500 I have a number which represents the number of minutes the problem caused them. Some cells in both these columns will however be empty if there was no problem occour. But wherever there is a problem selected, there will be a number alongside it, there will never be one without the other.

What I want to do is look down Sheet OEE V20:V500 and get two lots of information -

The unique problem names (no duplicates of the same problem) in Sheet Reports A1:A100 for example (I may change the range of this).
The number of occurances of each of the problems it lists in Sheets Reports B1:B100.
Count up the total number of minutes of each problem. So for every occurance of "Paper problem" there will be a unique number in Column U in the same row as the problem and place this in Sheet Reports C1:C100.

I would ideally like to have this as VB code as I am going to tie it into a command button which formats and prints my report page.

I have found various bits of code dotted about the forums for counting unique cell entries but they always seem to produce a list with lots of blank rows (I would like a list one after another without blank rows all over the place) and I'm really struggling to figure out how to make it count up the numbers in the adjacent cell of each entry it sees.

Just in case the list of problems for the cell validation in Sheet OEE V20:V500 is found in Sheet OEE AQ16 downwards.

View 9 Replies View Related

Navy Inspections - Running Total Of Each Sailors Inspection Results

Mar 26, 2009

Id like to have a running total of each sailors inspection results. So if he's had 5 outstandings, 10 sats, and 3 fails during his 18 week course, it will display this at the end of his row. Mind you, its a class of 30 for 18 weeks so there are a ton of cells. I tried multiple check box macros which assign them great but cant total the selections. Same with selection buttons.

View 9 Replies View Related

Calculating Total Time In One Sheet And Pasting Results Automatically In Another Sheet

Apr 21, 2014

I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......

I want that excel should automatically identify the site and calculate its outage with the following formula:

Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.

e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.

e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins

Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496

Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"

Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.

View 1 Replies View Related

Extracting Data From File In Directory And Extracting Filename

Mar 20, 2014

I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.

In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.

View 4 Replies View Related

Total Occurences: Formula That Shows The Total Payments Recieved For A Particular Month For A Particular Product

Jan 7, 2010

I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.

Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.

I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.

View 2 Replies View Related

Macro Allow To Total The Data On The Total Sheet Depending On What Unit Number Is Selected

Apr 22, 2009

This may not be the best way to do this, but I don't know Macros or Pivot Tables.

I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:

Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.

Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?

View 9 Replies View Related

Create A Spreadsheet That Will Calculate Total Money Spent And Total Savings?

Mar 5, 2014

I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.

View 3 Replies View Related

Add Each Day's Total While Displaying A Positive Number If The Total Exceeds 6000

Dec 21, 2008

I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.

View 5 Replies View Related

Formula To Multiply Total Number Per Category With The % Of Total?

Dec 13, 2013

number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.

View 2 Replies View Related

Running Total From Daily Worksheet Into Monthly Total

Jun 13, 2014

I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.

Colac Production.xls

View 7 Replies View Related

Results Show Results Of Formula, But Should Be Blank

Jul 27, 2006

I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.

View 2 Replies View Related

Add Current Total And Running Total

Oct 10, 2009

In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet.

I don't know if it is possible or not and am working on a pre-existing worksheet.

View 9 Replies View Related

Vba To Sum Total Of Column Not Total Of Range

Sep 13, 2009

Sub sumbotton()
Dim ar As Range
Dim rng As Range
Set rng = Selection.CurrentRegion
Set rng = rng.Resize(rng.Rows.Count + 1)
rng.Rows(rng.Rows.Count).Select
For Each ar In rng.Areas
ar.Resize(1).Offset(ar.Rows.Count) = "=SUM(" & ar.Address & ")"
Next ar
End Sub

View 9 Replies View Related

Change Total Formulas For All Tables At Once To Show Either Year-to- Date Or Total Year

Oct 15, 2007

I have a sheet in my workbook with at least 180 small tables, there may be more.
I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.

For example:
If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.

View 9 Replies View Related

Total Percentage Of True Cells Compared To Total Number Of Cells

Jun 9, 2014

I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.

View 9 Replies View Related

Change Formula To The Results Of The Formula For Cells That Have Results

May 9, 2006

I have a range that has formulas that are based on other fcells outside the range.

What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.

There could be a marco to run when this process is needed.

example attached

View 9 Replies View Related

Extracting Last Name

Oct 14, 2008

I am trying to extract the last name in a column and put it in a seperate column. Here is what my data looks like:

Joe B Smith
Jan Doe

I want to take out Smith and Doe. I found this formula on a post =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)), but it just returns B Smith.

View 3 Replies View Related

Extracting Min

Sep 22, 2007

I tried the =min(sheet1!B:B) but it pulls up the min of my entire column i need the min of each row This is what i got going on except there is 150 rows on this sheet. the rows are set with conditional format to highlite the min and Max of each row (which i cant show here!) ...

View 10 Replies View Related

Extracting Bad Data

Jan 5, 2009

I have two exported lists of data to compare from my newsletter program. One is the list of email addresses that I sent the newsletter to and the other is a list of the bounced email addresses.

Question: How can I extract the bounced emails from the list of sent email addresses?
In other words I want a list of clean, sendable email addresses.

View 10 Replies View Related

Extracting The Last 2 Digit

Jun 2, 2009

How do you remove the last 2 digit

A B
12345688 88
12548963 63
14552365 65

So in column B is would show

88
63
65

View 3 Replies View Related

Extracting Two Dates

Aug 15, 2009

how to extract this two dates (sample below) in separate cell and format it as like this: "January 1, 2009". "12/1/08 thru 1/1/09" and another question, is there any way, that i can make it as "JANUARY 1, 2009", it seems upper function is not working with dates.

View 4 Replies View Related

Extracting A Value From A Formula

Jan 29, 2013

I am calculating a range of results and I'd like to extract the values calculated and put them into a seperate spreadsheet then re-use to formulas to calculate my next set of results. Obviously copy+pasting from my formulas just copies the original formula and not the result of it.

View 2 Replies View Related

Extracting Values From PDF

Sep 23, 2013

I know that there are programs out there that will convert a PDF into excel, but what I need to know is, is there a way for excel to access values (numbers) in a PDF and then use those values in a worksheet?

So, if I have a table in a PDF file - containing part numbers, descriptions and quantities ordered, I want to be able to extract the part numbers and QTY, and have them populate an excel table?

View 11 Replies View Related







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