excel - Compare two data sheets -

the issue i'm faced have 2 sheets of data in excel. stocksheet list, listing items have variance stocktake. items randomly placed between both documents, impossible side-by-side view if order columns (which have). example this:

sheet 1:

a1 (apple) (1) a2 (carrot) (-3) a3 (banana) (4) a4 (chocolate (-7)

whereas sheet 2 may be:

a1 (orange) (-2) a2 (apple) (3) a3 (muffin) (-8) a4 (carrot) (3)

so can see, same data may appear, , if want compare 2 sets, know variance, i.e. sheet 1 said -3 whereas sheet 2 said +1... preferably in batch if possible, there on 800 cells go through.

just can see i'm dealing with, here's links pastebins of both sheets;

sheet 1: http://pastebin.com/6i7qkj6n sheet 2: http://pastebin.com/zjtc2u7q

is there can think of able assist me, other me going through 1 one considering doing?

i'm assuming unique identifier stock items column labelled cysku, right?

if that's so, there 192 common items between 2 sheets. ran vlookup in both sheets bit similar 1 pnuts used , used filter.

there more variances between cycost cyretl far can see (i haven't compared other columns).

to perform comparison, can following:

1. insert column between columns c , f (just after cysku) , put vlookup formula in row 2 of column , fill down:

   =vlookup(c2, sheet2!c:c, 1, 0) 

2. insert filter , filter out #n/a column common between 2 sheets.

3. in column m (after cydvar), insert vlookup , fill down:

   =vlookup(c2, sheet2!c:f, 4, 0) 

   this give corresponding cyretl sheet2. can compare 2 cyretl.

how vlookup works:

1. the first parameter vlookup looking for.
2. the second parameter table range in first parameter.
3. the third parameter nth column match returned, limited table (if table in column a:a, 1 column available, if table a:b, 2 columns available, etc).
4. the last parameter either exact or approximate match. exact 0 (or false) , approximate 1 (or true).

you can change table range , column number change value you're looking sheet2.