×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Formula Help

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

RE: Excel Formula Help

Hi,

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

(OP)
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

RE: Excel Formula Help

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

(OP)
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

RE: Excel Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

(OP)
Hi Skip
Ah...Noted.
As usual, Thanks for your help 2thumbsup.

Regards,
arv

RE: Excel Formula Help

Hope it works for you.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula Help

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

RE: Excel Formula Help

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close