Create A Master List From Several Lists

[Timbo]

I have workbooks one per year with codes in Column N and Categories in
O.

The codes and data vary from year to year so a code i.e. AKC for the
Category Graphic Design may appear in 2005 but not in 2006.

I have created a master list of Codes which I have in a seperate
workbook in Column L.

I tried using Vlookup on 2005 alone but then I get #N/A where the code
isn't in the year, so I tried combing ISNA with Vlookup which works for
one year but then I have 5 columns of categories one for each year
2005-2009, and I have to cut and paste as vales to create one single
column of catagories that match the adjacent codes.

The categories won't change new codes and categories will be added and
I have to make provision for 400 codes.

Is there an easier way to do this?

 

[Simon Lloyd]

I have workbooks one per year with codes in Column N and Categories in
O.

The codes and data vary from year to year so a code i.e. AKC for the
Category Graphic Design may appear in 2005 but not in 2006.

I have created a master list of Codes which I have in a seperate
workbook in Column L.

I tried using Vlookup on 2005 alone but then I get #N/A where the code
isn't in the year, so I tried combing ISNA with Vlookup which works for
one year but then I have 5 columns of categories one for each year
2005-2009, and I have to cut and paste as vales to create one single
column of catagories that match the adjacent codes.

The categories won't change new codes and categories will be added and
I have to make provision for 400 codes.

Is there an easier way to do this?Timbo, your explanation is a little confusing, can you supply sample

workbook(s)?

Attatchments.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attatchments button,
this is where you get to add files for upload, if you have any trouble
please use this link or the one at the bottom of the
any page.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Timbo]

Hi Simon,

I have stripped out most of the data from these large workbooks and
just left the codes to enable me to upload them.

ExampleA is a file for 2005 with codes and categories in Columns N & O.
There is a file like this for every year.

Example B is a file of all codes for 2005 - 2009 I want to somehow
lookup the category based on the code but the category could be in one
of 5 workbooks called 2005, 2006, 2007, 2008 and 2009.

Timbo


+-------------------------------------------------------------------+
|Filename: EXAMPLEB.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=133|
+-------------------------------------------------------------------+

 

[mubashir aziz]

See attached ..


+-------------------------------------------------------------------+
|Filename: create-master-list-several-lists-examplea.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=134|
+-------------------------------------------------------------------+

 

[Timbo]

Hi Mubashir,

Thanks for the help I need to take it a step further, perhaps you can
help.

If there is no for a code category existing in the 2005 file I need to
reference the other files to get a match.

For example WH, AKLP, AVGM (all the blank cells) do not exist in the
2005 file but they do exist in one of the other years.

A bit like having 5 options one for each year if the sum returns ""
rather than a category.

 

[mubashir aziz]

Hi,

I've created 10 Ranges Names from Insert Name define and then use in
conditions and now its working fine. You can make changes after
carefully understanding the concept.

Now what ever you'll copy in Sheet 2005 - 2009 will be updated in your
sorted sheet but make sure you have all codes in your sorted sheet and
there is no duplication ....


+-------------------------------------------------------------------+
|Filename: Solutiont-several-2009.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=137|
+-------------------------------------------------------------------+