software

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 quot;Aquot; contains 3 columns named
as Amout, SAP Number(which is blank) and Bill No.

The other file quot;Bquot; contains 3 columns named as Reference no, Doc No. and
Amount.

I want to get Doc No from quot;Bquot; file to Column SAP Number of File quot;Aquot; where
the Bill No. and Amount column values of file quot;Aquot; and Reference no and Amount
columns value of file quot;Bquot; matches.

If Bill No. and quot;Reference noquot; matches but Amount does not match they can
appear as below or seperate worksheet so that I can investigate these.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 quot;Amt Errorquot; anytime that the amounts don't
match.
=IF(A2=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,3,FALSE),quot;quot;,quot;Amt Errorquot;)

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.

quot;Asad - Shareefquot; wrote:

gt; I have two files in excel that I need to use some macros or functions to
gt; match the items. That is one of the file is file quot;Aquot; contains 3 columns named
gt; as Amout, SAP Number(which is blank) and Bill No.
gt;
gt; The other file quot;Bquot; contains 3 columns named as Reference no, Doc No. and
gt; Amount.
gt;
gt; I want to get Doc No from quot;Bquot; file to Column SAP Number of File quot;Aquot; where
gt; the Bill No. and Amount column values of file quot;Aquot; and Reference no and Amount
gt; columns value of file quot;Bquot; matches.
gt;
gt; If Bill No. and quot;Reference noquot; matches but Amount does not match they can
gt; appear as below or seperate worksheet so that I can investigate these.
gt;

Hi Latham
Thanks very much buddy.
Your information was very much vital in solving my queries.
Thanks again.
Rgds
Asad - Shareefquot;JLathamquot; wrote:

gt; This can be done with VLOOKUP() but a little more information is needed.
gt;
gt; In file 'B', can the Reference no (to be matched with Bill No. in A) show up
gt; more than once in your list but with different values for the Amount? Or
gt; will a Reference number show up only once in file 'B'?
gt;
gt; If the Reference No. will only show up once, then these formulas should work
gt; for you. First requirement is that the information in workbook 'B' has to be
gt; sorted by the Reference No, with smallest at the top of the list.
gt;
gt; In 'B' your 3 columns go from A2 to C9 (change in formula to match your
gt; range in that book), and are laid out in the sequence you gave: Ref #, Doc #,
gt; Amount.
gt;
gt; In book 'A' your entries are in the same range (A2 to C9).
gt;
gt; You should have both workbooks opened up when you initially set up these
gt; formulas, but after you set them up, you can close 'B' and it will still work.
gt;
gt; In the cell for the first SAP entry (B2) put this formula:
gt; =VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,2,FALSE)
gt; where B.xls is the name of workbook 'B' and BookBDataSheet is the name of
gt; the sheet with the information to be moved and compared. You can now
gt; 'extend' that formula to the end of the list in book 'A'.
gt;
gt; If you will put this formula in D2 in book 'A' and extend it down to the end
gt; of your list, it will then display quot;Amt Errorquot; anytime that the amounts don't
gt; match.
gt; =IF(A2=VLOOKUP(C2,[B.xls]BookBDataSheet!$A$2:$C$9,3,FALSE),quot;quot;,quot;Amt Errorquot;)
gt;
gt; the FALSE part of the VLOOKUP forces it to look for an exact match between
gt; the Bill Number in 'A' and the Reference No. in 'B', but as I mentioned
gt; earlier, the Reference No.s in 'B' must be in order from least to greatest.
gt;
gt; This works if each Reference No in 'B' only appears once, if they can show
gt; up more than once, then we have more work to do.
gt;
gt; The cells in workbook B are written as $A$2 and $C$9 instead of just plain
gt; A2 and C9 to keep them from changing as you drag/extend the formula down the
gt; worksheet in book A.
gt;
gt; quot;Asad - Shareefquot; wrote:
gt;
gt; gt; I have two files in excel that I need to use some macros or functions to
gt; gt; match the items. That is one of the file is file quot;Aquot; contains 3 columns named
gt; gt; as Amout, SAP Number(which is blank) and Bill No.
gt; gt;
gt; gt; The other file quot;Bquot; contains 3 columns named as Reference no, Doc No. and
gt; gt; Amount.
gt; gt;
gt; gt; I want to get Doc No from quot;Bquot; file to Column SAP Number of File quot;Aquot; where
gt; gt; the Bill No. and Amount column values of file quot;Aquot; and Reference no and Amount
gt; gt; columns value of file quot;Bquot; matches.
gt; gt;
gt; gt; If Bill No. and quot;Reference noquot; matches but Amount does not match they can
gt; gt; appear as below or seperate worksheet so that I can investigate these.
gt; gt;