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]

Please, I will wroship everyone who can help! Thank you.

 

[Rick Rothstein]

It is unclear why 10111300 is first on your list in Column C when the related items are so far down in Column A. I think you will need to provide us with more details about how the list in Column C is to be generated. Besides telling this additional detail in words, perhaps posting your non-working formula might help us to see exactly what you are trying to do.

 

[pplstuff]

All you're trying to do is match the CLASS to the first 6 digits of the COMMODITY? You can use vlookup() (but the 36k records may take a few minutes to calculate).

Insert a column before ColA
a2: =left(b2,6)
f2: = vlookup(left(d2,6),$a$3:$e$50,5,0)

Make sure you have your file saved, doing this 36k may freeze a slow computer. Press Esc to stop calculation if you feel it's taking too long.

 

[mledu]

Thank you Rick-
Actually, there is a hierarchy between the Class codes and the Commodity codes than can be explained this way :

A Class code is 12345600 and can include commodity codes comprised between 12345601 and 12345699, 12345600 being not includedsince it is the name of the Class or Family.

The list of Commodity Codes is the full list I was given whereas the Class code list corresponds to the only Class codes that I need to use. I need to extract all the related commodity codes belonging to each Class code of my list.

One formula i figured out which does not render what i am asking for :

=SUM((LEN($A$3:$A$36796)-LEN(SUBSTITUTE($A$3:$A$36796,($D$3/100),""))))/LEN(($D$3/100))

This formula attributes the number 1 to each Commodity Code that matches the reference. Again this is not what I am looking for, I thought this might help in my process but it does not.

Thank you.

 

[Rick Rothstein]

Thank you Rick-
Actually, there is a hierarchy between the Class codes and the Commodity codes than can be explained this way :

A Class code is 12345600 and can include commodity codes comprised between 12345601 and 12345699, 12345600 being not includedsince it is the name of the Class or Family.

The list of Commodity Codes is the full list I was given whereas the Class code list corresponds to the only Class codes that I need to use. I need to extract all the related commodity codes belonging to each Class code of my list.

I understood the hierarchy, but my first sentence covered the main thing that puzzled me...

"It is unclear why 10111300 is first on your list in Column C when the related items are so far down in Column A."

Why is the first item in Column C 10111300 instead of 10101500 which would cover the first 16 items in Column A? What dictates the starting Class Code and order for Column C?

 

[mledu]

h

 

[mledu]

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.

 

[Norie]

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.

 

[mledu]

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.

I don't need all of them so my boss and I selected the ones we are going to use.