[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
drjorn
Newbie

Joined: 14 May 2012
Posts: 2

 Posted: Mon May 14, 2012 1:47 pm    Post subject: Help with Open Office calculations IF/AND statements? I need some help with open office calc I need to compare in Report One a range of names (col K) and dates of service (col M) with Report Two's range of names (col BD) and dates of service (col BI) and where there is an exact match between the two reports i.e. Joe Blow and 5/14/12 matches in Report Two Joe Blow and 5/14/12. Then I need the formula to grab the cell data in Col BE where that match occurs. Col BE is the date paid. Good luck - my hair is now gray.
scsisys

Joined: 17 Dec 2009
Posts: 248

 Posted: Mon May 14, 2012 6:30 pm    Post subject: drjorn... Are both columns of names in alphabetical order ? Column BE of which report; 1 or 2 ? scsisys_________________OO 3.2.1 Win XP /SP3
drjorn
Newbie

Joined: 14 May 2012
Posts: 2

 Posted: Tue May 15, 2012 4:29 am    Post subject: [quote="scsisys"]drjorn... Are both columns of names in alphabetical order ? Column BE of which report; 1 or 2 ? scsisys[/quote] Yes
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

Posted: Tue May 15, 2012 4:34 am    Post subject: Re: Help with Open Office calculations IF/AND statements?

 drjorn wrote: I need some help with open office calc I need to compare in Report One a range of names (col K) and dates of service (col M) with Report Two's range of names (col BD) and dates of service (col BI) and where there is an exact match between the two reports i.e. Joe Blow and 5/14/12 matches in Report Two Joe Blow and 5/14/12. Then I need the formula to grab the cell data in Col BE where that match occurs. Col BE is the date paid. Good luck - my hair is now gray.

if this is a fairly large project you might want to post the problem in the Base forum.

It sounds to me like a fairly natural problem for a data base. Treat the two reports as separte tables and just do a Select command on them. I don't use Base so I'm no use in how to approach it in a practical manner.
_________________
jrkrideau
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

Posted: Tue May 15, 2012 5:30 am    Post subject:

Say Names and Dates in columns K and M start in row 2 and go down to row 500 and Names and Dates in columns BD and BI start in row 2 and go down to row 2000, try this array formula...
 Code: IF(ISNUMBER(MATCH(\$K2&\$M2;\$BD\$2:\$BD\$2000&\$BI\$2:\$BI\$2000;0));INDEX(\$BE\$2:\$BE\$2000;MATCH(\$K2&\$M2;\$BD\$2:\$BD\$2000&\$BI\$2:\$BI\$2000;0));"")
Array formulae must be entered using Ctrl+Shift+Enter and the Ctrl key must be held down when the fill handle is dragged to copy the formula into adjacent cells.

If you don't want to use an array formula use =BD2&" "&BI2 in say BJ2 filled down to BJ2000 to act as a helper column then use...
 Code: =IF(ISNUMBER(MATCH(\$K2&" "&\$M2;\$BJ\$2:\$BJ\$2000;0));INDEX(\$BE\$2:\$BE\$2000;MATCH(\$K2&" "&\$M2;\$BJ\$2:\$BJ\$2000;0));"")

See sample doc (IF AND question.ods) for solution using helper column...
http://www.mediafire.com/view/?bhwo8j3lqj74ap9

Ken Johnson
_________________