Comparing two lists for matches - with a twist!

Hi,
I am trying to do the following:

There are two sets of supplier lists - A List and B List. I need to
compare the two lists. Source A is my master. Source B is a subset of
that but has contact information for all the suppliers. Hence, my goal
is to retrieve the contact information from Source B and match it to
the suppliers of Source A.

The above can be easliy achieved using a vlookup funtion. But the
problem is that some of the supplier names in the two lists are not
exact matches. Hence, vlookup does not catch it. For instance, a
supplier is listed as ABC Inc. in A list and as ABC, inc. in the
other.

Please advise what is a good way to find the matches, when they are
not exact. Putting "TRUE" as the last parameter in the vlookup
function doesn't work as it is too broad.

Thanks,
Vidita.

0
vidita (1)
7/26/2007 12:25:26 AM
excel 39879 articles. 2 followers. Follow

1 Replies
737 Views

Similar Articles

[PageSpeed] 55

I do not know any standard Excel function that could do that. May be you
could create a new column into the supplier master that contains the first n
characters of the supplier (=LEFT(A2, n)), and do a VLOOKUP using this column.


0
squenson
7/26/2007 10:53:02 AM
Reply:

Similar Artilces:

Two Email Accounts not able to reply out of one of them
Hello All, I was wondering if someone here has ran into this problem. I currently have two email accounts setup in Exchange I can receive email in both of these accounts and view the email in Outlook 2003. In my default account if I receive an email I can reply back. But in my secondary mailbox if I receive an email I cannot reply back I get a error message which is listed below. On the secondary account I have added my username under permissions and gave it full access but I am still getting the below error. Anything will help thanks. Your message did not reach some or all of the inten...

MRU File List
Can someone point me to a MRU File List tutorial? -Tom R. "Thomas Rybka" wrote: > Can someone point me to a MRU File List tutorial? > > -Tom R. > I am certain there are better solutions; but the following code worked for me.... // When we create a new document the first time, we will // fill that documents first view with data read from the // first file in the users Most Recently Used (MRU) list of files. (or "Recent File List") // String defined per documentation (Word "File", ending with formating "%d") static cons...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

Importing a contact list from Excel
I have been trying to import a contact list from Excel into Outlook. It keep telling me that I need to rename my ranges in the Excel document but I cannot find anything that tells me how to accomplish this. ...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

Two databases or one
Hi, What if you have two organizations and you plan to design a database to do the same task but within that task, the defined tables will vary because of unique qualities related to the individual organization. Would it be best to create separate databases for each organization or keep the two organizations together? Anyone know the guidelines related to this? Thanks for any feedback. Ask yourself this - How often will you need the combined data? -- Build a little, test a little. "AccessKay" wrote: > Hi, > > What if you have two o...

Custom Smart Lists missing after SP7
It appears that we no longer have any of our custom smart list reports after the upgrade. Is there any way to restore or recover this. Which dictionary do custom smart lists reports get stored? We may have a backup of that dictionary. ------=_NextPart_0001_3EBC268F Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello and thank you for using Newsgroups! Can you clarify the following: Are you referring to Smartlist favorites? These you create within Smartlist and it allows you to specify additional search criteria for smartlist objects. If this is the case, these are stor...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Calendar and task list in same view with Outlook 2003
I used to have a view in Outlook 2000 that had both my calendar and my task list. Is this possible in Outlook 2003? How would you do it? In Outlook 2003 to show the Task List with your calendar you need to go to the Calendar in Outlook and select View -> Task Pad. I hope this information is helpful. Robert Findlay Partner Technical Lead -- BizApps Microsoft Technical Support for Platforms and Business Applications ...

differences between two tables
I'm trying to find the differences between two tables, A and B. There are some records in A that are not in B, there are some in B that are not in A, and there are some in both but with differences in the fields. I'm not getting everything. Does anyone have any ideas how I can tackle this? Thanks! Use a UNION ALL query. -- KARL DEWEY Build a little - Test a little "denise" wrote: > I'm trying to find the differences between two tables, A and B. There are > some records in A that are not in B, there are some in B that are not in A, > and there are s...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

Match, Index, Indirect, Offset
I often see these functions used together and used quite a lot but what practical application do they have? From reading the help files it is easy to understand the theory of what's going on but applying it in practice is something else. Sumproduct was a classic case in point here. Going by the help file, we are told that sumproduct multiplies corresponding values in arrays and then sums the result (yeah but so what). But who would have guessed from this that it's application is quite extensive as a filtering tool of a sort. ----== Posted via Newsfeeds.Com - Unlimited-Unrest...

Why does Outlook open two windows?
When installed Outlook 2003 with a new profile about two hours ago, it would open one window each time I started Outlook. But now, after I've been doing a lot of work on the folder structure, particularly transferring dozens of folders by drag and drop within a single non-default PST file. I notice that the computer has been very noisy for a while, apparently furiously coping with the changes I've been making. Now when I start Outlook, it opens two windows; presumably that's an outcome of something I inadvertently did when transferring all the folders. I'd rather it ...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Outlook Folder list #3
I have a customer who's folder list in Outlook 2002 is no longer arranged alphabetically. Does anyone know how to change it back? ...

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

How to obtain Averages from a list of multiple items?
Example:Excel worksheet-Column A has list of Cat or Dog (say 15 items)-Column C has their age in days old (10,3,4,etc) I need (2) Averages- Average age of Cats and Average Age of Dogs Hi! =SUMIF(A1:A50,"cat",C1:C50)/COUNTIF(A1:A50,"cat") Do the same for dog. Biff "MadameJunk" <MadameJunk@discussions.microsoft.com> wrote in message news:B0E6C651-E09D-4FA4-A0BB-F859A88CA81A@microsoft.com... > Example:Excel worksheet-Column A has list of Cat or Dog (say 15 > items)-Column > C has their age in days old (10,3,4,etc) > I need (2) Averages- Aver...

Outlook should let me remove the favorites listing
I find the 'Favorite Folders' window to be a nuisance that has the added advantage of wasting desktop real estate. I'm a new user to Office 2003, having used previous versions for years. Imagine my annoyance when I look in Help, and am told point blank that I can neither move nor remove this useless panel. It's as annoying as the "Personal Menues" feature, but that, at least, can be removed. If you wish to have your issue considered for future releases of Outlook you should send an e-mail with your issue to the MSWISH@Microsoft.com alias and the developers ...

Server side rules for Exchange 2007 for auto detaching attachments to a specified location when sender/recipient names match pattern?
Greetings, Some suppliers send us updates to product pricing as email attachments. We would like to process these updates automatically into our SQL product database using SSIS. Can anybody recommend a server side method of automatically detaching these attachments from Exchange 2007 to a specified location when the sender name and recipient address match specified patterns? I believe MAPiLab rules for exchange does it for Exchange 2003 but I need to find a way with Exchange 2007, Cheers Noel ...

Smart List and Receivables Payments
HI, When I use the default Receivables Transactions Smart List, the records for payments and returns have $0 values despite the fact that a payment was received. The payment is accurately represented elsewhere in the system (e.g. transactions by Customer), but for whatever reason the payment and return values do not show up anywhere in smart list. Why is this? And how can we fix? Thanks, Jason The problem is the amount column that is displayed by default. You need to select the Document Amount column. "Jason" wrote: > HI, > > When I use the default Receivables Tr...

List Comparison
I would be most grateful if someone could help. I have two lists residing in Column A and Column B respectively. Both lists contain 4 figure numbers. Column A contains the 'complete list' and Column B only contains 'some' of the entries from Column A. I would like Column A compared to Column B and any numbers that are not in Column B but are in Column A put in Column C. Many thanks in advance. hi, in column C enter and then copy formula down =IF(ISERROR(MATCH($A$1:$A$6,$B$1:$B$6,0)),$A$1:$A$6,"") Change range to fit your needs "SiH23&qu...