Excel Help needed

allllec · 22 Oct 2007 at 18:49

right firstly I'm not totally sure this is the right area but hell seems close enough

secondly I'm useless when it comes to excel and such so bear with me

right basically i have 2 sheets

both have just over 5 thousand items.

column a contains a code
column b contains manufacturer

basically one is the old system/website

and the second is the new system/website

so apparently 2 things have happened. since the old system new items have been added to the new list and also not everything imported to the new system correctly so this is going to have to work both ways

i need a way of creating TWO new lists in a new sheet. One will contain items which are on sheet 1 and not sheet 2

and vice versa. sounds simple enough but i have no idea how to go about this.

how would i do this ?

cheers

alec

adelburn · 22 Oct 2007 at 21:33

First off I have limited knowledge of excel, but i'll have a crack all the same

What I would do would to put a vlookup formula in there,

=vlookup(old!a2,new!$1:$5000,2,false)

old!a2 this being the first code to lookup in the "old" system

new!$1:$5000 this being where to retrieve the data from, i.e. new system between codes 1 and 5000.

2 this is what column to return with, so in this case manufacturer, it has to be a numerical value so column a=1, b=2, c=3 e.t.c

false false finds an exact match to old!a2, rather than true which is the closest match

Now this would search for old code 1 in new sheet and return with the manufacturer name if found and if not return with N/A, you would then filter the N/A's to get the code that are on 1 sheet and not the other.

Sorry if this doesnt help much but I tried

allllec · 23 Oct 2007 at 11:49

Took the adice i've a very smart chap and just plopped the whole lot in access 2007 and used queries. all sorted now

thanks for trying thou!

ta

alec

DAS · 23 Oct 2007 at 16:08

Access is exactly what I would have suggested so the chap obviously is very smart.

allllec · 23 Oct 2007 at 18:22

Hehe =p

i cant believe i didnt think to do it that way in the first place! *pulls face*

Its well efficient, working with several tables all containing upto 13k items without a single bit of lag or issue

right, the big one tomorrow - gotta start putting stuff thats missing on the company website! hopefully goes nice and painlessly like today.

thanks again for your help people