Excel Formula Help 1

[arvarr]

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

 

[SkipVought]

Hi,

On question 3, combo gave you the answer.
[tt]
=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)[highlight #FCE94F]*ROW(Table1[Outcome])[/highlight])-1),"")
[/tt]
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 [highlight #FCE94F]ROWS()[/highlight] function and everything within those parentheses too, along with the [highlight #FCE94F]leading Asterisk[/highlight]. 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

 

[SkipVought]

Here's a solution that will identify rows that match the criteria in ROW 1 columns I:K, using Conditional Formatting.

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

 

[arvarr]

Hi Skip
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

 

[SkipVought]

You select all the cell you want the CF to affect before opening the CF Wizard.

Skip,

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

 

[arvarr]

Hi Skip
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

 

[SkipVought]

Columns I:K examples have individual values that must be compared to the ranges in the Criteria Table. Thats column L. In column M is a formula that COUNTS the number of Criteria Table rows that satisfy the 3 given values for any row.
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

 

[arvarr]

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

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

Thanks,
arv

 

[SkipVought]

So i see 2 rows that have a value in column M > 1: row 3 & row 8

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

 

[arvarr]

Hi Skip
Ah...Noted.
As usual, Thanks for your help .

Regards,
arv

 

[SkipVought]

Hope it works for you.

Skip,

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

 

[arvarr]

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

 

[SkipVought]

Cleaning up corrupt data is often a painstaking effort. It's a dirty job, but someone's gotta do it!

Skip,

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