lookup to see if multiple values exist

[Yegod]

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.

 

[Lars-Åke Aspelin]

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

 

[Yegod]

Fantastic, that works!

Thank you!!!

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


.