Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default How do I match 2 cols on two books and get price info for all matc

Hi folks I have a major problem. I have done this manually in the past but it
has just taken so long and is so tedious that I am sure there is a quicker
way using VLOOKUP or some other function however I have been having no succes
so was hoping some kind hearted individual out there could help me out....
anyhows here'e my problem.

I have two worksheets one is a Reference Look Up table , we shall call this
workbook A. Its columns are as follows :
WORKBOOK A

ColA Col B Part No Desc ColE ColF ColG
===== ====

I also have another Worksheet which is a pricelist we shall call this
Workbook B. Its columns areas follows :
WORKBOOK B

PART NO DESC PRICE
====== ==== =====

What I require is to add a Price Column to Workbook A in column H. Any Part
Number in Workbook A that matches Part No in Workbook B should be copied and
inserted in Workbook A in the new price column for that part.

- This is made more complicated as the Part No's in Workbook A may
appear many times (i.e. a certain part is shared by many different products),
however it will only appear once on the pricelist - Workbook B.

- Also some part no's may exist on Workbook A and not on Workbook B,
similarly some part no's may exist on Workbook B but not on Workbook A. In
any of these cases this information needs to be written either to a new
worksheet or added to the bottom of the data in Workbook A.


Hope somewhere out there can provide me some help as my head hurts from
banging it against this monitor !! lol

Thanks folks :)


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default How do I match 2 cols on two books and get price info for all matc

To obtain price:

=if(ISNA(VLOOKUP(C2,Workbookb!A1:C100,3,0)),"Part not
found",VLOOKUP(C2,Workbookb!A1:C100,3,0))

To obtain description:

=if(ISNA(VLOOKUP(C2,Workbookb!A1:C100,2,0)),"Part not
found",VLOOKUP(Cc2,Workbookb!A1:C100,2,0))

Copy down as required and change range of w/book B as needed.

For mismatches between workbooks A & B you probably need VBA code if want to
write them out to a separate sheet or add to the end of exising

HTH

"Sonny" wrote:

Hi folks I have a major problem. I have done this manually in the past but it
has just taken so long and is so tedious that I am sure there is a quicker
way using VLOOKUP or some other function however I have been having no succes
so was hoping some kind hearted individual out there could help me out....
anyhows here'e my problem.

I have two worksheets one is a Reference Look Up table , we shall call this
workbook A. Its columns are as follows :
WORKBOOK A

ColA Col B Part No Desc ColE ColF ColG
===== ====

I also have another Worksheet which is a pricelist we shall call this
Workbook B. Its columns areas follows :
WORKBOOK B

PART NO DESC PRICE
====== ==== =====

What I require is to add a Price Column to Workbook A in column H. Any Part
Number in Workbook A that matches Part No in Workbook B should be copied and
inserted in Workbook A in the new price column for that part.

- This is made more complicated as the Part No's in Workbook A may
appear many times (i.e. a certain part is shared by many different products),
however it will only appear once on the pricelist - Workbook B.

- Also some part no's may exist on Workbook A and not on Workbook B,
similarly some part no's may exist on Workbook B but not on Workbook A. In
any of these cases this information needs to be written either to a new
worksheet or added to the bottom of the data in Workbook A.


Hope somewhere out there can provide me some help as my head hurts from
banging it against this monitor !! lol

Thanks folks :)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 06:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"