Problem finding the right formulae - URGENT!

[mledu]

Hi everyone!
I have a huge problem that is really making me crazy.

What I want excel to do is basically look up and create a list for all the values that start with the first 6 numbers of the class code that has to serve as a reference. I highlighted an example of what i would like excel to do for the first line. There are more than 36 thousand lines to go through for each 664 Class code. Many Commodity codes as you may guess will not be picked up by the formulae since they don't match with any of the class codes i selected for my work.

Here is a sample of the file I want to work with:

[/URL][/IMG]

Help me I am desperate, if someone could give me the exact formula instead of just a hint I would worship them forever!

max.

 

[mledu]

max

I don't know if this can be done with a formula, not much of a formula person though.

You could use filters via VBA code.

I'd post an example but you've posted an image rather than actual data so there's nothing to work with.

Rick

I don't think the order of the data really matters.

I am going to post the spread sheet if you don't mind workingwith it, how can i do that on the forump please?

 

[Rick Rothstein]

The starting class code has been dictated manually by me out of the complete list of Class code. There used to be 3058 Class codes at first only 664 remaining. I can show you a print screen of the orignial file if you want.

Sorry, I am still confused. What exactly is the job you are doing... Producing the Class Codes in Column C? Producing the descriptions in Column D? Highlighting the items in Column A that match the selected item in Column C? Maybe if you showed us a before and after layout, things would clear up for me.

 

[Norie]

You can't add attachments here.

What a lot of people do is upload there file(s) to a filesharing site, eg box.net, and then post a download link here.

 

[mledu]

Here is a cleaner version of my spreadsheet with the '6 first numbers' columns : Thank you again for your help.

[/URL][/IMG]

 

[mledu]

Sorry, I am still confused. What exactly is the job you are doing... Producing the Class Codes in Column C? Producing the descriptions in Column D? Highlighting the items in Column A that match the selected item in Column C? Maybe if you showed us a before and after layout, things would clear up for me.

Again Rick I'm sorry for being unclear. What's highlighted is just an example of what I would like to match in a list on the side or on another sheet automatically.

The codes and the descriptions already exist, i didn't invent them. They are part of the UNSPSC (United Nations Standard Products and Services Code).

 

[mledu]

Norie and Rick, here is the link to this file: https://www.box.com/s/a808c6d86db14c3d3066

It doesn't contain any sensitive information since anyone can download it from the United nations website.

Thank you

 

[Norie]

With the sheet setup as it is with the 2 columns with the first 6 characters you can do this with advanced filter.

It will need some minor changes though.

1 Delete row 2.

2 Change A1 and D1 to a suitable field name, I used FirstSix.

3 With the cursor in column A goto Data>AdvancedFilter...

4 Select Copy to another location

5 Use $A$1:$C$36795 for ListRange, $D$1:$D$665 for Criteria range and I1 for the Copy to.

6 Press OK.

It does take some time, probably due to the no of records.

I end up with 6063 records, no idea if that's right.

 

[mledu]

One thousand Thank You(s) Nori! it works great. I learnt a great tip too!!!!

 

[mledu]

Now on this file: https://www.box.com/s/3bb9100bc69fcec64972, for each Commodity Code we selected previously, I would like to associate/have the description of the Class in front of them on the "UNSPSC Commodity Codes used by MOA" worksheet. How would I do that?

Thank you.

 

[mledu]

The description can be found on the "Sorting Worksheet" highlighted in yellow. Thank you.

Here is what I would like excel to do ( I did this one manually to show you):

[/URL][/IMG]

Or this if what's shown above is not possible:

[/URL][/IMG]