Macro Code To Filter Out 'Exact Matches' In 2 Columns
Nov 29, 2006
I have a large worksheet in drive C: called 'hits.csv' that has manufacturer part numbers in column G and column L.
I need macro code to find all rows in the worksheet where the part number in each cell in column G is exactly the same as the part number on the same row in the corresponding cell in column L. The macro should then delete/remove all rows where there is no exact match.
Note: The part number in every cell in column G is the only thing that is in each cell in that column. However just to warn you that the part number in every cell in column L is at the beginning of each cell, followed by a space and then a description of the part.
Once it has done the above, I would also like this macro code to open a worksheet called 'partnumbers.csv' in drive C:. It should then compare the part numbers in column A of this file with the part numbers in the above worksheet ('hits.csv'). The cells in Column D of the file 'partnumbers.csv' all contains a price. Where the macro finds an exact match on the part numbers in these 2 files, it should copy the price from the cell in column D in the file 'partnumbers.csv', into Column K of the other file, 'hits.csv' where the match exists.
View 9 Replies
ADVERTISEMENT
Mar 14, 2008
Most likely another very basic on...
2 worksheets.
Worksheet 1, holding all data in column C.
Worksheet 2, the value in column B.
In need for a function that list all exact matches of the values in worksheet 2 column B that are found in Column C of worksheet 1.
I got this VBA code that checks for the string in worksheet 2, column B, but it is a Instr, and I need a exact match and only the exact match. Is this difficult to modify and how?
Or would this be a regular array function?
(I would not need a macro, since the data change constantly and I prefer function as they update easily)
Function matchArray(ByVal testString As String, ByVal dataRange As Range, Optional IndexNum As Long) As Variant
Dim outRRay() As String
Dim rangeArray As Variant
Dim xColl As New Collection
Dim xVal As Variant
Dim rIndex As Long
View 9 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
Jul 24, 2006
I am getting #N/A errors even when I have an apparently exact match in my
table array to the lookup value. I know that excel requires the formats to
be exactly the same and I can force the match if I copy the lookup value from
my table array and paste it into the worksheet. Is there an easy way to
"fix" my table array. The table array is a reference worksheet I have
created which has worked in the past.
View 13 Replies
View Related
Feb 13, 2007
I have the following formula which, if text is in A1, will lookup from a list of worker's comp codes to locate the code in A2 and return the wording for that
=IF(ISTEXT(A$1),VLOOKUP(A$2,Codes!$B$3:$C$720,2),"")
However, if there is not an exact match to the code in A2, it returns the wording for the next closest code.
Is there a way to make it so that it will only return the wording for the exact match and return "invalid code" if the number in A2 is not found on the list on the "Codes" sheet?
View 9 Replies
View Related
May 24, 2014
I have a workbook that contains 3 sheets. What i am looking to do is:
1. Use the names in Sheet 1 (Column A) and find the names on Sheet 2(Column E).
2. If there is a match, put the whole row that contains the match on Sheet 3
View 14 Replies
View Related
Jun 19, 2013
Doing some job costing on our lowes invoice and am using SUMIF and asterisks to account for all the different names that get used for properties by the cashiers. An example of that range column is:
67TH AVE
19112 PONCA
51ST
STERLING
9420 67 ST
1503 SW 13TH
51ST
10818
19112 PONCA
19112 PONKA
I have formatted this whole column as text and get strange results from sumif when there are exact matches. For example the "10818" string is uniform through the spreadsheet and returns a result of 0.00 if entered as "*10818*" for criteria but returns the correct amount if I use "10818" or 10818 with no quotes.
It seems to be related to the text strings that contain just "numbers" as I'm having no trouble picking up exact matches when there are alpha characters.
View 2 Replies
View Related
Jun 23, 2014
I have a macro that I am working on which I have gotten stuck on. It compares Column A and Column C and any numbers which match are pasted into Column D.
What I am getting stuck with is I need to copy and paste the data for column B to Column E as well. So in other words.
If column A and B match then the number from Column A that matches B will be pasted in column D along with the value that is right next to it in Column B.
Attached File : MatchingMacro.xlsm
View 11 Replies
View Related
Jun 20, 2013
How can i filter a column to show me all the cells containing the word "on" when i try to filter i still get results like canyon or ontario etc which i dont need.
View 7 Replies
View Related
Nov 21, 2011
I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
View 2 Replies
View Related
Mar 5, 2014
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm
View 4 Replies
View Related
Mar 18, 2008
I have just started trying to learn VBA as its pretty cool but I am getting stuck on how to filter by non-blanks. It's probably something easy:- here is what i have got so far, but its not working
With ActiveSheet
.AutoFilterMode = False
. Range("A1:V1").AutoFilter
.Range("A1:V1").AutoFilter Field:=22, Criteria1:=0
.Range("A1:V1").AutoFilter Field:=5, Criteria2:="<>"
End With
View 4 Replies
View Related
Mar 30, 2008
I do not know VB coding but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.
The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually,
A1 2 3 100A
A1 2 3 100B
A1 2 3 100C
A1 2 3 100D
A1 100A AA BA
A1 100B AB BB
A1 100C AC BC
A1 100D AD BD
View 3 Replies
View Related
Apr 30, 2008
I have a set of data that I need to sort and filter and create pivot tables with 2-3 times per week. Example data sheet attached (confidential data removed). I will insert the code that I am using for reference. The issue is that when the data is filtered for 'Approved Timesheets' I need the filter to be based on 2 criteria; If myBaseRow.Cells.Item(1, 21) <> "Approved" Then If myBaseRow.Cells.Item(1, 44) <=StartDate And myBaseRow.Cells.Item(1, 44) >=EndDate Then
The first If statement works fine, but the second appears to be ignored?? I have tried to colour this part of the code blue to make it easily visible.
Private Function PTSubtotals(ByRef PTField As PivotField)
PTField.Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
End Function
Sub Timesheets()
'
' Timesheets Macro
' Timesheet Filter 4/22/2008 by Richard Francis
'
Dim myWorkBook As Workbook
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As Range
Dim myBaseRow As Range
Dim RowsCounter As Long
Dim StartDate As Date
Dim EndDate As Date
StartDate = Format(Date, "mm/dd/yyyy")
EndDate = Format(Date, "mm/dd/yyyy")
On Error Resume Next
StartDate = Application.InputBox("Enter start date", Type:=2)
On Error Goto 0
If StartDate > 0 Then.................................
View 3 Replies
View Related
Jun 28, 2008
This is quite a easy one. I am a begineer so I don't know how to do it. I keep getting a runtime error. I want create a macro that does the following. I have this data(it is made up).
NameSchoolFee
JohnFDU700
MarkeyNYIT400
NickNJIT500
SamBU100
I want it to autofilter school njit
NickNJIT500...........
What would be the code for this? I want use the code for bigger data?. Also if you have a file which is 100 lines long and your macro does a series of steps for that file. Then you have another file and you want the same steps done but it is 500 lines long. How would you write a macro thatrun the steps taking in the last line and not just a 100 lines.
View 2 Replies
View Related
May 9, 2008
the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code?
Sub myfilt()
Application. ScreenUpdating = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Jun 11, 2008
I've taken data from a fixed-length text file and the records are identified using the first two characters on each line of the text file. I've managed to identify these in each row with other bits of code so that was fine.
Now, I have 56 string variables (identifying my record types), which I'm assigning into an array. Then, I want to go through each variable in the array, performing the same action by way of a For Next Loop where, by taking this particular RecIDNo (see my code) string variable I will get the code to copy/paste the data into the relevant worksheet (where later on I will apply a text to columns using another array). But first, this set must work before I can do that! To avoid confusion, note that the record types i.e. 01 through to 56, will be filtered, then copied into the corresponding worksheet with the same number.
The problem I'm getting is the autofilter I'm applying, then the array I'm trying to reference (to avoid repetitive coding!) doesn't work as the code doesn't compile (I've not worked a lot with Arrays but I'm learning all the time!)
Option Base 1
Sub test()
'The array is declared here:
Dim RecIDNo(56) As String
'The arrays are assigned the string variables below:
RecIDNo(1) = "01"
....
RecIDNo(56) = "56"
'This worksheet contains my data:
Sheets("DATA").Select
For RecIDNo(1 To 56) '<--I THINK THIS IS WHERE I'M GOING WRONG!!
'Filter and select arrays:................
View 2 Replies
View Related
Jun 11, 2014
I am running a auto filter macro to I need to change it to only copy column A:B and past in column C:D in the sheet called "Diary". How to change my current macro to only select column A:B and paste it. It is currently pasting the entire row.
Here is the part of my code:
[Code] .....
View 1 Replies
View Related
Jul 31, 2008
I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!).
I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.
View 9 Replies
View Related
Nov 25, 2009
I have a advanced filter that works pretty much close to how I want it to. However I would like it to either cancel the new filter or copy everything if it finds 0 matches.
If the copied location is blank it breaks my sheet. So I need to find a way for it to never be blank, either by canceling it, copying everything, or finding some other way I haven't thought of.
Also for some reason my Advanced Filter does NOT Ignore blank "OR" cells. If I place a word in the top cell, then leave the bottom blank, it searches for the top cell or anything and I end up with everything. Its quite frustrating.
View 3 Replies
View Related
Oct 1, 2008
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
View 7 Replies
View Related
May 1, 2014
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
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
Mar 26, 2013
I have some columns on which I have a filter, with some columns next to those that have information in them.
What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..
View 1 Replies
View Related
Jul 16, 2012
I have a userform with several textboxes and 2 comboboxes. The data that I need protected from duplication is based in the comboboxes. If I ignore the other fields and presume the following it may be easier to understand:
The comboboxes are populated by the data stored in sheet1 and when the userform is complete, the OK button populates all the data into sheet2
Combobox1 contains names eg, John, Julie, Bob etc
Combobox2 contains colours eg, Red, Blue, Green etc
I want the userform to allow the data to be entered into sheet2 only if it is not an exact duplicate of the choices in BOTH of the comboboxes
For Example
John Red
Julie Red
Bob Blue
John Blue
These are all ok, however, to try and enter John Red again would bring up a message box indicating a duplicate entry and prevent the data from being entered.
View 2 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
Aug 4, 2009
I am trying to build a simple spreadsheet for my salesman that given a customer's zip code, it looks up the corresponding territory code for that zip code. I have conditional formatting setup so that it colors the zip code that matches the one inputted, but how do I then return the value of the corresponding territory code. The spreadsheet being searched is a simple 2 column list, with the headings: Zip Code and Territory. What's the best way to get the territory code back to my worksheet?
View 9 Replies
View Related
Jan 2, 2012
know a macro code to add 3 columns after each different Item.on my spread sheet.
1ABCD
2Item
3111
4111
5111
6111
7111
8111
9112
10112
11112
[code]...
View 5 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
Jan 8, 2007
I have many small spreadsheets of data organised into 4 columns and anything upto 250 rows. I want to be able to test if all the cells along each row have the same data in each of the 4 cells, so 4 occurences of the same thing. Each row of data will be different. (I'm not intertested in matching the data going down the column).
However, I won't know with each spreadsheet what is the exact data I will be looking for - if all 4 cells don't have the same data, that is fine as this will then require my manual attention which is the purpose of my project.
I just want to avoid having to check through lots of rows of data where all the data does already match.
The data will usually be text but it can be IP addresses and numbers too. I need a formula or set of operators to use for is exercise.
View 7 Replies
View Related
