Adding Column Minus Rows That Have Data In Another Column
Feb 15, 2007
I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.
Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...
i'm not even sure if this makes sense lol... let me use example
on SP-B there's a column M.
in row 11, value = 3
in row 12, value = 5, in this same row column E is filled with whatever
in row 13, value = 2, in this same row column E is filled with whatever
in row 14, value = 6
in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.
View 9 Replies
ADVERTISEMENT
Mar 13, 2014
I am dealing with date ranges with the data being on a second tab of the Excel workbook. The first tab is to populate a report of the difference in dates. I need to count the number of values are less than 3, after I subtract Column P from Column K.
Raw Data'!P:P is a Particular date and 'Raw Data'!K:K is another date. I am trying to find a count of all of the rows where the difference of the 2 dates is less than 3.
Here is a couple formulas that I tried.
=COUNTIF(IF(('Raw Data'!P:P-'Raw Data'!K:K)
View 5 Replies
View Related
Oct 7, 2008
I've got an excel sheet which has names in column A followed by 5 numerical values in columns B-F. I'd like to get a macro that will compare the values in Column A and if they match, add the B values, the C values, the D values, the E values, and the F values and delete one row - effectively combining the row. What's the best way to go about this? Also the sheet is already in ascending alphabetical order by Column A, so the rows that need adding will be next to each other. So I guess compare each A value with the one below it and if they match add the rows, but how?
View 5 Replies
View Related
May 12, 2014
I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
View 4 Replies
View Related
Feb 6, 2009
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
View 14 Replies
View Related
Mar 2, 2011
I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
View 7 Replies
View Related
Dec 30, 2013
I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:
Pounds
=SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)
Pence
=MOD(SUM(E3:E8),100)
These work very well and give me the correct figures.
What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:
When added together the values of rows 3,4 & 6 are 7.25 (based on a value of "W")When added together the values of rows 7 & 8 are 5.00 (based on a value of "F")
I have tried to show this as an example in the image below:
A
B
C
D
E
[Code]....
I'm using Excel 2010 on Win 7 Pro.
View 3 Replies
View Related
Sep 19, 2012
I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.
The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.
This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).
For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units.
In points, the difference is 420 vs. 386.25, or 33.75 points.
Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.
Then (10-1) margins allowances time 3.75 points resolves the difference.
Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design.
For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10).
Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.
I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.
View 1 Replies
View Related
Nov 5, 2013
I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.
Example.xlsx
View 1 Replies
View Related
Jun 18, 2014
Anyway, I have a column graph that shows company totals on the left, in three columns, and sub-group totals to the right. These columns represent Revenue Targets (Blue), and current projections (Red) as of the current time. Ignore the green column. I would like to add incremental amounts to the Red column weekly, as the projections change. So I want to show an increment on top of the red column.
So if LG picks up 100 of additional revenue, I want to show the increment of 100 on top of the 2000 that is there, using a different pattern or cross-hatch.
View 3 Replies
View Related
Jul 9, 2014
1.I've watched tutorials on youtube but the data entry was on the next row. I want that my data entry to input data on the next blank column. I'm having problem with the code to use
2. Any way that the P.O. number will arrange in ascending order.
Example : The last P.O. number was 005 then i've realize that I've missed P.O. number 003. And I want to input P.O. number 003 and it will be arrange.
View 1 Replies
View Related
Jan 30, 2013
If i have the following layout of data
Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
View 1 Replies
View Related
Jan 9, 2014
Is there a formula that will allow you to add an ascending suffix to duplicate data in a column?
For example:
1234
1234
1234
1234
1234[code].....
I've been trying to figure it out on my own for a while now with no luck...
View 7 Replies
View Related
Jun 19, 2013
I have a column with dates and times, and an associated column with data for all the dates and times. I want to add every hour of every day to the date/time column, but want the data associated with the times to stay in the row next to the correct time. How would I do this? In the attached file, i want to make coulmns A and B look like Coulnds E and F. I have hundreds (if not thousands) of rows, so i need a way to do this easily. Also, the times are not all evenly spaced or exactly on the hour. I dont need the times to be exactly on the hour or spaced out evenly, but i would like to have at least one time from every hour.
DBdate time alter.xlsx
View 2 Replies
View Related
Aug 13, 2014
I want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.
So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.
This would be repeated until all rows in B are complete.
Example
Col A: red
Col B: red
Col C: red
Col A: red
Col B: blue
Col C: not found
View 6 Replies
View Related
Nov 19, 2013
I Basically need to use DATA in Column D of my file to add a ROW and then use Column C to name that new ROW added...
Example: [URL] ........
View 2 Replies
View Related
Feb 16, 2009
Adding data in column A depending on data in B. I have following macro:
View 2 Replies
View Related
Jun 30, 2014
I have a excelsheet that looks like this:
Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept
Health Services
Internal Services
Public Works
Social Services
Los Angeles
3
12
New York
8
22
100
7
Chicago
15
56
4
View 8 Replies
View Related
Sep 11, 2011
I have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. How can I insert escalation date so as that it would deduct non working hours from it.
View 5 Replies
View Related
Aug 3, 2006
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
View 9 Replies
View Related
Apr 22, 2009
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies
View Related
May 29, 2014
I have set of data Pasted in 4th row, in the top row 44 columns values assigned i want move data from set of data to different column among these 44 columns
Like "Service Order ID" is 1 column in set of data ,it move to second column of top row
Some of column need to delete. (Service Order Type,Service Order Description,Created By,Status,Contact,Expected Delivery Date,
Creation Date,Priority,Net Value,Currency,External Reference,Reference Date)
I want Get output result in same sheet (Actual).
View 3 Replies
View Related
Nov 27, 2009
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
View 9 Replies
View Related
Dec 11, 2006
Our Excel spreadsheet organizes names, addresses, and the amount of money per account in the following manner:
Column A Column B Column C ....Column X
Row 1:"Account Name" "Name of Company" "Money in Accout", etc.
Row 2: "Address of company"
Row 3: "PO box"
Row 4: "More information or State and Zip Code"
Row 5:"Account Name" "Name of Company" "Money in Accout", etc.
Where the "Name of COmpany", "PO Box", "More information or state and Zip code" are all located in Column B.
We have anywhere from twenty to a thousand or so names organized like this going down a spreadsheet. In addition, the number of rows underneath a particular Company varies.
So, I was wondering if anybody knows if a VBA program could be written where the code would move the cells that are put under the name of the company (i.e., Row 2, Column B; Row 3 Column B) so they'd appear only on row 1? Ideally, this program would know when to start putting the information on, for example, row 1 or row 5 because there is an account name in Column B.
View 9 Replies
View Related
Dec 8, 2009
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
View 2 Replies
View Related
Apr 20, 2014
I student data in the following format
STUD ID, Surname, Forename, Subject
E.g
0001,Smith, Stan,Biology
0001,Smith,Stan,Chemistry
0001,Smith,Stan,Physics
Etc
I would like to display it so instead of there being multiple rows per student there is just 1 row with each subject in a new column
e.g
STUD ID,Surname,Forename, Subject 1, Subject 2, Subject 3
e.g
0001,Smith,Stan, Biology, Chemistry, Physics
Is there any way I can do this quickly for hundreds of students?
View 12 Replies
View Related
Apr 30, 2014
How to do this ? keep a running record in rows...
A---H I J K
1 4-4 8 2 6
2 8-5 6 1 4
3 2-8 3 2 5
4 6
5
6 5
7 6
8 1
9 4
10
11 8
12 3
13 2
14 5
View 9 Replies
View Related
Apr 23, 2013
I have a data set with raw data from an online survey with >2000 rows of data, with each row varying in width from 2 columns to 4 columns wide of data, located in columns A through D. Each row has the value 2 in it as well as some additional values on either the right or left side (the location of the value 2 varies -- so it's not always in the same column). Picture attached of some example data for clarification:
Picture 1.png
I want to figure out if there is a function that can align all of the 2's in the same column -- let's say Column C -- while maintaining the same data that was to the right or left of the 2 (up to two values on either side) before all of the 2's were aligned in the same column.
I could certainly manually align the central column by copying and pasting repeatedly, but that would take an incredibly long time -- and I am sure that there is a better way to do this. I can use a VLOOKUP function to align all of the 2's in a column as well as everything to the right, but I have tried using the INDEX and MATCH functions to do a VLOOKUP to the left, but this doesn't seem to be working because the source column (where the number 2 is) varies by row, so it doesn't match in the same way that a VLOOKUP function does.
View 5 Replies
View Related
Feb 22, 2014
figure it out thus problem with macro,
before/sample data :
name
address
gender
activity
code
[Code]...
after macro :
george
g
t
jas
[Code]...
it's possible with macro??
View 3 Replies
View Related
Jan 9, 2008
I have data that is all in column A there are 8 pieces of information.
So client 1 info is listed from A2-A9, client 2 from A10-A17 etc. etc.
I set up A1-H1 to be the headers necessary.
How do I go about moving A2-A9 to A2-H2, then A10-A17 to A3-H3 etc. down through all the contacts which go down to A2400...
View 9 Replies
View Related