i would like to find code for a macro(s) to delete selected columns and delete row by partial string..
due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..
I currently have a speadsheet that looks similar to the following:
REF DATA1 DATA2
SVRW_67 784598 475395u
SVRW_34 fdsjsjdf 734978
CAT_56 ghdrhad gaghadh
CAT_67 48578 8943539
I'm trying to create a macro that will delete all rows with "CAT" in the REF column.
I've searched this site and struggled trying to adapt other methods listed and got nowhere.
I am using VBA
I have an input box pop up to ask the user for a base item number. The way the excel sheets part numbers work for example is TM-T88VP-GRY, the last part 'GRY' changing based on the variation of the item that someone ordered. So, the base item number would be TM-T88VP, and what I want to do is search through a set column (Column G) and delete any row that contains that base item number.
ertret.jpg
I am trying to delete rows that contain the same addresses BASED ON COLUMNS.
Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.
I want to keep the ones that DO NOT MATCH.
P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH
I am looking for a macro to delete entire rows based on the a partial text contained in column A. For example, I have a list of names that I need to keep but I need to delete the rows in which column A contain cells starting with the following.
BOOKLET 201, PROMO
LOTTO, PRIZE 2
PBT 2, STW WEEK 4
the numbrs in these cells increment therefore I can not make it a specific to the cell but need to select based on Booklet, Lotto and PBT.
I need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
I saw a post with a formula of =LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$A1&" "),KeyWords) which I thought might work, but it returns an error.
I'm trying to search on A1 and return the correct name from a named range called KeyWords.
A
B
C
D
1
Chicago
#N/A
Chicago IAP
[Code] ......
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
Application.ScreenUpdating = False
Range("B:B").Select
For Each c In Selection
If c.Value = Range("A4") Then c.Rows.Interior.ColorIndex = 36
Application.ScreenUpdating = True
I got this code so far that highligts the rows that match but range a4 needs to go down the column to a5, a6, a7....so it only checks the first cell in column a. I need it check all cells. Then i was going to delete the entire row in column B where it was not highlighted.
column "b" in the attached file has numbers that match some numbers in column "c" I need to delete all the rows that done match the numbers in column "c" that are used in column "b"
for example no " 53" is in column "c" but not column "b" so they need deleting
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 RelatedI'm trying to put together a script which on the sheet "Resource" compares every cell in the range B:U, starting at row 8, and where all cells match, keep the first row, but delete the duplicate proceeding rows.
I've found the code below here: [URL] ...., which I thought I may be able to adapt, but for me to include all the columns in the 'If.Evaluate' section of code seems to perhaps not the most efficient way of doing this.
[Code] ......
I have a file which has some data I want to keep and some data I want to strip out.
The data I want to keep will always have either a date or a string “Overdue” or a string “> 1 year” in the first column, anything else I want to remove.
Example file shown below
23-Apr-0812341234123412341234
30-Apr-0812341234123412341234
19-May-0812341234123412341234
27-May-0812341234123412341234
> 1 year12341234123412341234
Overdue12341234123412341234
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I have over 2000 rows with 20 columns and i need to find and remove identical rows. I can do a match with MATCH() by concatenating the columns and comparing them as a whole but i need to apply this to the whole table and i need it to remove all the duplicate rows it finds.
View 5 Replies View RelatedIn cells A1:A10 I have the following list;
FLOUR
DOUGH
CHICKEN
BEEF
PEPPERS
TOMATOES
CHEESE
ONIONS
POTATOES
SALAMI
I want to be able to get a partial match that will allow for spelling mistakes by the user. The wild card (*) seems to only look at the left-most text and return #N/A if the text isn't matched sequencially.
For example ....
I have a column with a different alphanumeric strings in each cell. Some of these values have a portion that is common. For e.g.:
Example kealey 1980
Testing
Reference
Example john commission
Example (hard sell)
Is there a formula to return the number of cells that have "Example" in them?
column A
MARY
JOHN
MARK
SUE
cell B1
zzzJOHNxx
I am looking for a formula (Vlookup, Match, etc) that will look for an entry in column A that matches part of the string in cell B1 (not the other way around i.e. using wildcard)
How do you do a vlookup with a partial match?
View 5 Replies View RelatedI have one list of waybill numbers which are 12 digits long and I have another file of waybill numbers which are 15-16 digits long. I have tried a VLookup but I do not get matches on these since the digits are not the same length. Is there any other formula I can use to match a 12 digit waybill against a 15-16 digit waybill number? I am assuming that if 12 digits match in both lists that it is an exact match.
View 4 Replies View RelatedI have data below, what I am trying to do is to put an "x" if the data has a partial match. This is what I am using below.
HTMLSheet1 Â ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOLÂ 2Electrical Magnet SchoolELECTRICALÂ 3Sports Magnet SchoolCOLUMBUS SCHOOLÂ 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOLÂ 6Abe Lincoln SchoolLINCOLN SCHOOLÂ 7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOLÂ 9Abe Lincoln SchoolLINCOLN SCHOOLÂ 10Abe Lincoln SchoolLINCOLN SCHOOLÂ Spreadsheet
[Code] ...........
What I would like is this
HTMLSheet1 Â ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOLx2Electrical Magnet SchoolELECTRICALx3Sports Magnet SchoolCOLUMBUS SCHOOLÂ 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOLx6Abe Lincoln SchoolLINCOLN SCHOOLx7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOLx9Abe Lincoln SchoolLINCOLN SCHOOLx10Abe Lincoln SchoolLINCOLN SCHOOLxSpreadsheet FormulasCellFormulaC3=IFERROR(IF(SEARCH(A3,B3),"x",""),"")C7=IFERROR(IF(SEARCH(A7,B7),"x",""),"")
I am trying to create a lookup table that will lookup parts of a colum and return the second column in the table. For example, column A has a lot of different titles in it, they each contain different segments and codes in them but I need to see if NM is in the list and if it is I want to return a 1.
Column A Lookup COLUMN Lookup Result
NM01A NM 01
NM02B IN 02
IN01A
IN02B
I need to see if what is in the lookup column is in column A, and if it is then I need to return the lookup result.
i need some formula to lookup with partial match "name", like this below :
name
property code
partial name (manual type name)
desired result
jim bob
1988.08.000250
jimmy bob
1988.08.000250
[Code]...
etc...
it's possible with excel formula?
select first the 2 ranges i want to compare
select the columns used like criteria
compare and find the posible match and copy on an new excel the result
exemple
possible matching
28244654-500410428244654Y71J
comun value 28244654
same
123abcabcd
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies View RelatedHow to check for a partial match looking in one cell, A1 for example, and checking to see if the contents of B1 show a partial match (example attached).
I can't use the vlookup because I only want to look at cells specifically, not a table/list. Please also note that the attached example has no formulas.
Forum Example.xlsx‎
I have a long list of categories in the column A (seen below). To this list i want to assign keys that will support with further calculations (in a column B).
An example of such a key would be in the case of the given example "ACTION SPORTS" and "FOOTBALL". The whole list of these "keys" hast 17 elements.
It is evident that i will need some kind of a partial match. The one i have found on the internet, limittin itself just on the first or last number of characters wont work.
Would it be possible to select my "key - list" (all 17 of them). Then excel would asign a key, whenver it would find a partial match, matching the key?
Appendix:
The Data list (Column A):
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
[code].....
I have strings in cell a1. I will put a formula in a3 if string "xyz" is found inside a1, it will show value from a2, else shows nothing.
i tried simple formula cell a3
=if(a1="xyz",a2,"")
here it is exact match, i need partial match, or multiple partial match in a single cell. How to do it?