If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
lookup to see if multiple values exist
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. |
#2
|
|||
|
|||
lookup to see if multiple values exist
On Mon, 10 May 2010 13:49:01 -0700, Yegod
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$5VLOOKUP(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 |
#3
|
|||
|
|||
lookup to see if multiple values exist
Fantastic, that works!
Thank you!!! "Lars-Ã…ke Aspelin" wrote: On Mon, 10 May 2010 13:49:01 -0700, Yegod 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$5VLOOKUP(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 . |
Thread Tools | |
Display Modes | |
|
|