Compare Excel Speadsheets

[scouser]

I am hoping this is a commonexcel question and some clever person out there can advise a solution!

I have been asked to compare to excel spreadsheets (containing stock information).

Sheet one might have say 8000 records
Sheet two might have say 1000 records

I have been asked to return records that are the same in both spreadsheets?

Sounds kinda complex to me (non programmer).

Many Thanks,
Phil.

 

[twoplustwo]

vlookup my man

 

[scouser]

vlookup

vlookup? Any example or shall I search that big world wide thingy?
Thanks,
Phil.

 

[twoplustwo]

In a cell adjacent to list A enter =VLOOKUP(Cell reference in list A, Cell RANGE that you want to compare against, COndition if TRUE, Condition if FALSE)

Then drag-fill this down the entire array of list A.

After that, filter on results for TRUE and hey presto - a list of things in both.

 

[scouser]

vlookup

Hi, thanks for the quick response.

If speadsheet A & B has 10 columns and column A holds the Part Number (in no particular order) will vlookup look at every row then return the values where the part numbers are the same?

i.e. S
Spreadsheet A Column A row 1 Part ID was 1234
Spreadsheet B Column A row 5000 Part ID was 1234

Would vlookup return 1234?

Finally from what spreadsheet would I insert the vlookup? A blank one looking at both A & B?
Thanks,
Phil.

 

[twoplustwo]

Zip and attach it mate

 

[scouser]

vlookup

I really appreciate that. I am waiting to receive the files this end. When I have them I will zip and post with a clearer explanation of what is required from me (or you!! )
Many Thanks,
Phil.

 

[Brianwarnock]

Can you have multiple matches and if so in both directions or just say the smaller occurring more than once in the larger?

Brian

 

[scouser]

Vlookup

Brian long time no speak, disappointing season.

I have now imported excel sheets into access then did an inner join on part number.

I do have another question however.

Excel sheet has a column 'Priority'. I would like some form of combo box that enables the user to select a value from another worksheet (i.e. lookup). Lookup holds values 'Emergency / High Priority / Medium Priority / Low Priority). If the select 'High Priority' this value is returned to that cell.

Any thoughts to an approach to acheiving this?
Thanks,
Phil.

 

[Brianwarnock]

Type combo box into Excel help and then select the entry Enter data in a cell from a list you specify
that should guide you to do what you want.

Yeah a poor season so far, Rafa needs to stop rotating for its own sake. We definitely need to finish above those toffee noses I know too many fans, in laws etc, life will be hell.

Brian

 

[scouser]

vlookup

Thanks Brian. Fourth is in the bag, very disappointing however......
Phil.

 

[shades]

Another option might be to use dependent lists with an INDIRECT function in Data > Validation.
________
Toyota tf109