VBA To Delete Matches
Oct 8, 2008
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.
BEFORE MACRO:
Sheet 1 Column A
ID#
A
B
C
D
Sheet 2 Column A
ID#
A
C
D
AFTER MACRO:
Sheet 1 Column A
ID#
B
Sheet 2 Column A
ID#
A
C
D
View 9 Replies
ADVERTISEMENT
May 21, 2008
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:"
View 3 Replies
View Related
Mar 29, 2014
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
View 3 Replies
View Related
Mar 22, 2011
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.
View 4 Replies
View Related
Sep 6, 2006
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.........................................
View 7 Replies
View Related
May 5, 2008
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.
View 6 Replies
View Related
Apr 29, 2009
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
View 3 Replies
View Related
Jan 29, 2010
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"
View 4 Replies
View Related
Aug 18, 2009
Does anyone have a macro that will delete entire row if column a matches column d
View 6 Replies
View Related
Jan 28, 2009
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?
View 3 Replies
View Related
Dec 10, 2006
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.
View 9 Replies
View Related
Nov 14, 2009
If a row starts with T3:X3 matches up with the rows in range T6:X8605 then goto that matching row... perhaps could be done with a macro?
View 9 Replies
View Related
May 6, 2007
I need a formula that will do this:
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
View 9 Replies
View Related
Aug 12, 2012
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.
View 6 Replies
View Related
Aug 3, 2014
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.
matches between data.xlsx
View 8 Replies
View Related
May 1, 2014
So
Client Ref
a212198
a312365
a412546
a512355
a612656
a712198
a812198
a912198
a1012355
a1112355
a1212355
There is a tab a tab called client
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
View 4 Replies
View Related
Jun 20, 2014
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.
View 9 Replies
View Related
Oct 27, 2008
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.
View 2 Replies
View Related
Aug 5, 2009
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.
View 5 Replies
View Related
Sep 21, 2009
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.
View 3 Replies
View Related
May 16, 2014
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.
View 5 Replies
View Related
Nov 16, 2006
The table i'm looking up contains 2 rows with a value which matches my lookup value, how do i get the vlookup to pick up both values?
this is what i have:
ACEUSA-0017,730.84
SISTRUINSU-0018,865.69
WARNORAMER-00212,868.59
ACEUSA-00113,948.61
This is what vlookup picks up:
ACEUSA-001 7,730.84
ACEUSA-001 7,730.84
using this formula:
IF(ISNA(VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE)),0,VLOOKUP(I13,'May 2006 MJ'!$A$5:$B$41,2,FALSE))
This is what i need it to pick up
ACEUSA-001 7,730.84
ACEUSA-001 13,948.61
I do need ISNA to take care of #N/A
View 14 Replies
View Related
Aug 26, 2009
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
View 2 Replies
View Related
Oct 27, 2009
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".
View 4 Replies
View Related
Dec 12, 2011
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:
Code:
=VLOOKUP(C5,Master!D4:$W$38,20,FALSE)
MasterABCDEFGHIJ9AndrewSmith10:30BQT10AnnaSmith11:30CBQT11:30C5:30C11:0011BrianSmith12Brittany
Smith11:30C11:30C11:0013CassieSmith11:30C5:0011:005:0014
DanielleSmithBARBAR5:30C15DemirSmith5:0016DerekSmith10:305:00OC5:0011:30CExcel 2010
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':
8 ServersABCDEFGHIJ52Derek10:3023243334632625353674Anna11:30C272892938511:30C4142615196#
VALUE!10:3043446352Excel 2010Worksheet FormulasCellFormulaB7=VLOOKUP(C7,Master!D4:W38,20,FALSE)A5=A4+1B5=VLOOKUP(C5,Master!D4:$W$38,20,FALSE)
A6=A5+1A7=A6+1A8=A7+1A9=A8+1B9=VLOOKUP(C9,Master!D4+P6:W38,20,FALSE)
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):
8 ServersOP3Cell Loc.Cell Loc. + 145D16D1767D1189Excel 2010Worksheet FormulasCellFormulaO5=ADDRESS(ROW(Master!$D$4:$D$38)+MATCH(C5,Master!$D$4:$D$38,0)-1,COLUMN(Master!$D$4:$D$38),4)P5=ADDRESS(ROW(Master!D4:D38)+1+MATCH(C5,Master!D4:D38,0)-1,COLUMN(Master!D4:D38),4)P7=ADDRESS(ROW(Master!D4:D38)+1+MATCH(C7,Master!D4:D38,0)-1,COLUMN(Master!D4:D38),4)
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...
View 9 Replies
View Related
Aug 25, 2012
if a value appears in a column 4 times or more how can you do a vlookup which will show the 1st 2nd 3rd and 4th values.
when i currently write the vlookup formula it shows the 4th time the value appears
=VLOOKUP(B5,B14:D1854,3,0)
View 9 Replies
View Related
Feb 14, 2013
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.
How can I attach the excel file...
View 4 Replies
View Related
Apr 11, 2013
IF A1 on sheet one matches the value in A1:A500 on sheet two, to display "Yes" in the cell on sheet one column B.
View 2 Replies
View Related
Mar 26, 2014
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.
View 4 Replies
View Related
Apr 1, 2014
I have a reference vertical table which can be up to 30 long (I just put 5 here to keep it simple):
Sheet 1
A
B
1
[Code].....
And I want to return the data in the B column depending on whether the data matches either of the words in the corresponding A column.
E.g. Sheet 2
C
D
10
laki
The formula in this cell would return B1 because C10 = A1
11
katla
The formula in this cell would return B4 because C11 = A4
View 4 Replies
View Related
