Not sure what I'm doing wrong here...

**machinegod** · 10-22-2019, 01:00 PM

I'm slightly modifying a another function I found online for a table of values. It's purpose is to detect duplicate pairing of the same name from a roster of about 20 persons. The desired result would be 20 unique pairs.

Original Function =IF(A1=F1, "DUPLICATE!", "GOOD!") This checks if a person is paired up with themselves. For example, if Wolverine=Wolverine, then "DUPLICATE!" is indicated in a separate column.
The value for cell A1 is a fixed number, and the value for cell F1 is a randomly generated number using the RANK function that references a RANDBETWEEN function in a separate column.
The fixed number from A1 is associated with a person's name in B1, and the random number from F1 is associated with another person's name in C1 which has a VLOOKUP function.

This works perfectly.

However, I'm trying to modify the function by also checking if a pair of two different people shows up again together in a different table. For example, if Wolverine and Jean were paired up in one table, I don't want them to be paired up in a separate table.

I tried this, =IF(A1=F1, "DUPLICATE!",IF(B1:C1=B29:C29,"DUPLICATE!","GOOD!"))

For some reason the parentheses for the second if function are red, and when it runs the entire function I keep getting a "#VALUE" within a cell of the "Duplicate Detector" column.

I can't find the website I got the instructions from. I'll try to find it for further clarification.

**Fluff13** · 10-22-2019, 01:08 PM

How about
=IF(A1=F1, "DUPLICATE!",IF(B1&"|"&C1=B29&"|"&C29,"DUPLICATE!","GOOD!"))

**machinegod** · 10-22-2019, 01:29 PM

OMG! That worked! Thank you!

**Fluff13** · 10-22-2019, 01:32 PM

You're welcome & thanks for the feedback

**machinegod** · 10-22-2019, 02:31 PM

Originally Posted by Fluff13

You're welcome & thanks for the feedback

You're welcome!

Another thing...

Is there a way to avoid writing the formula over and over again to reference each pairing?Screenshot of Worksheet.jpg

**Fluff13** · 10-22-2019, 02:35 PM

Very probably, but can you please supply a sample workbook, I can hardly read the image.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**machinegod** · 10-22-2019, 02:48 PM

Okay. I attached the sample.

All the names in the worksheet are either Marvel or DC character names.

**63falcondude** · 10-22-2019, 02:54 PM

You can use this in D29:

=IF(OR(B29=C29,COUNTIFS(StudentTable2[Reviewer],B29,StudentTable2[Reviewee],C29)),"DUPLICATE!","GOOD!")

**Fluff13** · 10-22-2019, 03:02 PM

How about in D3
=IF(OR(B3=C3,COUNTIFS(B:B,B3,C:C,C3)>1),"Duplicate","Good")

then copy paste the formula into the 1st row in each table

**machinegod** · 10-23-2019, 11:01 AM

Originally Posted by Fluff13

How about in D3
=IF(OR(B3=C3,COUNTIFS(B:B,B3,C:C,C3)>1),"Duplicate","Good")

then copy paste the formula into the 1st row in each table

Didn't work. Now it indicates pairing of the same name as "Good", instead of duplicate.

**machinegod** · 10-23-2019, 11:06 AM

Originally Posted by 63falcondude

You can use this in D29:

=IF(OR(B29=C29,COUNTIFS(StudentTable2[Reviewer],B29,StudentTable2[Reviewee],C29)),"DUPLICATE!","GOOD!")

This doesn't run through the entire sheet, but I may be able to modify the formula. Thank you.

**machinegod** · 10-23-2019, 11:30 AM

Originally Posted by machinegod

Didn't work. Now it indicates pairing of the same name as "Good", instead of duplicate.

I think I fixed it with this...
=IF(OR(B3=C3,COUNTIFS(B:B,B3,C:C,C3)>1),"Duplicate!",IF(B29=C29,"Duplicate!","Good!"))

I'll see if it works out.

**AliGW** · 10-23-2019, 11:41 AM

Administrative Note:

Please update your user profile MS Office verion - please be explicit, as there are various 'current' versions that are subtly different (e.g. Office 2019 standalone and Office 365 subscription). Your helpers will benefit from knowing exactly which you are using.

**63falcondude** · 10-23-2019, 11:50 AM

Originally Posted by machinegod

This doesn't run through the entire sheet, but I may be able to modify the formula. Thank you.

Thanks for the rep!

The formula that I offered gave you the same exact result as the one that you already had in there.
I thought that's what you were after.

**AliGW** · 10-23-2019, 11:54 AM

Thank you for updating your profile. However, you appear to have given me negative rep for asking you to do so. Might I know why you felt this deserved a 'slap'? Or have you clicked the wrong button and meant to say 'thanks'?

**machinegod** · 10-23-2019, 11:56 AM

For some reason when I use this formula, it labels a pair as a duplicate even though it isn't.

=IF(OR(B3=C3,COUNTIFS(B:B,B3,C:C,C3)>1),"Duplicate!",IF(B29=C29,"Duplicate!","Good!"))

Then when I try adding more criteria like the function below for the third table, it returns a "#VALUE" within every row of the third table.

=IF(OR(B29=C29,COUNTIFS(StudentTable2[Reviewer],B45,StudentTable2[Reviewee],C45,LabATable[Reviewer (2nd Set)],B45,LabATable[Reviewee (2nd Set)],C45)),"DUPLICATE!","GOOD!")

**Glenn Kennedy** · 10-23-2019, 12:02 PM

Since the thread is getting a bit long.... can we see a fresah attachment showing the latest position... and your latest requirement?

**machinegod** · 10-23-2019, 12:02 PM

Originally Posted by 63falcondude

Thanks for the rep!

The formula that I offered gave you the same exact result as the one that you already had in there.
I thought that's what you were after.

I had noted within the sheet provided that I wanted all the tables to be checked for duplicates.

**machinegod** · 10-23-2019, 12:06 PM

Originally Posted by Glenn Kennedy

Since the thread is getting a bit long.... can we see a fresah attachment showing the latest position... and your latest requirement?

Okay. Give me some time to upload the two different versions (with the separate formulas from Fluffly and Falcon) that I'm trying to figure out.

**Fluff13** · 10-23-2019, 12:09 PM

Using this formula
=IF(OR(B3=C3,COUNTIFS(B:B,B3,C:C,C3)>1),"Duplicate","Good")
It indicates all dupes regardless of table.

**Glenn Kennedy** · 10-23-2019, 12:10 PM

... unless of course Fluff 13 has just nailed it...

**machinegod** · 10-23-2019, 12:16 PM

Here they are.

Thanks for the help, everyone.

**machinegod** · 10-23-2019, 12:16 PM

"Using this formula
=IF(OR(B3=C3,COUNTIFS(B:B,B3,C:C,C3)>1),"Duplicate","Good")
It indicates all dupes regardless of table."

Thanks. I'll check it out.

**Glenn Kennedy** · 10-23-2019, 12:17 PM

I'll sit on my hands for now...

**machinegod** · 10-23-2019, 12:28 PM

Originally Posted by Fluff13

Using this formula
=IF(OR(B3=C3,COUNTIFS(B:B,B3,C:C,C3)>1),"Duplicate","Good")
It indicates all dupes regardless of table.

I think this is good! Thank you so much everyone for your help!

**AliGW** · 10-23-2019, 12:40 PM

At the risk of earning more negative rep, if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**rorya** · 10-23-2019, 12:44 PM

And as for that title...

**AliGW** · 10-23-2019, 12:48 PM

Yep!

**Fluff13** · 10-23-2019, 01:10 PM

Originally Posted by machinegod

I think this is good! Thank you so much everyone for your help!

You're welcome & thanks for the feedback

Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Re: Not sure what I'm doing wrong here...

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro exporting and saving the wrong page with wrong name

[SOLVED] VBA, IFs went wrong

[SOLVED] Wrong formula or wrong function?

Bookmarks

Bookmarks

Posting Permissions