Please Help - Trying to use one table to complete another

[Methodman1]

Hi all,

I have been given a task to find out information for a list of part numbers. The information can be found in another Excel table that I have, which is quite large (~25,000 rows). Since I'm working with a large number of part numbers, I'm trying to extract the information from the larger table so that I can use it to complete the new "smaller" table (~900 rows).

I've tried to add the part numbers from the small table into the existing larger table, and then resort, however I'm running into problems. For some reason the part numbers are not sorting correctly. It seems that there are almost 2 sorts happening. I've checked the cell formats and everything is the same, so I'm confused as to why I can't get the column to sort correctly. If I get it to sort the right way, I'll use an "IF" statement to find the matches in the part numbers then I will just filter out the "YES" matches.

Any help that could be provided would be greatly appreciated. If my explanation of the problem can't be understood, please let me know (I would be surprised if it is hard to follow).

Thanks in advance!

[greg7468]

Peolple often find it hard to visualise the sort of data you are working with.

It may be worthwhile posting a sample of your data before and after sorting to show the type of data, what the data looks like and what you would like it to look like.

HTH

[Methodman1]

greg7468,

Thanks for the reply. Sorry about that, please see the attached example of data:

The smaller data has the following:

Part Number
12345
67891
23456

The larger table has the following:

Part Number Shelf Life Storage
12345 12 Room Temp
67891 24 Refrigerate
23456 12 Room Temp

Since the data is already in the larger table, I'm trying to find a quick way to get the Shelf Life and Storage information for the part numbers in the smaller table.

Can you think of a quick way of getting at this information?

Thank you.

[greg7468]

Hi,
you can use vlookup to help you do that.
Assuming that sheet 1 looks like this

col A
1234
5678

and sheet 2 looks like this

col A-------col B------col C
1234--------12------room
5678--------24------fridge

in B1 on sheet 1 put this as the formula

=VLOOKUP(A1,Sheet2!A1:C2,2)

this will look for the value in A1 on sheet 1
it will look for that value in the range given (A1:C2) on sheet 2 and return the value in the second column. (e.g 12)


then in C1 put

=VLOOKUP(A1,Sheet2!A1:C2,3)

this will look for the value in A1 on sheet 1
it will look for that value in the range given (A1:C2) on sheet 2 and return the value in the third column. (e.g room)

You can then copy this down for all the cell on sheet 1.

HTH

Greg.

[bj]

One thing that often happens with numeric Part numbers is that some are text
and the others are numeric. What you tell it to be has little to do with
what it actually is.
The other things I would watch out for is if there is a formula which
generates the part number. A sort on an active equation can change the part
number so that it is different after the sort than befor.
In your lookup table did you use false as the last entry in the lookup
formula?
if you use false you will not have to sort to be able use the lookup formula.
Note the text,number issue can still raise its ugly head. Lookup tabes do
not recognise a text 12 to be the same as a numeric 12.

"Methodman1" wrote:

>
> Hi all,
>
> I have been given a task to find out information for a list of part
> numbers. The information can be found in another Excel table that I
> have, which is quite large (~25,000 rows). Since I'm working with a
> large number of part numbers, I'm trying to extract the information
> from the larger table so that I can use it to complete the new
> "smaller" table (~900 rows).
>
> I've tried to add the part numbers from the small table into the
> existing larger table, and then resort, however I'm running into
> problems. For some reason the part numbers are not sorting correctly.
> It seems that there are almost 2 sorts happening. I've checked the cell
> formats and everything is the same, so I'm confused as to why I can't
> get the column to sort correctly. If I get it to sort the right way,
> I'll use an "IF" statement to find the matches in the part numbers then
> I will just filter out the "YES" matches.
>
> Any help that could be provided would be greatly appreciated. If my
> explanation of the problem can't be understood, please let me know (I
> would be surprised if it is hard to follow).
>
> Thanks in advance!
>
>
> --
> Methodman1
> ------------------------------------------------------------------------
> Methodman1's Profile: http://www.excelforum.com/member.php...o&userid=24246
> View this thread: http://www.excelforum.com/showthread...hreadid=378528
>
>