Sum Cells From Rows With Same Criteria
Feb 1, 2009
Relatively new to advanced Excel and have browsed a bit but haven't found anything to help me. If a thread exists that answers my question, please link!
Short version: What I'd like is for Excel to know to add the value of G to the value of D of the previous row that has the same F value.
I have a spreadsheet that has financial data from three bank accounts, H, S and P. Columns in order are Date, Transaction, Category, Amount, Acct_Bal [for account balance], Acct [H, S or P], Acct_Avail [for available funds] and T_Avail [for sum of available funds of all three accounts].
At the moment, I am manually creating formulae in G (Acct_Avail). For instance, row 17 is the S account, and G17's formula is =SUM($G$9,D17), giving the account balance for S account after transaction D17, given the previous balance in G9, when that account was last used. I'm sure there's a better way, but this is beyond my ability.
View 11 Replies
ADVERTISEMENT
Sep 22, 2009
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
View 5 Replies
View Related
Nov 14, 2006
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.
The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)
Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".
Step 2:......................
View 5 Replies
View Related
Apr 26, 2008
I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.
(example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)
To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.
I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".
View 4 Replies
View Related
May 7, 2012
Each week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.
I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.
Next, in column C, I have...
Employee2's Name
Employee2's Number
SPACE
[Code]....
I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.
So, all I would have left in column C is
SUB TOTAL
SPACE
View 9 Replies
View Related
May 5, 2008
I did a search to find a question similar to mine and I found this:
Copying data matching a criteria into another spreadsheet
This is exactly the same concept I am trying to accomplish, but don't know how to...at all. I couldn't follow the thread either.
I have a schedule which is constantly being updated (simplier version attached). I want to create a schedule, which will:
1) First ask which region to display the information for (I would like to create the same pop up menu to choose from)
2) Then, it will select only that region's information (all rows & columns) and copy them into a new worksheet and lists it all
-if you go back to the Master List, you can again choose another Region and it will create another new worksheet with that region's information
3) Don't know if this is making things more complicated, but at the bottom of the attached file, there are 3 lines under "Land".
Is it possible to list these under the same spreadsheet as the region it is pertaining to but classify it separately under "Land" (ie leaving space, so it is clear this is separate
View 9 Replies
View Related
Jun 28, 2013
I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.
Create_List.xlsx
View 4 Replies
View Related
Apr 3, 2014
Code is trying to
1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
View 4 Replies
View Related
Dec 6, 2013
I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
I then want to count the number of rows (looking at column 1-3) with the following criteria:
Countif column2<= "date in colum4" AND column3< "date in column4"
View 6 Replies
View Related
Aug 28, 2007
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
View 2 Replies
View Related
Mar 5, 2008
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete
End If
Next Row_Counter
End Sub
View 9 Replies
View Related
Jul 1, 2008
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
View 8 Replies
View Related
Apr 30, 2014
I worked on a countifs function, but i needed a average of column N.
the countif formula is
A2 = current month
=COUNTIFS(
'Location'!J3:J999,">="&$A2,
'Location!J3:J999,"<="&EOMONTH($A2,0),
'Location'!M3:M999,"Name")
With the exceptions from above i need to get a average from column N.
View 4 Replies
View Related
Jul 31, 2014
delete rows if two conditions are met .THe sample data looks as shown below:
I need to check for a given resource where the allocation hours are highest against which project , and then delete the rest of the rows for that resource records.If the Allocation hours are the same , then the priority column should be considered the highest priority should be included and the rest to be deleted.And if the priority is also the same then need just one record to remain rest of the rows to be deleted.I have priority of business area ranging from
Resource NameResource IDProject NameBusiness areapriorityAllocation hours
Lavanya 123243 MarketingSales 145
Lavanya 123243 Sales Sales 1150
Mukund 5678 Re engineeringRetail 210
Mukund 5678 StreamliningGeneric 990
Mukund 5678 documnentationDocumentation 850
View 5 Replies
View Related
Oct 3, 2013
I have rows like below in Column A. I wish to cycle till row 700 and delete rows where Right(A2(,1)="X") and also Rows where there is a blank Cell.
5
22X3
65
260X
70X5
8X66
873X
2206
059X
088X
90X8
2283
7606
View 6 Replies
View Related
Jan 19, 2014
There are two workseets involved. Inventory is the main and Pending is the second. There are 28 columns of data in each that match based on row 1 headers. I need to figure out how to cut all the rows with "Pend" listed in Inventory column Z to one row below the data in column A of the Pending worksheet. The data can be dates and also notes but no matter what is in column Z it must be cut and moved.
View 6 Replies
View Related
Jul 20, 2009
I have the following table, which starts in Column A, row 2. I would like to have a macro, that deletes some rows after certain criteria.
If Column F = SO normal and
Column E = aa, bb, cc, dd, ee, ff then delete the whole row.
GeoRgn refSub Rgn refCountry Country 2GroupENNR3NxaaSO claim-inENNR3NxbbSO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3NxaaSO normalENNR3NxbbSO normalENNR3NxccSO normalENNR3NxddSO normalENNR3NxeeSO normalENNR3NxffSO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx222SO normal
View 9 Replies
View Related
Oct 23, 2009
I've got a file set up like this:
1001 Value 1
Value 2
Value 3
1018 Value 1
Value 2
1023 Value 1
Value 2
Value 3
Value 4
1029 Value 1
Value 2
Etc.
Now I want to delete the sections where the 1000-something value ends on 3/4/7/8 and keep the sections where the 1000-something value ends on 1/2/5/6/9/0 So the result would look like this: ....
View 9 Replies
View Related
May 12, 2007
I am trying to set formula to change row colour when certain criterias are met the options must be availiable for all rows but Governed of one colum.
Eg. If Cell A1 = "Contacted" then row will change to colour Green,
"Left message then row will change to amber
"Not contacted" then row will change to Red
"Do not contact" then row will change blue
If these can be set as drop down menu to save operator typing them that's even better.
Once one is set I can copy the formula into the remaining collums.
View 9 Replies
View Related
Nov 20, 2007
i need a macro for inserting a row in sheet by criteria. the Criteria:
1-checking if Collumn G ( letters ) are equal
if not then inserting the row
2-cheking if collumn H ( numbers ) are equal
if not then inserting the row
one more thing i need the row to be yellow from collumn B .. to .. I
View 4 Replies
View Related
Dec 9, 2007
I have an excel 2007 file, which is quite big: ~10mb. There are situated plenty of columns with coordinates. I would like to transcribe it, modify those data, but it will take me ages. I'm not acquainted with macros, but I heard that people can do with them everything using computers' abilities. I'll abridge my whole problem.
I give you a sample of that file, so please open it. In the file you can see two 'columns'. Every column has two other columns called x and y. As you are thinking now these two columns are containing arguments (in x column) and their values (in column y). Every third column is empty. In attached file you see, that arguments are repeating and values are increasing by one. Sometimes there is an argument without repeats, and sometimes there are only two repeating arguments.
What I wanted to do is delete those repeating arguments leaving arguments with the highest and the lowest value. Example: Please look at range A230:B232. There are three repeating arguments and for this case I should delete row 231 from columns A&B and then move cells up to join separated columns. There are also not repeating arguments, which I need to double. If there will be only two repeating arguments I would like leave them. In the end I should have a clear view for all those coordinates.
View 8 Replies
View Related
Apr 14, 2014
I am new to macro and just trying to learn. I have a spreadsheet with 20000 rows and 8 columns. I am trying to identify equal rows based on the values of columns C, D, E and F. then I need to separate equal bunches with a blank row. Then I need to copy the ID number from the first cell of column B of each bunch and paste it for the rest of the rows in that bunch. I have written the following code but this does not put the ID of the first cell in a bunch for the rest of the rows in that bunch.
[Code] .........
View 10 Replies
View Related
Feb 13, 2014
Check the file for reference.
Basically, I want to conditional format(fill entire column with a color) if the numbers of non-empty cells for any given column are more than 2 for each team (labeled Alpha, Delta, Office, etc). So what I want to do is to count how many rows contain a letter (V or P) in a team (in column A) and if it exceeds 2, I can conditional format the entire column. I believe I know how to conditional format. I just need to get the counting function going.
Example.xlsx
View 2 Replies
View Related
Sep 8, 2009
i have several student ID results that have conditional formatting in place. I'd like to KEEP the following data but delete/hide everything else:
1) Filter out purple rows
2) WITH filtered rows: KEEP any scoreA that's 100% or less than 85%
3) Look at Score A and Score B - KEEP if the difference is greater/less than 5%
4) If a student took testB but didnt take testA, KEEP.
View 2 Replies
View Related
Dec 15, 2012
I am looking for a macro to delete rows that DO NOT contain criteria. But what I could achieve so far is deleting all rows except the first word ie ACC here. My code so far is as follows
[Code] ..........
View 12 Replies
View Related
Jan 30, 2013
I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)
View 5 Replies
View Related
Feb 2, 2009
I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.
View 5 Replies
View Related
Feb 14, 2009
I simply want to delete the last 10 rows containing date from a worksheet. No criteria other than they contain data anywhere in the row.
View 4 Replies
View Related
Sep 24, 2009
I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool.
The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error).
The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...).
What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as:
"T" "["
"T" "#"
"T" "@"
"T" "]"
The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ).
I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).
View 8 Replies
View Related
Oct 6, 2009
What am I doing wrong here?
I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.
View 12 Replies
View Related
