Please Help - Trying to use one table to complete another
 

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

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


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=378528

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.


--
Methodman1
------------------------------------------------------------------------
Methodman1's Profile: http://www.excelforum.com/member.php...o&userid=24246
View this thread: http://www.excelforum.com/showthread...hreadid=378528

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

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.


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=378528