lookup to see if multiple values exist
in [Functions]
|
Prev: Pivot Table from 2 worksheets
Next: =SUBSTITUTE(C4,"~?#","") will this work to remove multiple string
From: Yegod on 10 May 2010 16:49 Hi, I am faced with this problem. I have data as follows: Col1 Col2 Col3 S1 H1 Value 1 S1 H2 Value 2 S2 H1 Value 3 S2 H2 Value 3 I need to roll this data up so that I get ColR1 ColR2 S1 "Multiple" S2 Value3 Basically if S1 has multiple values then shown "Multiple". If it has the same value for all occurrences, as is the case for S2, show that value. These are formulas with dynamic data which I need to calculate automatically. I cannot use a pivot table. I do not want to use a macro unless I have to. I tried to think of a logic saying if min(value)<>max(value), but these functions only work with numeric data. Please help.
From: Lars-�ke Aspelin on 10 May 2010 17:06 On Mon, 10 May 2010 13:49:01 -0700, Yegod <Yegod(a)discussions.microsoft.com> wrote: >Hi, > >I am faced with this problem. I have data as follows: > >Col1 Col2 Col3 >S1 H1 Value 1 >S1 H2 Value 2 >S2 H1 Value 3 >S2 H2 Value 3 > >I need to roll this data up so that I get >ColR1 ColR2 >S1 "Multiple" >S2 Value3 > >Basically if S1 has multiple values then shown "Multiple". If it has the >same value for all occurrences, as is the case for S2, show that value. > >These are formulas with dynamic data which I need to calculate >automatically. I cannot use a pivot table. I do not want to use a macro >unless I have to. > >I tried to think of a logic saying if min(value)<>max(value), but these >functions only work with numeric data. > >Please help. Assuming Col1 = A, Col2 =B, Col3 = C (actually Col2 is of no interest here) Assuming ColR1 = D, ColR2 = E Assuming that there is a header row so the real data table starts on row 2 (and in the example goes down to row 5). Try the following formula in Cell E2 =IF(SUMPRODUCT((A$2:A$5=D2)*(C$2:C$5<>VLOOKUP(D2,A$2:C$5,3,FALSE))),"Multiple",VLOOKUP(D2,A$2:C$5,3,FALSE)) Change the 5 in all places to fit the size of your data table. Copy the formula down column E as far as you have data in column D. The SUMPRODUCT part of the formula counts all occations of "S" that have a different "Value" than the one the VLOOKUP first finds. If the result of the SUMPRODUCT is more than 0, that means that there are "Multiple" Values. If the result is 0 we just return the value found by VLOOKUP. Hope this helps / Lars-�ke
From: Yegod on 10 May 2010 20:58
Fantastic, that works! Thank you!!! "Lars-Åke Aspelin" wrote: > On Mon, 10 May 2010 13:49:01 -0700, Yegod > <Yegod(a)discussions.microsoft.com> wrote: > > >Hi, > > > >I am faced with this problem. I have data as follows: > > > >Col1 Col2 Col3 > >S1 H1 Value 1 > >S1 H2 Value 2 > >S2 H1 Value 3 > >S2 H2 Value 3 > > > >I need to roll this data up so that I get > >ColR1 ColR2 > >S1 "Multiple" > >S2 Value3 > > > >Basically if S1 has multiple values then shown "Multiple". If it has the > >same value for all occurrences, as is the case for S2, show that value. > > > >These are formulas with dynamic data which I need to calculate > >automatically. I cannot use a pivot table. I do not want to use a macro > >unless I have to. > > > >I tried to think of a logic saying if min(value)<>max(value), but these > >functions only work with numeric data. > > > >Please help. > > Assuming Col1 = A, Col2 =B, Col3 = C > (actually Col2 is of no interest here) > > Assuming ColR1 = D, ColR2 = E > > Assuming that there is a header row so the real data table starts on > row 2 (and in the example goes down to row 5). > > Try the following formula in Cell E2 > > =IF(SUMPRODUCT((A$2:A$5=D2)*(C$2:C$5<>VLOOKUP(D2,A$2:C$5,3,FALSE))),"Multiple",VLOOKUP(D2,A$2:C$5,3,FALSE)) > > Change the 5 in all places to fit the size of your data table. > > Copy the formula down column E as far as you have data in column D. > > The SUMPRODUCT part of the formula counts all occations of "S" that > have a different "Value" than the one the VLOOKUP first finds. > If the result of the SUMPRODUCT is more than 0, that means that there > are "Multiple" Values. If the result is 0 we just return the value > found by VLOOKUP. > > Hope this helps / Lars-Åke > > > . > |