• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fix my formula please

P

Patrick

New Member
Hello excel masters,

In the attached file on the sheet "~Magic" I have a formula that find if two cells have a duplicate pair.

=IF(COUNTIFS(Revised[Address],Revised[@Address],Revised[Unit],Revised[@Unit])>1,"Duplicate", "")

My issue is, when I have an address, but no unit(empty cell) it will not detect duplicate addresses.

Thank you in advance.
 

Attachments

Last edited:
Luke M said:
Hi Patrick,
What exactly is your overall goal? You're using the @ to reference the row in table at same row as formula, but the Magic table is much longer. Do we just need to know if there are any duplicates, or do you want to highlight specific duplicates?

You might also check out some of the similar threads about counting duplicates:
http://chandoo.org/forum/search/150072/?q=multiple columns duplicate&o=date
Click to expand...

Hi Luke,

I'm glad you are able to help me again http://forum.chandoo.org/threads/check-cell-range.12782/

The magic table is much longer because the size of the revised table is unknown and varying. This way anyone adding values to Revised doesn't have to worry about extending Magic. Unless you know of a way to link the size of the Magic table and Revised table?

Half way threw writing this response your presence was enough to help me find a solution to my problem. However I do have 2 other questions. :)

1. On the table in the Revised sheet, when the cursor is placed at the top left corner of the header I am un able to quick select(the black arrow) the entire table or its individual columns. It works for all other tables, any idea why?

2. In the new attached I am working on a formula in Magic "Wrong Range". My end goal; look at row 11 on the Revised page. The same AP# should have matching Lo & Hi PR values. If AP#=1; then LoPR=10; HiPR=20. I would like to highlight all AP# cells that do not meet this requirement.

Thank you so much!
 

Attachments

You must log in or register to reply here.
Back
Top