Highlight Rows Meeting Multiple Column Criteria
Apr 17, 2008
I have a large file of invoices, and some have been paid. The problem is, sometimes it is not paid against the correct invoice, so I have a lot of credit and debit that should offset each other. I want to go through the sheet and highlight all the rows that has a matching ID and an a 0 value when total amount column was added up to another row. I would like it so that it highlights a different color for every pair of offsetting amount, but the same color highlight is fine too.
View 2 Replies
ADVERTISEMENT
Dec 13, 2006
In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "PA" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "TX" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
View 5 Replies
View Related
Feb 29, 2008
I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.
View 8 Replies
View Related
Aug 10, 2009
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
View 2 Replies
View Related
Dec 18, 2006
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View 9 Replies
View Related
Jun 4, 2009
I have a large number of user inputs which is linked to a data sheet. In this sheet are rows to be kept and rows to be deleted. See attached example...
I need code to delete all other similar rows than the one with a number (1) in the field Complete. There can only be a number (1) in the field Complete in one of the rows.
But if there is no number (1) in the field Complete in any of the rows, no rows should be deleted.
The data sheet is to be transferred to Access afterwards.
View 3 Replies
View Related
Aug 11, 2008
I have searched for this topic, and I have read http://www.ozgrid.com/VBA/row-delete-criteria.htm, I don't really understand it though. I think what I want is simple, I want to search column A for cells containing the word " Total" - if any are found I then want to delete the corresponding rows.
View 2 Replies
View Related
Jun 26, 2014
I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.
I have tried different formulas, but fast running out of time
I have attached a modified sample from the main spreadsheet
I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.
View 7 Replies
View Related
Nov 30, 2006
I am trying to delete rows - based on 1 field in each row not being equal to ANY of the values stored in an Array. e.g. my array has 3 values, CAT,DOG,MOUSE
cells are:
A1 = CAT
A2 = HORSE
A3 = DOG
A4 = DOG
A5 = BIRD
i want to delete rows not containing CAT,DOG,MOUSE. I think the autofilter could be the trick but cant quite get my head around it.
View 5 Replies
View Related
Sep 3, 2007
Searching through hide/unhide topics seem to yield more complicated codes then my meager needs. Within my worksheet I have a column BD which contains a list of values,
ie:
BD
1
2
3
6
3
2
1 etc..
I've created a toggle button "HideRow", which I would like to use to hide/unide only the rows containing value "2" within column BD.
View 2 Replies
View Related
Mar 12, 2009
I have a worksheet that contains 2 Rn managers per 3 Site and 2 TSR managers per 3 Site.
I want to be able to sort these managers by Site into 6 tabs: 3 RN tabs and 3 TSR tabs.
I want to keep the original worksheet as is and copy the appropriate rows to the designated worksheets labeled SACRN SACTSR VALRN VALTSR and so on. The data range is A:M
The columns of value is A (JOB) and B (SITE) and here is the sample of the data
JOB SITE TEAM
RN SITE SAC GASTON SUSAN TEAM SAC BERNAL
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE SAC MCINNIS JOHN TEAM SAC SMITH
RN SITE SAC MCINNIS JOHN TEAM SAC LEMON
RN SITE SAC MCINNIS JOHN TEAM SAC JACK
TSR SITE SAC GRECO DAVE M TEAM SAC KEN
TSR SITE SAC GRECO DAVE M TEAM SAC ROGERS
TSR SITE SAC GRECO DAVE M TEAM SAC LOPEZ
TSR SITE SAC SHAVER KELLY TEAM SAC JONES
TSR SITE VAL FRANCIS TEAM SAC YANG
View 9 Replies
View Related
Feb 2, 2010
Broken off from:Delete Rows Meeting Criteria
Thats for that, the sheet in the workbook is called Ticket Handling and if i try to change the name in the VBA code it will not accept this.
It Displays the following error:
Compile Error:
Expected: End of Statment
View 8 Replies
View Related
Aug 7, 2007
I have the following code that works well on a single worksheet
Sub hide()
For Each rw In ActiveSheet.UsedRange.Rows
If rw. Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
End Sub
However I want it to run on each worksheet in the workbook when I run the macro. I tried this but it seems to loop continuously on the first worksheet. When I press escape the line "next row" is highlighted in yellow by the debug in the VBA Editor.
Sub hide()
Dim ws As Worksheet
Dim rw
For Each ws In ThisWorkbook.Worksheets
For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
'Next ws
End Sub
View 6 Replies
View Related
Aug 20, 2007
I need a macro to sort through a large table of data entries and remove all repetitive entries (entires are based on a number in column A with data entries in B through EY) so i need to locate repetive numbers select entire row and paste them in a new location.
But i cant sort the data as it will change the entire order and i need the data intact.
the user will specify which entry number to search for.
I know some sort of search and loop function would work but i just cant seem to make them work.
View 9 Replies
View Related
Sep 29, 2007
I'm trying to make my spreadsheet to show next hidden row by entering a text or number on the cells in the range of my code. It works from rows 67 to 135 but I canot make it work with an additional ranges on it. if i insert the same instruction 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' it shows an error that said ambiguos name detected.?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If RenglonAnt > 67 And RenglonAnt < 135 Then
If Range("C" & RenglonAnt).Value <> "" Then
Sheet1.Rows(RenglonAnt + 1).Hidden = False
End If
End If
If Target.Row > 67 And Target.Row < 135 Then
RenglonAnt = Target.Row
Else......................
View 2 Replies
View Related
Nov 21, 2007
Excel Range A1 - D500. Data located in column C. I would like to delete the rows in this spread sheet that do not contain any of the following 3 prefixes of part numbers from column C: X-SXP, X-SCM and X-SBP. There are many part numbers but only parts with these 3 prefixes are needed. Part numbers are 10 characters long after the prefix. If any additional information is needed, let me know.
I viewed the Delete Rows Meeting Conditions thread, but the input value in that code is for what you want to delete, and I need to delete everything not meeting the data I want to keep, which is about 30 different part numbers. Also, that appeared to only offer one input and not 3.
View 3 Replies
View Related
Apr 4, 2008
I have a macro that hides rows in my workbook if certain criterias are met. I'd like to add a criteria to the logic that only hides rows if the date in cell 17 is less than 3 months from today's date. The date in cell 17 is shown in this format: 25-03-2008.
Sub ForceHide()
Dim ws As Worksheet
Dim line As Integer
Dim Endline As Long
For Each ws In Worksheets
If ws.Name <> " Total" And ws.Name <> "Batch" And ws.Name <> "Summary" And ws.Name <> "PivotTable" And ws.Name <> "Fields" And ws.Name <> "RTP" Then
With ws........................
View 3 Replies
View Related
Jun 9, 2007
how a SUMPRODUCT function can resolve this. In this sample I want to have 1120-32188 & 1120-32188W be treated the same for the Total sum qty. In this case =10.
This will compute but how do I include items in column A that have 'W' suffixes for computing Total Qty? Formula: =IF(A2=A1,"",SUMPRODUCT(($A$2:$A$100=A2)*($C$2:$C$100)))
View 4 Replies
View Related
Jun 19, 2008
1) I have a original worhsheet_A which with header rows(1~6) and many columns.
2) In the sheet_A, there are 2 columns (ColumnN & S) which I need to set criteria (value>5).
3) If any row's data which meet the criteria (value >5) with either column N or S, copy all the entired rows to sheet_B following sheet_A sequence.
4) The sheet_B should have the same header row with sheet_A(row1~6).
Btw, is possible create a corn job trigger this macro to run daily 9am to send out the worksheet_B as email, so that the command button can be disregarded.
View 4 Replies
View Related
Nov 2, 2011
I would like to sum fields IF the STAFF Name is the same AND the PROJECT ID is the same...
I have included sample data below:
Example: Sum the total TIME for GRIFFIN, TROY for PROJECT 1 (1 row). Separately Sum the total TIME for GRIFFIN, TROY for PROJECT 2 (2 rows).
When this sheet goes live there will be hundreds of rows with many instances of the same project id and same Staff name.
Project IDStaffTimeInt AmtExt AmtInt TtlExt TtlTask SavingsTotal Project Savings1Griffin, Troy2.25$30.00$60.00$67.50$135.00$67.508501Harvey, Deborah1$30.00$60.00$30.00$60.00$30.001Johnson, Lenworth6$30.00$85.00$180.00$510.00$330.001Kelley, Marijane6.5$30.00$95.00$195.00$617.50$422.502Griffin, Troy12$30.00$60.00$360.00$720.00$360.008702Griffin, Troy17$30.00$60.00$510.00$1,020.00$510.00312$45.00$55.00$540.00$660.00$120.00240
View 3 Replies
View Related
Jan 15, 2008
I have what I thought would be a simple task, but after almost a day of searching this and other sites, I've gotten no where. I have two issues that are similar. First, I need to edit my VBA code to count the number of blank cells in a column. Here's the code I'm starting with...
View 3 Replies
View Related
Jul 25, 2012
A column W5:W9999 contains attendance from either of 0, 1, ........31 of employees.
How to count no of employees meeting certain criteria like:
Total no. of emp with values >0.
Total no. of emp with values >0 but < 16.
View 9 Replies
View Related
Jun 30, 2009
I have a "SUMMARY" sheet and then five other sheets. I need to be able to extract the data which is >0% from each of the individual sheets and capture the results in the relevant section of the "SUMMARY" sheet.
For example, the data from sheet "PAYG" would need to be entered into cells F148:AJ155.
In each instance it is just the "model" detail I need to capture (column D)
View 5 Replies
View Related
Aug 13, 2007
I am trying to count the number of times a code appears in column N, IF the corresponding cell on column T is blank. Column T either has a date or is blank and column N has a 4 letter code.
This is what i have been trying.
=SUMPRODUCT((CMRF!T:T=ISBLANK)*(CMRF!N:N="B2"))
I want the code in column N to match to the code currently in cell B2, i have also tried using "" instead of ISBLANK but i get # NUM! error as a result either way. the result should just be a number i.e. 400 (cells with the same code as B2 and no date in cell T)
View 9 Replies
View Related
Sep 29, 2011
Need to create a macro?
Delete rows where multiple column meets multiple criteria.
detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)
Those 2 criteria have to occur at the same time..
View 1 Replies
View Related
Jun 6, 2008
I Have workbook-1 with 5 cells and 10 rows and in 5 th (E) cell i have value of 1 or 0 .
i want to selectively copy entire rows which has value = 1 in cell(E) and paste these rows into 2 nd row(A2 cell) of workbook-2 using Macro
View 5 Replies
View Related
Aug 22, 2008
In the included sheet I have 5 groups of data with five categories in column A. Current, Plan, Plan Var, Prior, Prior Var. I have included the button “Show Options” that opens the userform I created and gives 5 options. What I want is the user to be able to select any number of these options and then upon “ok” the rows in the sheet that weren’t selected are automatically hidden. If the procedure is completed again and a differen set of options is selected I want it to unhide any hidden ones that were selected and hide any that werent selected.
So if just current is selected the sheet will show 5 rows of current and nothing else. If current and prior are selected it will show current prior current prior current prior... etc.
I have some hide code that I created in the file as well.
Sub NotCurrentHide() ...
View 5 Replies
View Related
Sep 17, 2009
I'm working with transcribed comments from patient satisfaction surveys. The goal is to produce a single Excel file that contains all of the positive comments from source files where physicians are mentioned by name. Our vendor notes this by preceding any person's name with an asterisk (e.g., *Dr. Smith or *Doctor Smith"). I've attached a sample source file (Ambulatory.xls), so you can see what I'm starting with (I've deleted the identifying data in columns B, C, and D).
I'm looking for the appropriate VBA code to cut and paste the row in one file (in this case, Ambulatory.xls) to a separate Excel file (Weekly Physician Comment Report.xls) IF the cell contents in column G of that row contain either of the following text pieces:
*Dr
*Doctor
Each week's file will obviously have an unknown number of total rows - and will contain lots of positive comments that DON'T mention a physician by name. In the file I've attached here, for example, there is only one such comment (highlighted in yellow). I simply want to cut and paste the rows with physician names into the new file. If you can provide me with the code to do this for this example file, I can repeat the process for my other files (there are 6 source files in all).
View 6 Replies
View Related
Oct 26, 2007
A friend is trying to change an entire row's color based on a specific cell's value in that row. He cannot use conditional formatting. This is the code he's tried, to no avail:
Sub temp()
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If Cells(Row, 25).Value = 4 Then
Rows(Row).Select
Selection.Font.ColorIndex = 3
End If
Next Row
End Sub
View 2 Replies
View Related
Sep 17, 2007
I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!
So, I have a daily/weekly down load of a spreadsheet (exported from access).
* I want to copy the values of column AK to A
* Then delete all entries in column D that equal 19, 20 and 6
* Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G"
* Then delete entire rows where the value in column A is a duplicate within the list
I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.
View 6 Replies
View Related
