I have two excel spreadsheets that I need to combine based on a specific column value. I am trying to add price totals for the inventory information that is on one sheet while the pricing is listed on another sheet. I need the pricing information to be listed in the appropriate row based on codes matching.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
combining two excel spreadsheets based on Codes Matching
- Thread starter SHAWTY721
- Start date
SkipVought
Programmer
Hi,
This seems like it's taylor made for MS Query. faq68-5829.
Skip,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...for a NUANCE!
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]MSQuery might be overkill for this'un Skip - would go with VLOOKUP unless any more requirements fall out...
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
- Thread starter
- #4
Okay I have created a VLookup but it is returning a value of '#N/A' even for the ones that should list a value. Here is what my VLookup looks like:
"=VLOOKUP(B2,PRICING,9,FALSE)",
Where Pricing is a cell reference to the sheet that I am trying to use the VLookup to find the values that correspond with the appropriate codes.
Inventory Catalog Sheet
ITEM_NO NDC UPC
1804731 55513003201 35551303201
1126283 55513004801 35551304801
1801976 55513009701 35551309701
1440049 14290055098 31429055098
1601863 00085141701 30085141701
Deaton Inventory
2N 2FE 2FW 3N 3FW 3FE Total Omnicell
2 2
2 2 4
3 3
NDC
66685100100
63739012815
Where the NDC numbers from each of these two spreadsheets match, leaving the ones that don't have a matching NDC from the Deaton Inventory file to leave the 2N, 2FE, 2FW, 3N, 3FW, 3FW, Total Omnicell fields blank.
"=VLOOKUP(B2,PRICING,9,FALSE)",
Where Pricing is a cell reference to the sheet that I am trying to use the VLookup to find the values that correspond with the appropriate codes.
Inventory Catalog Sheet
ITEM_NO NDC UPC
1804731 55513003201 35551303201
1126283 55513004801 35551304801
1801976 55513009701 35551309701
1440049 14290055098 31429055098
1601863 00085141701 30085141701
Deaton Inventory
2N 2FE 2FW 3N 3FW 3FE Total Omnicell
2 2
2 2 4
3 3
NDC
66685100100
63739012815
Where the NDC numbers from each of these two spreadsheets match, leaving the ones that don't have a matching NDC from the Deaton Inventory file to leave the 2N, 2FE, 2FW, 3N, 3FW, 3FW, Total Omnicell fields blank.
If "pricing" contains all the records you need to look in and is at least 9 columns wide then there is no issue with the formula. Probably a data type issue....try:
=VLOOKUP(VALUE(B2),PRICING,9,FALSE)
or
=VLOOKUP(TEXT(B2,"0"),PRICING,9,FALSE)
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
=VLOOKUP(VALUE(B2),PRICING,9,FALSE)
or
=VLOOKUP(TEXT(B2,"0"),PRICING,9,FALSE)
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
- Status
Similar threads
