Create a list of data by comparing two different worksheets

[jamey84]

I have two different worksheets that contain data. I am looking for a macro, or some other way, to check in one column of data and see if it matches data in a column in the second worksheet. If it does, then there is characteristic data that I want to put into a third worksheet, along with what I was seeing if it matches. Below is a simple example of what I am working with.

Sheet1
Johnson
Smith
Richards
Peterson
Asmit
American
Jonson
Pepper
Barry
Owens
Sanders
Mann
Miller
Oswald
Zoo


Sheet2(columns seperated by //)
Johnson// Bob// 123// 456// 1111
Smith// Jane// 456// 456// 2222
Richards// Ben// 4// 156456// 3333
Peterson// Jason// 154// 48// 4444
Asmit// Ahmed// 45645645// 12315// 5555
Pepper// Dr// 444// 44586// 6666
Barry// HI// 25151// 25// 7777
Sanders// Rick// 254// 456// 8888
Mann// Jessica// 25// 64// 9999
Oswald// Ben// 222// 4546654// 1010
Zoo// The// 789// 778995// 1212



I want to create a third worksheet that contains only the data from the second worksheet, and only where column 1 matches. I am using Excel 2000. I look forward to your help...thank you very much in advance.

 

[Cbrine]

Re: Create a list of data by comparing two different workshe

You might want to check out the vlookup function in the online help. It should be able to do what you are looking for in conjuction with the If(ISNA(Vlookup),"",Vlookup)

Post back, after you have reviewed the online help, if you are having trouble figuring it out.

 

[jamey84]

Re: Create a list of data by comparing two different workshe

From my understanding of the Vlookup Function, won't this only return a single value from the cooresponding column? What I am looking for is to return 5 cells of information, each time the name on Sheet 1 equals the name on Sheet 2.

 

[Cbrine]

Re: Create a list of data by comparing two different workshe

Just string the Vlookup's together.

=Vlookup(A1,Data,2,false)&" " &Vlookup(A1,data,3,false)&" "&Vlookup(A1,data,4,false)

If you do 5 vlookups concatanted together with seperator spaces, you should get the return value you are looking for? I think that's what you want? If not, let me know where I'm going wrong.