Accurately Match Up Two Columns In Two Sheets To Do VLookup?
Aug 30, 2013
The columns that I want to match up are not exact matches. Multiple values from sheet1 colA can match up to one value in sheet2 colA by using the first 5 characters of the cell value.
Here is the workbook to go with the sample data set I put below.
HowDoIMatch2.xlsm
As of now, the formula is generating a 2 in the banana row. But there are no bananas documented in sheet2.
How can I get my output to Sheet1 ColB to be accurate without having to create new columns/data with truncated values for the lookup?
If I use this formula in Sheet1 ColC (then copy downwards /autofill):
[Code] .....
to grab the Sheet2 colB. It works fine except for in the cases where sheet1 colA values have no match in sheet2 colA (i.e. Sheet1 A4-banana1).
That vlookup formula will output SOMETHING because of the nature of MATCH using either -1 or 1 as a match_type option. I cannot use MATCH if I want to solve this issue...but I do not know what else to do.
Sheet1
a..........................b.........
apple1........=formula
apple2........=formula
apple3........=formula
banana1......=formula
coconut1......=formula
donut1.........=formula
Sheet2
a.....................b.....
apple123...........1
coconut123........2
donut123...........3
I know of a roundabout way to do this but I would like to stay away from "creating new data". (Create/insert a new column in Sheet2. The first row would be =Left($A2,5) then downfilled. This way I can use exact value in the MATCH formula in Sheet1's VLOOKUP.) If VBA is the only way I can "automate" this lookup output to be correct, then that is the road I'll have to take
View 3 Replies
ADVERTISEMENT
Dec 15, 2013
[URL]
What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.
View 9 Replies
View Related
May 19, 2009
I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.
For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:
View 2 Replies
View Related
Sep 23, 2008
I have two columns with names on it. and Column A has about 600 names and column B has about 447 names. I want to match these two columns and get the names which do not match on Column C.
View 11 Replies
View Related
May 22, 2014
I have two data sets across two worksheets. The first worksheet contains sales order numbers (Sheet1:column A) and other data . The second worksheet contains sales order numbers (Sheet2:column A) and the product details.
On sheet1, I had to manually duplicate a sales order number (inserting another row) if the number of units of the order is greater than 1. I then need to fetch the product details from sheet2 for each unit for that specific order number. However in sheet2, there are multiple products for one sales order number.
How do i create a lookup/match to fetch the product details for each sales order number without duplicating the product details if there are multiple products for one sales order number?
I know a simple vlookup function will return the values that it matches first and that is not what i want.
Example: Sheet1
Column A (Sales Order#) | Column B (Quantity)
0417436GPCP | 1
0417436GPCP | 1
0413412FACY | 1
0413412FACY | 1
[code].....
View 6 Replies
View Related
Apr 10, 2014
Not sure where to start with this one, an IF statement or vlookup? Basically I have 3 columns A, B, C (original sheet) and A, B, C in my working sheet. A contains car registrations, B contains a date and C contains a weight. I need to return C from my original sheet into my working sheet only when A and B BOTH match from my working sheet (there are scenarios where the same car registration has a different date next to it). So I want to return the weight only when the car registration and the date match in a specific instance from my working sheet.
View 6 Replies
View Related
Sep 24, 2009
I just need an macro which can compare two sheets and give an output as matched or not matched.
I Have data in Sheet1 and Sheet2. In Sheet1 i have 4 columns of data and in Sheet2 aslo the same set of data.
If any of the four Columns of sheet1 or sheet2 is not matching then should pop-up not matched in the last column.
I Hope someone has the best solution for my query .For more clarification iam attaching an sample sheet.
View 4 Replies
View Related
Apr 10, 2007
I have a list of names on 2 separate sheets that I need to modify. If I have a name on sheet1 matches a name on sheet2 I would Like to add a "space" then "(SV1) at the end of the text string on sheet2. I have a list of 1500 and will only have to modify 75-100. Example shows on sheet2 how I would like the desired outcome to be.
View 3 Replies
View Related
Mar 13, 2014
I've got a problem where I am trying to return a value if two criteria from one table match two from another.
I have included a example of my issue.
=INDEX(Time!D:D,MATCH(Data!A2,Time!A:A,0)*AND(MATCH(Data!C2,Time!C:C,0)))
I have had a look around lots of forums but cant get my head around what is wrong with my formula
View 4 Replies
View Related
Mar 20, 2007
I have a column on sheet 1 which contains 200 lines with a unique number in each row (account codes). I have another sheet (sheet 2) which contains the same column. sometimes an extra row for an extra account code is added to the col in sheet 1. I need to make sure that the col I have in sheet 2 always has exactly the same as the col in sheet one, where the same account code should never be repeated. I am currently trying to setup a sheet where I keep all the checks and controls.
View 7 Replies
View Related
Jun 25, 2014
Is it possible to use multiple Vlookups in one macro?
Currently running a vlookup of computer names between multiple sheets, i need to be able to match product name and computer name between sheets.
For example (Sheet 2 column B) compare it to (sheet1 column B and C)
if it gets a match then vlookup to (sheet 2 column A) to (sheet 1 column A)
Spreadsheet attached : excelhelp.xls‎
View 2 Replies
View Related
Aug 10, 2009
This is driving me nuts. What I have is:
Sheet1:
ColumnB - number stored as text (Work ID)
Remaining Columns: - mixed data, some columsn numbers, some text
Sheet 2
ColumnA - number stored as text (Work ID)
ColumnB:ColumnAW - mixed data, some columsn numbers, some text
CollumnAX - Dollar Values
What I want is in Column F (Sheet1) a formula to:
if workID in Sheet1!ColumnB is present in Sheet2!ColumnA AND Sheet2!ColumnAX>1 then return True
View 9 Replies
View Related
Jan 20, 2009
I would like to match column data in a source spreadsheet to column data in a target sheet. If a match is found, I would like to copy the corresponding row range from the source sheet to a separate, third sheet. For values where no match in found in the a target sheet, I would color the unmatched cell in the target sheet red. If a match was found, the cells would be colored green. The data in the Source sheet is in column A, while the Data in the Target sheet is in Column T. The data will be pased in the third sheet in Column T preserving original formats
I have this code, gleaned from several postings on this forum that somewhat works. The problem is that I get false mismatches (i.e. some cells get colored red even when there is a match and the data got copied to the third sheet) even though there are no duplicates. I have made sure that the formats are identical in both Target and Source sheets to try to fix this. Also, I don't want to cut the entire row , but just copy and paste a row range onto a third sheet. The column and row ranges are variable. I am attaching a file.!!
Sub CutRows()
Dim i As Long, k As Long, n As Variant, r As Range
Application. ScreenUpdating = False
With Sheets("Source")
Set r = Range(.Cells(1, 9), .Cells(65536, 6).End(xlUp))
End With
k = 0
i = 6
While Not IsEmpty(Sheets("Target").Cells(i, 20))
n = Application.Match(Sheets("Target").Cells(i, 20).Value, r, 0)
If IsNumeric(n) Then
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 35
k = k + 1
Sheets("Source").Rows(n).Cut Sheets("Sheet3").Rows(k)
Else
Sheets("Target").Cells(i, 20).Interior.ColorIndex = 3
End If
i = i + 1
Wend
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
View 7 Replies
View Related
Mar 11, 2014
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
View 2 Replies
View Related
Oct 8, 2009
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies
View Related
Feb 4, 2014
I have two sheets of data , sheet A and Sheet B.
Sheet B contains a column called "Name" and for each name, and for each name there are corresponding numbers. In sheet A, I have a "list of interest" in column A. What i want to do look through the data in sheetB to find a match from the list of interest and return the corresponding letters, located in column A.
I have filled out the first two rows of results that should be returned as an example.
One idea i had was to put a vlookup formula in each column result 1 to result 6 so i can catch all 6 "Serves" columns from column B, but there may be duplicates in the serve columns and vlookup only reports the first match.
View 3 Replies
View Related
Mar 3, 2009
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
View 9 Replies
View Related
Jul 13, 2009
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies
View Related
Jun 9, 2009
I’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
View 14 Replies
View Related
Feb 20, 2009
I have four columns of data, as follows:
label 1, value 1, label 2, value 2
I need to create a formula in the fith column that for each line will tell excel to:
look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.
I have tried doing this with SUMIF but am getting nowhere fast....
View 7 Replies
View Related
Oct 8, 2009
I have this table
.......A.....B.....C....D
1.....I......a.....d.....g
2.....II.....b.....e.....h
3.....III....c.....f......i
As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i
I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on
I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.
View 9 Replies
View Related
Jan 28, 2008
HOw can I match combination columns of A-F (row1) to combination H-M (rows1 to end) and show how
many are number matched in column N..
ex..
Rows
A
B
C
D
E
F
G
H
I
J
K
L
M
N
1
2
3
16
17
11
9
11
17
18
View 9 Replies
View Related
May 25, 2014
i wanna copy C and D columns in Sheet1 and insert these 2 columns (copy + insert copy cells function, not copy + paste) to all other sheets except Infopage sheet
View 14 Replies
View Related
Jun 7, 2006
I have 2 worksheets in which I have to copy one column of cells from one to
the other. The problem is that the "main" worksheet lists nearly 3,500
clients and I have to copy numbers into the "main" worksheet on weekly basis
from another worksheet that lists only about 1,100 of those 3,500 clients.
Both client lists are in alphabetical order, but I obviously cannot just copy
one column into the other, because then the numbers won't be matching the
correct clients.
View 10 Replies
View Related
Jul 2, 2008
I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.
View 9 Replies
View Related
Sep 11, 2006
Now i have one excel sheet with two separet sheets in it ( Sheet 1 ) and ( Sheet 2 ) . I have in sheet 1 a column A with material codes and ColB is discreption and Col C IS Prices . But in Sheet 2 Col D is materila Code and Col I is a price .
the recordes in sheet 1 are around 11000 but in sheet 2 are around 2200 where the sheet 2 has a specific materials from sheet 1 .
Now i want to update the prices in sheet 2 from sheet 1 for each item after confirm that the materila code in sheet 2 equal in sheet 1 so copy the price from sheet 1 to sheet 2
Sheet 2 is old prices and sheet 1 has new prices so i need update the new prices in sheet 2
View 9 Replies
View Related
Apr 21, 2007
I have 2 workbooks (workbook1 & workbook2). Information in column A and column N must match both workbooks per row/ cell (ie. if A2 and N2 in workbook1 match workbook2, copy information from 3 other columns (S, T, U from from workbook1 into workbook2 - cells S2, T2, U2). Workbook2 may have information in column S, T, U, but I need to over-write it with the information from workbook1. If the information does not match per that row/cell (ie. A2 in both worksheets are the same, but N2, they are not, so go to the next line that does match and copy the information into worksheet2). I do not want to use a V- lookup for this problem. I really need a marco.
View 3 Replies
View Related
Nov 20, 2007
I am either trying to do 2 vlookups or 1 match and a vlookup, but I am not sure. I have two workbooks. The first workbook I need to populate the 'Actual Taken' column from my second workbook. Below is the first workbook:
******** ******************** ************************************************************************>Microsoft Excel - FY07Q3_LMS_Payroll_.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD9=
ABCD1*Training*HoursStore*Manager*2Pay*Per*Hour*$28.37Actual*Taken3LMS*Courses4Baseball*/*Softball*Basics***5Baseball*/*Softball*Equipment***6Bike*Basics***7Bike*Fitting*and*Essential*Items***8Callaway*Golf***9Columbia*Footwear*Technologies*2006/2007***RCoursebyPosition_MGR*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The second workbook contains all of the data. Please see below:
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCD462Softlines*MgrTeam*Sports*-*Hockey**Count3*463Softlines*MgrTeam*Sports*-*Lacrosse*Count2*464Softlines*MgrTeam*Sports*-*Soccer**Count4*465Softlines*MgrTennis*Audio*CD*Test*Count4*466Softlines*MgrTennis*Basics**Count7*467Softlines*MgrTennis*Equipment*and*Technologies**Count7*468Softlines*MgrUnder*
What I would like to do, is first have the formula locate the Store Manager from the First workbook and find it in the Second Workbook, then once it finds Store Manager, look at the Course Name from the Frist Workbook and find it in the second workbook, finally populate column C from Workbook 2 to the Actual Taken in Workbook 1.
View 9 Replies
View Related
Mar 20, 2007
I have an account issue. In the attached file sheet Balance, there is a trial balance sheet with account ID, Name, Credit and Debt which I download from system every month. In sheet Check, I have some related account IDs. If the ID is found from Balance sheet and is Credit, the amount in Check sheet will be positive. Otherwise it will be negative.I have ever tried to use Match, Vlookup function to do it but did not get it.
View 3 Replies
View Related
Jul 24, 2014
Why I am getting N/A errors in sheet 1 of workbook?
For example, in sheet1, Cell B2 should equal 3. And it should stretch across the entire data range, so even something like B14 should return 3.
View 8 Replies
View Related
