+ Reply to Thread
Results 1 to 5 of 5

A function that verifies pair matches

  1. #1
    Tetsuya Oguma
    Guest

    A function that verifies pair matches

    Hi all,

    I need a one simlpe function to return true if all the pairs are entered
    correctly in both Column A and B and false if ANY of the pairs misses an
    entry in either Column A or B. To illustrate:

    (True)
    A B
    1 ABC 100
    2 DEF 50
    3 GHI 10

    (False)
    A B
    1 ABC 100
    2 DEF
    3 10

    Something along the line of {=SUM(A1:A3, B1:B3)}???

    Thanks in advance
    ---
    Tetsuya Oguma Singapore

  2. #2
    Biff
    Guest

    Re: A function that verifies pair matches

    Hi!

    Try this:

    =SUMPRODUCT(--(A1:A3<>""),--(B1:B3<>""))=ROWS(A1:A3)

    This would also return FALSE:

    > A B
    > 1 ABC 100
    > 2
    > 3 GHI 10


    Biff

    "Tetsuya Oguma" <[email protected]> wrote in message
    news:[email protected]...
    > Hi all,
    >
    > I need a one simlpe function to return true if all the pairs are entered
    > correctly in both Column A and B and false if ANY of the pairs misses an
    > entry in either Column A or B. To illustrate:
    >
    > (True)
    > A B
    > 1 ABC 100
    > 2 DEF 50
    > 3 GHI 10
    >
    > (False)
    > A B
    > 1 ABC 100
    > 2 DEF
    > 3 10
    >
    > Something along the line of {=SUM(A1:A3, B1:B3)}???
    >
    > Thanks in advance
    > ---
    > Tetsuya Oguma Singapore




  3. #3
    Tetsuya Oguma
    Guest

    Re: A function that verifies pair matches

    Hi BIff,

    Thank you so much for this and my previous post!

    If I want to still have a function returning True in the following
    situation, what function should I write?

    > > A B
    > > 1 ABC 100
    > > 2
    > > 3 GHI 10


    Sorry for complicating the issue, but that will be THE ultimate solution to
    my problem!

    Thanks again.
    ---
    Tetsuya

    "Biff" wrote:

    > Hi!
    >
    > Try this:
    >
    > =SUMPRODUCT(--(A1:A3<>""),--(B1:B3<>""))=ROWS(A1:A3)
    >
    > This would also return FALSE:
    >
    > > A B
    > > 1 ABC 100
    > > 2
    > > 3 GHI 10

    >
    > Biff
    >
    > "Tetsuya Oguma" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi all,
    > >
    > > I need a one simlpe function to return true if all the pairs are entered
    > > correctly in both Column A and B and false if ANY of the pairs misses an
    > > entry in either Column A or B. To illustrate:
    > >
    > > (True)
    > > A B
    > > 1 ABC 100
    > > 2 DEF 50
    > > 3 GHI 10
    > >
    > > (False)
    > > A B
    > > 1 ABC 100
    > > 2 DEF
    > > 3 10
    > >
    > > Something along the line of {=SUM(A1:A3, B1:B3)}???
    > >
    > > Thanks in advance
    > > ---
    > > Tetsuya Oguma Singapore

    >
    >
    >


  4. #4
    vezerid
    Guest

    Re: A function that verifies pair matches

    Tetsuya,
    try this:
    =SUMPRODUCT(MOD((A1:A3<>"")+(B1:B3<>""),2))=0

    This formula will return true if, in your range, all rows are either
    completely filled or completely empty.

    Does this help?

    Kostis Vezerides


  5. #5
    Tetsuya Oguma
    Guest

    Re: A function that verifies pair matches

    > Does this help?
    You are LEGENDARY, mate!

+ 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