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.
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
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.
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.
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
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.
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.
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
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
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.
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
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.
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........................
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)))
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.
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...
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)
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.
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)
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.
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:
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).
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.