+ Reply to Thread
Results 1 to 2 of 2

Compare data

  1. #1
    Registered User
    Join Date
    09-17-2006
    Posts
    18

    Compare data

    Hi All,

    I have a question regarding comparing of data from execl.
    In my execl file there is 2 worksheet. One named Data Source and the other named Invoice Number.
    In Data Source there are below fields:
    1. Refnum (unique number)
    2. FCharge

    In Invoice Number:
    1. BLNUM
    2. FCODE
    3. INVNO

    In Date Source work sheet, I will like to create a field as InvoiceNo.
    I need to write something to compare the below:
    1st: Compare Refnum = BLNUM
    2nd: Fcharge = Fcode

    We need to check the first part first, if 1st part pass, then will check the 2nd part.
    If both pass then in Data Source.InvoiceNo will return with the value of INVNO.

    Can anyone tell me what should i do?
    I got totally no idea how can i do so.

    Thanks in advance.

  2. #2
    Forum Contributor
    Join Date
    12-10-2008
    Location
    Hong Kong
    MS-Off Ver
    Office 365 PC Version
    Posts
    193

    Re: Compare data

    Hi,

    I think you can do this with an array formula.

    Here is an example

    offset( INVOICENUMBER C1,Sum(IF(A1=INVOICENUMBER A1:A100,1,0)*if(B1=INVOICENUMBER B1:B100,1,0)*row(INVOICENUMBERC1:C100))-1)

    PUT THIS FORMULA IN DATA SOURCE. A=REFNUM B=FCHARGE

    In INVOICE NUMBER A=BLNUM, B=FCODE and C=INVO

    The formula first finds the match and returns the row number, then uses an offset function to find the INVO

    Remember to use control shift enter to make an array formula.

    Let me know if it does not work.

    Regards JV

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1