Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup a value from multiple columns of two workbooks
I have two files in excel that I need to use some macros or functions to
match the items. That is one of the file is file "A" contains 3 columns named as Amout, SAP Number(which is blank) and Bill No. The other file "B" contains 3 columns named as Reference no, Doc No. and Amount. I want to get Doc No from "B" file to Column SAP Number of File "A" where the Bill No. and Amount column values of file "A" and Reference no and Amount columns value of file "B" matches. If Bill No. and "Reference no" matches but Amount does not match they can appear as below or seperate worksheet so that I can investigate these. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup a value from multiple columns of two workbooks
This can be done with VLOOKUP() but a little more information is needed.
In file 'B', can the Reference no (to be matched with Bill No. in A) show up more than once in your list but with different values for the Amount? Or will a Reference number show up only once in file 'B'? If the Reference No. will only show up once, then these formulas should work for you. First requirement is that the information in workbook 'B' has to be sorted by the Reference No, with smallest at the top of the list. In 'B' your 3 columns go from A2 to C9 (change in formula to match your range in that book), and are laid out in the sequence you gave: Ref #, Doc #, Amount. In book 'A' your entries are in the same range (A2 to C9). You should have both workbooks opened up when you initially set up these formulas, but after you set them up, you can close 'B' and it will still work. In the cell for the first SAP entry (B2) put this formula: =VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,2,FALSE) where B.xls is the name of workbook 'B' and BookBDataSheet is the name of the sheet with the information to be moved and compared. You can now 'extend' that formula to the end of the list in book 'A'. If you will put this formula in D2 in book 'A' and extend it down to the end of your list, it will then display "Amt Error" anytime that the amounts don't match. =IF(A2=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,3,FALSE),"","Amt Error") the FALSE part of the VLOOKUP forces it to look for an exact match between the Bill Number in 'A' and the Reference No. in 'B', but as I mentioned earlier, the Reference No.s in 'B' must be in order from least to greatest. This works if each Reference No in 'B' only appears once, if they can show up more than once, then we have more work to do. The cells in workbook B are written as $A$2 and $C$9 instead of just plain A2 and C9 to keep them from changing as you drag/extend the formula down the worksheet in book A. "Asad - Shareef" wrote: I have two files in excel that I need to use some macros or functions to match the items. That is one of the file is file "A" contains 3 columns named as Amout, SAP Number(which is blank) and Bill No. The other file "B" contains 3 columns named as Reference no, Doc No. and Amount. I want to get Doc No from "B" file to Column SAP Number of File "A" where the Bill No. and Amount column values of file "A" and Reference no and Amount columns value of file "B" matches. If Bill No. and "Reference no" matches but Amount does not match they can appear as below or seperate worksheet so that I can investigate these. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup a value from multiple columns of two workbooks
Hi Latham
Thanks very much buddy. Your information was very much vital in solving my queries. Thanks again. Rgds Asad - Shareef "JLatham" wrote: This can be done with VLOOKUP() but a little more information is needed. In file 'B', can the Reference no (to be matched with Bill No. in A) show up more than once in your list but with different values for the Amount? Or will a Reference number show up only once in file 'B'? If the Reference No. will only show up once, then these formulas should work for you. First requirement is that the information in workbook 'B' has to be sorted by the Reference No, with smallest at the top of the list. In 'B' your 3 columns go from A2 to C9 (change in formula to match your range in that book), and are laid out in the sequence you gave: Ref #, Doc #, Amount. In book 'A' your entries are in the same range (A2 to C9). You should have both workbooks opened up when you initially set up these formulas, but after you set them up, you can close 'B' and it will still work. In the cell for the first SAP entry (B2) put this formula: =VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,2,FALSE) where B.xls is the name of workbook 'B' and BookBDataSheet is the name of the sheet with the information to be moved and compared. You can now 'extend' that formula to the end of the list in book 'A'. If you will put this formula in D2 in book 'A' and extend it down to the end of your list, it will then display "Amt Error" anytime that the amounts don't match. =IF(A2=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,3,FALSE),"","Amt Error") the FALSE part of the VLOOKUP forces it to look for an exact match between the Bill Number in 'A' and the Reference No. in 'B', but as I mentioned earlier, the Reference No.s in 'B' must be in order from least to greatest. This works if each Reference No in 'B' only appears once, if they can show up more than once, then we have more work to do. The cells in workbook B are written as $A$2 and $C$9 instead of just plain A2 and C9 to keep them from changing as you drag/extend the formula down the worksheet in book A. "Asad - Shareef" wrote: I have two files in excel that I need to use some macros or functions to match the items. That is one of the file is file "A" contains 3 columns named as Amout, SAP Number(which is blank) and Bill No. The other file "B" contains 3 columns named as Reference no, Doc No. and Amount. I want to get Doc No from "B" file to Column SAP Number of File "A" where the Bill No. and Amount column values of file "A" and Reference no and Amount columns value of file "B" matches. If Bill No. and "Reference no" matches but Amount does not match they can appear as below or seperate worksheet so that I can investigate these. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup on multiple columns | Excel Discussion (Misc queries) | |||
making multiple columns when printing long datasheet | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup + Sum multiple columns | Excel Worksheet Functions | |||
how do I filter for 1 variable in multiple columns | Excel Worksheet Functions |