OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Help with Open Office calculations IF/AND statements?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
drjorn
Newbie
Newbie


Joined: 14 May 2012
Posts: 2

PostPosted: Mon May 14, 2012 1:47 pm    Post subject: Help with Open Office calculations IF/AND statements? Reply with quote

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.
Back to top
View user's profile Send private message
scsisys
OOo Advocate
OOo Advocate


Joined: 17 Dec 2009
Posts: 248

PostPosted: Mon May 14, 2012 6:30 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
drjorn
Newbie
Newbie


Joined: 14 May 2012
Posts: 2

PostPosted: Tue May 15, 2012 4:29 am    Post subject: Reply with quote

[quote="scsisys"]drjorn...

Are both columns of names in alphabetical order ?
Column BE of which report; 1 or 2 ?

scsisys[/quote]

Yes
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

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

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
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Tue May 15, 2012 5:30 am    Post subject: Reply with quote

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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group