I have an issue where I want to delete a row on Sheet1 if the cell in column a of that row is a match in a column A of Sheet2. If there is no Match, I want it to move to the next row until it has checked all rows with information. The size of the data varies daily. I do not want to check Row 1 either. Below is an example of what would happen.
I have an excel file, and on some rows, the "B" column contains the text " Total:" (it does have a leading space). I would like to loop through the first 200 rows (maximum length of the file) and delete all the rows that contain " Total:" in column "B". I have tried:
For i = 1 To 200 range("B" & i).activate If range("B" & i).value = " Total:" Then rows(activecell.row).select selection.delete shift:=xlUp End If Next i
When I step through that code, it just skips over the IF function as if B1 does not equal " Witness:"
creating a macro I have two sheets named customer list (I have only put in 30 rows as an example but some sheets have 400 rows)
in the sheet named list column "F" are the names it should be cross checked with column "F" in the sheet named customer if it is there entire row to be deleted
I need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2. Sheet 1 contains data in columns A to H. Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!
Sub DeleteRepeatedRows() ' goes through coord. sheet and identifies duplicated lines, copies the date from the old row ' copies it into the new line, then deletes the old line Dim rCell As Range For Each rCell In Selection If rCell.Value = rCell.Offset(1, 0).Value Then rCell.EntireRow.Delete End If If rCell.Value = rCell.Offset(2, 0).Value Then rCell.EntireRow.Delete End If.........................................
I have data sets on multiple worksheets within a workbook (over 70). Based on the begining of a string, I need to count all instances with that begining, and if there are fewer than 12 instances, delete the entire row.
Data set example:
In this example, I need to count each instance of rows starting with 1/* - 9/*, and delete rows as mentioned. In other data sets the string might start Gi1/*, and so on. In the following code, if I do not loop, and only test against 1 value type, and ONLY run it once, it works. As soone as I try to loop through all possibilities, or run the macro a second time, it blanks out the entire worksheet, starting at row 3.
Dim c As Long c = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row With Range("A3:A" & c) If Application.WorksheetFunction.CountIf(Worksheets(i).Columns(1), "1/*") < 12 Then . AutoFilter field:=1, Criteria1:="1/*" .Offset(1).Resize(.Rows.Count - 1, 1).EntireRow.Delete .AutoFilter
I have also tried to concatenate a variable with my CountIf criteria, but that fails miserably.
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?
I am trying to match data on two different reports. Both reports have month, location and supplier number. I would like to match a quantity on one report to information to another. Here is an example: (A-D are columns in excel)
Report 1: A B C D Month Location Supplier # Quantity
Report 2: A B C D Month Location Supplier # Quality
I would like to match the quality on the second report to the data in report one. I would have several months of data and want to match on month, location and supplier number. But there could be occurences where a match is unable to be made - for example having report with quantity but no corresponding quality on the other report for the month.
for all rows where data in column A matches, does data in column c & f also match? If it does, then put a 1 in column n; if it doesn't, put a 0 in column n
I have a large table that gives me the intersection points of many lines. I have used conditional formatting to highlight the intersection points that match by turning the text red, thus showing me when 3 or more lines intersect. Manually scanning the whole table is inefficient as I wish to expand it.
Due to size limitations, I've uploaded a shortened version of the workbook, just a copy with one example of a match.
How do I get excel to locate the matches (currently highlighted with red text) and list them in column I "Intersections" all neatly sorted?
I thought Advanced Autofilter but I don't think the criteria range can search for red text alone.
What's the best way to look at two sets of data in excel and make comparisons between them. For instance how many matches (and what are the matches). Workbook attached as an example.
so in b2 i want basically say if a2 matches a column in the client tab columns A:A then paste the username which is stated in the client tab in columns B:B
I got here different vessel name with corresponding vessel code and a amount. I needed to put the amount the to corresponding vessel if it matches the vessel code. Further explanation are in the attach excel file.
I have two lists of email addresses in columns and I need to find the matches between columns and have those addresses returned to perhaps a third column.
Students choose different electives, upto a maximum of 20. In the example below, I have shown them with only four electives. Some choices require a pre-requisite subject to be chosen. In the example below, let us assume that you must select WXY101 in your choices if you want to include WXY102 as an option as well. Jane Doe fits this criteria, so there is no alert.
However, John Smith is trying to complete WXY102 without having chosen WXY101, and hence WXY102 appears in the alert column as a problem selection. Note that choosing WXY101 without choosing WXY102 is OK, as you can just complete a pre-requisite subject.
I am trying to compare matches and differences in two lists in order to pull statistics from them.
I have a production schedule I make at the start of the week (list 1) and compare that to what I actually ran at the end of the week (list 2). I would like to produce the following from these two lists;
- a list (list 3) that shows all the orders I didn't run in my original schedule - a way of summing the number of matches (of work order #s) in the two lists (list 1 and list 2) - and way of extracting the corresponding machine hours I did / did not run from the two lists
I have provided text boxes on the attached excel file to better explain my problem.
I have a multi listbox which contains certain values from a column, how do I get the row numbers of every cell that matches a highlighted value in the listbox? There can be multiple instances of a value in the listbox within the column and I need all the row numbers.
Worksheet #1: Column "A" going down (starting at A1 to A5) I have the numbers 1,2,3,4,5 entered in each cell...
Worksheet #2: In cell A1 is the number "1" In cell A2 is the number "7"
I want a formula in cell B1 (WS#2) that looks for the number in cell A1 (WS#2) in the range of cells A1:A5 on Worksheet #1, and if it finds the value of A1 (WS#2) in that range of cells on Worksheet #1, it returns the letter Y... if not it returns the letter N
So my result on Worksheet #2 should be... Cell B1 shows the letter Y Cell B2 shows the letter N
Is it possible to use HLOOKUP without exact matches. e.g. < =HLOOKUP(E1,Actuals,2) > where E1 is "2009 October" and the lookup value in named range "Actuals" would be simply "Oct".
I am trying to use the vlookup fxn to display employee names according their in-time (10:30 for this example). I have multiple employees in at 10:30 so I need to find a way using vlookup to accomplish this.
This is a sample of my Master worksheet showing the schedule for all employees (Cell used for this example is D16):
My vlookup formula for finding the first match for a 10:30 in-time:
The other worksheets in this workbook are various Floorplans depending on how many employees are scheduled that day with a column designated to the employee's name(B), and a column for their in-time (C). Here is a sample of the '8 Server Floorplan':
Finding the first 10:30 match with vlookup is fine, my issue starts when trying to find the next 10:30 match, since vlookup only finds the first match (this is happening in B9).
Here is the sample of cells showing my formulas for finding the address of the first '10:30' match from my initial vlookup fxn (O5 and P5):
I thought by just finding the address of the first match (Master!D16), incrementing it by 1 (=D17) and plugging the cell containing this formula (P5) into my data range, that it would force the vlookup formula to adjust its data range from D4:W38 to D17:W38. This, in turn would then find the first match for 10:30 and so on for anyother identical in-times...
In the attached excel sheet see the sample example tables 1.1.1 and 1.1.2. I have to use it for official purpose and my manger has given this task to complete it. I cannot even compare the forumlas and make it same for all the tables available.
I have a chart in sheet1 with 4 Code (EXPL: 0349) in a cell A2 and in Cell B2 to b1900 reasons against cell a2 so i need all matches against a2 in Sheet2 cell a2.
