## Excel Formula Help

## Excel Formula Help

(OP)

Hi

Needing some help with excel.

1. What would be the formula to identify duplication/s in the table criteria. There might be 2,3,4 or more duplication especially with those in the ranges.

2. Once the number of matched records are identified, is there anyway to indicate which row/s the duplication in the table criteria?

3. Also, i encountered this issue. Apparently the example falls into the criteria table outcome 1 and outcome 2.

It is giving outcome 4 and not entirely sure why.

Thanks,

arv

Needing some help with excel.

1. What would be the formula to identify duplication/s in the table criteria. There might be 2,3,4 or more duplication especially with those in the ranges.

2. Once the number of matched records are identified, is there anyway to indicate which row/s the duplication in the table criteria?

3. Also, i encountered this issue. Apparently the example falls into the criteria table outcome 1 and outcome 2.

It is giving outcome 4 and not entirely sure why.

Thanks,

arv

## RE: Excel Formula Help

On question 3, combo gave you the answer.

=IFERROR(INDEX(Table1[Outcome],

SUMPRODUCT((Table1[Upper A]>=$J3)*(Table1[Lower A]<=$J3)*(Table1[Upper B]>=$K3)*(Table1[Lower B]<=$K3)*(Table1[Upper C]>=L3)*(Table1[Lower C]<=L3)*ROW(Table1[Outcome]))-1),"")The

SUMPRODUCT()function you are using identified rows 2 & 3.Since it sums, it returns 5

But 1 is subtracted from that sum due to the fact that data begins in row 2, not 1, leaving 4, which corresponds to Output 4.

On question 1, you can simply extract the entire

SUMPRODUCT()function, that is everything those parentheses, and then remove the right-handROWS()function and everything within those parentheses too, along with the leading Asterisk. That will COUNT the number of rows. You could place that formula next to the original, giving you a count of outcome rows.Question 2 is more difficult and may be able to be solved with Conditional Formatting. I'll have to work on that.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel Formula Help

BTW, the CF expression used is in cell L1, FYI.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel Formula Help

Thanks for the answer to 1 and 3.

For question 2:

The CF:

'=AND($i$1>=$B2,$i$1<=$C2,$K$1>=$D2,$K$1<=$E2,$L$1>=$F2,$L$1<=$G2)

On the Expected Outcome, if i have multiple records, say 10,000 rows, how do i apply the formula onto the CF?

Thanks,

arv

## RE: Excel Formula Help

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel Formula Help

Thanks, i was referring to the columns from I to K.

If i have 10,000 lines in column I to K with different combinations, how do i factor them into the CF formula?

2043 180 101 =AND($i$1>=$B2,$i$1<=$C2,$K$1>=$D2,$K$1<=$E2,$L$1>=$F2,$L$1<=$G2)

A B C Expected Outcome Outcome Count

2043 180 101 Outcome 4 2

2043 400 101

2043 450 105 Outcome 5

Thanks,

arv

## RE: Excel Formula Help

So now you can see every example row where there are AMBIGUOUS CRITERIA ie COUNT > 1.

1) COPY the 3 values and PASTE them in Row 1 I:K and see the CF rows in the Criteria Table.

2) CORRECT the range limits accordingly.

3) Rinse & Repeat.

This is a one-at-a-time effort, in order to make the necessary corrections.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel Formula Help

Noted about the formula in column M to identify the ambiguous records.

I'm probably not thinking straight about the 3 steps.

Thanks,

arv

## RE: Excel Formula Help

COPY the 3 values in row 3,

PASTE into I1

Observe the 2 rows CF in the the Criteria table

Make necessary corrections in criteria ranges

Then repeat for row 8 if your corrections did not correct for those values.

BTW, the formula displayed in L1 is solely there FYI. That is the formula that I used in the CF for the Criteria table.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel Formula Help

Ah...Noted.

As usual, Thanks for your help .

Regards,

arv

## RE: Excel Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

## RE: Excel Formula Help

Pain point is just the one at a time effort but considering a once off identification process, should be ok.

## RE: Excel Formula Help

_{ Just traded in my OLD subtlety... for a NUance!}"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein