world33 Posted October 2, 2005 Share Posted October 2, 2005 Hello,I have got three columns A, B, C.In column A I have 7 fax numbers. In column B I have 7 URLs where faxes have been extracted from. In column C I have 14 URLs in which, among others, are included the exact URLs in column B. I want associate faxes in column A with URLs (matched with those in column B) present in column C.Ex. COL A 1 1-785-227-2004 2 208-376-7743 3 (617) 912-9101 4 267-502-2658 5 (317) 940-9930 6 716-888-2525 7 (219) 473-4259 COL B 1 www.bethanylb.edu/ 2 http://www.boisebible.edu/ 3 www.conservatory.edu/ 4 www.brynathyn.edu/ 5 www.butler.edu/ 6 http://www.canisius.edu/ 7 http://www.ccsj.edu/ COL C--------------------------------------- COL D1 www.ab.edu/ 2 www.abc.edu/ 3 www.abconline.edu/ 4 www.ac.edu/ 5 www.aca.edu/ 6 www.academy.edu/ 7 http://www.boisebible.edu/-----------> 208-376-77438 www.boisestate.edu/ 9 www.brevard.edu/ 10 http://www.canisius.edu/-------------> 716-888-252511 www.carleton.edu/ 12 www.carroll.edu/ 13 http://www.ccsj.edu/------------------> (219) 473-425914 www.clemson.edu/ Which function should I use in order to get faxes in Col A and put them next to the appropriate URLs (matched with URLs in column B) in column C ?Thanks a lot !!Fabio Link to comment Share on other sites More sharing options...
Atim Arap Ben Arap Posted October 2, 2005 Share Posted October 2, 2005 @world33I solved your problem with a small excel macro.There is a file at the bottom of this message which included an example like what you wanted.In order to open this, you must enable macros.When you want to modificate it; while working on file, press Alt+F11 (it opens VBA window) and go to Module1.Enjoy...CompareURLs.rar Link to comment Share on other sites More sharing options...
jkfegan Posted November 4, 2005 Share Posted November 4, 2005 The VLOOKUP function is the best answer.***FIRST***You'll have to reorder your columns (Swap the data in columns A and B) but then you can get your desired result without the need for a macro.I've attached an Excel file to demostrate how VLOOKUP works with your data. You can see more details in the help files.Notice, that I had to create a named range for a table array. I set a range called RANGE1 equal to A1:B8 (which is the current list of URLs/Telephone numbers). RANGE1 should change to fit your input data size. For more details on naming a range, go to Insert | Name | Define.=VLOOKUP(C2,RANGE1,2,0)In the example above, VLOOKUP will search the first column in RANGE1 (ie COL A) for the search parameter (the data in C2). When it finds a match it will return the value of the data in COL B that corresponds with the row of the match in COL A. This is a *really* great tool for managing data in large spreadsheets. For instance, if you had two telephone numbers (or addresses, or names, IPs, etc) you could set up a bunch of VLOOKUPs to pull out all of that data automatically. Your functions might look something like:=VLOOKUP(F2,RANGE2,2,0)=VLOOKUP(G2,RANGE2,3,0)=VLOOKUP(H2,RANGE2,4,0)=VLOOKUP(I2,RANGE2,5,0)RANGE2 would have to define a larger range (like A1:E8).I did a poor job of explaining it. The best way to find out more is to play around with it and look it up in the help files.I hope this helps...-JAnswer_using_VLOOKUP.zip Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now