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-hand ROWS() 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,
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,
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
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
Pain point is just the one at a time effort but considering a once off identification process, should be ok.
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