Detect if entries have duplicate criteria

**Philster** · 10-13-2010, 08:37 PM

Hi

I have two tables. The rows in the first table have a 1-to-many relationship with items in the second table.

I want a formula that I can copy down under column Ok-to-treat-like-app that will result in Y or N if, for all entries in second table, the values for VendorSupport & SupportedBy are the SAME for the same AppGroup. I want to be able to add to both tables as times goes on without modifying the formulas

In the case of BIEM above, it would be Y
In the case of Maximo above, it would be N

Can anybody assist? I checked the posts for something similar, but I haven't got it yet.

**Sofistikat** · 10-13-2010, 08:47 PM

Just to clarify, there are two instances of "IBM Support Jane" for Maximo, just like there are two instances of "MS Support Joe" for BIEM.

Shouldn't the "OK to treat like app?" column show "Y" for Maximo as well?

**Sofistikat** · 10-13-2010, 09:03 PM

Sorry, I didn't notice the workbook you'd attached. Please see attachment for one possible solution.

**Philster** · 10-13-2010, 09:32 PM

Originally Posted by Sofistikat

Sorry, I didn't notice the workbook you'd attached. Please see attachment for one possible solution.

Thank you. I see it, but I don't understand it.

I understand your column K. I did that too.
I understand column L. You test for the number of times the current criteria repeats. Although I I changed row 5's criteria to match, say row 1 & 2, it shows 3 in the final helper column. But no biggie...I continue
I don't understand column C. You search for the AppGroup in the entire table. But let's change it so that MS Support & Joe support one of the Maximo apps (see new attachmentt). It doesn't work.

However, you got me further than I was...I was trying to use the sumproduct logic, but couldn't get it work...any other ideas?

**Sofistikat** · 10-13-2010, 09:41 PM

Updated version attached.

**Philster** · 10-15-2010, 10:30 AM

Originally Posted by Sofistikat

Updated version attached.

I thought, great, until I began applying it. It tests if there is more than 1 occurrence, but there could that, but still not all the same. I have attached another example...showing that maximo app group has three apps in it, two of which now have the same criteria -- The OK-to-treat-like-app formula is now incorrectly Y.

If there was a way to compare the occurrence of the criteria (column L) to the number of items in the app group, that would do it.

I attach an example of the error.