You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Rafael Ribeiro Rezende <ra...@gmail.com> on 2012/03/25 20:42:21 UTC
Match columns with multiple criteria using POI
Hi all,
Day ago I made a question about a problem I had while evaluating formulas.
But now I would like to focus on the solution for matching columns with
multiple criteria, instead of trying to solve that specific formula I
posted. I don't know if I should reply on that, but since it's now a more
general question, I decided to create a new thread. I hope it's ok.
Well, I'm trying to make a MATCH of columns using multiple criteria, as in
the example below:
A1: Part B1: Code C1: Price D1: Find Part E1: Find Code
A2: x B2: 11 C2: 5.00 D2: y E2: 12
A3: x B3: 12 C3: 6.00
A4: y B4: 11 C4: 7.00
A5: y B5: 12 C5: 8.00
Where D2 and E2 are criterias. If D2 and E2 match with columns A and B
respectively, I should get the result from corresponding column C. (In this
example: 8.00)
For this example I tried the following Formulas on Excel:
=LOOKUP(D2&E2;A2:A5&B2:B5;C2:C5)
{=INDEX($C$2:$C$5;MATCH(D2&E2;$A$2:$A$5&$B$2:$B$5;0))}
=SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);INDEX(C2:C5;0;0))
=SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);C2:C5)
All of them give me the RIGHT result in Excel and OOCalc. But when using
POI, I get errorcode 15 from the first one. Exception *Unexpected eval
type* from
the second, and Exception *Invalid arg type for SUMPRODUCT* from the third
and last formulas.
Does anyone know how to match columns with multiple criteria by using a
Formula which can be parsed by Apache POI?
Note: With Apache POI I'm successfully matching columns with one single
criteria. So, I guess the error is not in my code, but maybe the format of
those Formulas above aren't supported by POI.
Thanks in advance
--
Rafael R Rezende
Re: Match columns with multiple criteria using POI
Posted by Rafael Ribeiro Rezende <ra...@gmail.com>.
Thanks for the reply Yegor! I'll open the bug as soon as I can!
Rafael
On Mon, Mar 26, 2012 at 11:58 AM, Yegor Kozlov <ye...@dinom.ru>wrote:
> > For this example I tried the following Formulas on Excel:
> >
> > =LOOKUP(D2&E2;A2:A5&B2:B5;C2:C5)
> > {=INDEX($C$2:$C$5;MATCH(D2&E2;$A$2:$A$5&$B$2:$B$5;0))}
>
> This is expected. Array arguments (those enclosed in { and }) are not
> yet supported by the formula evaluator.
>
> > =SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);INDEX(C2:C5;0;0))
> > =SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);C2:C5)
> >
>
> Aha, this looks like a bug ! Somehow the first argument evaluates to
> #VALUE!.
> Can you open a new bug in Bugzilla and upload a test spreadsheet and
> Java code that reproduces the trouble?
>
> Yegor
>
>
> > All of them give me the RIGHT result in Excel and OOCalc. But when using
> > POI, I get errorcode 15 from the first one. Exception *Unexpected eval
> > type* from
> > the second, and Exception *Invalid arg type for SUMPRODUCT* from the
> third
> > and last formulas.
> >
> > Does anyone know how to match columns with multiple criteria by using a
> > Formula which can be parsed by Apache POI?
> >
> > Note: With Apache POI I'm successfully matching columns with one single
> > criteria. So, I guess the error is not in my code, but maybe the format
> of
> > those Formulas above aren't supported by POI.
> >
> > Thanks in advance
> >
> > --
> > Rafael R Rezende
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Rafael R Rezende
Re: Match columns with multiple criteria using POI
Posted by Yegor Kozlov <ye...@dinom.ru>.
> For this example I tried the following Formulas on Excel:
>
> =LOOKUP(D2&E2;A2:A5&B2:B5;C2:C5)
> {=INDEX($C$2:$C$5;MATCH(D2&E2;$A$2:$A$5&$B$2:$B$5;0))}
This is expected. Array arguments (those enclosed in { and }) are not
yet supported by the formula evaluator.
> =SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);INDEX(C2:C5;0;0))
> =SUMPRODUCT(--(A2:A5=D2);--(B2:B5=E2);C2:C5)
>
Aha, this looks like a bug ! Somehow the first argument evaluates to #VALUE!.
Can you open a new bug in Bugzilla and upload a test spreadsheet and
Java code that reproduces the trouble?
Yegor
> All of them give me the RIGHT result in Excel and OOCalc. But when using
> POI, I get errorcode 15 from the first one. Exception *Unexpected eval
> type* from
> the second, and Exception *Invalid arg type for SUMPRODUCT* from the third
> and last formulas.
>
> Does anyone know how to match columns with multiple criteria by using a
> Formula which can be parsed by Apache POI?
>
> Note: With Apache POI I'm successfully matching columns with one single
> criteria. So, I guess the error is not in my code, but maybe the format of
> those Formulas above aren't supported by POI.
>
> Thanks in advance
>
> --
> Rafael R Rezende
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org