| View previous topic :: View next topic |
| 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. |
|
| Back to top |
|
 |
scsisys
OOo Enthusiast
Joined: 17 Dec 2009
Posts: 164
|
| 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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| 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
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1852
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
|
