Excel 2007 Multiple Column Conditional Vlookup, I think?

[Steven Higgins]

I have a large Workbook with 3 Columns.

Example:
Computer Name ProdName Version
AA746437 Flash Player ActiveX 10.3.183.10
AA746437 Flash Player Plugin 11.4.402.265
AA746437 Flash Player Plugin 10.3.183.10
AA746437 Flash Player ActiveX 11.4.402.265
AA746437 Acrobat Reader 9.5.2.295
AA746437 Acrobat 9.4.6.252 Standard
AA746437 Acrobat Distiller 9.4.0.195
AA746437 Acrobat Reader 9.5.2.295

I need either a VLookup function or VBA Script to give me the rows that have for each Computer Name with a Duplicate ProdName and Duplicate Version to either Highlight that line or give me a list of those records/rows only.

Hope that is enough info for an answer.

Thanks in advance for any assistance.

Steven Higgins

[FDibbins]

Assuming you're data is in columns A, B and C, i created a helper column in D with this...
=B1&C1

then I highlighted the new range and used conditional formatting "use formula" and entered this...
=COUNTIF($D$1:$D$8,D1)>1

Alternatively, you could use this formula, and then filter on the results...
=IF(COUNTIF($D$1:$D$8,D1)>1,"Duplicate","")

[Steven Higgins]

The idea of the helper Column lead us to a workable solution.

First we sorted each Column of A, C, and E in Ascending order Starting with A then C then E. That way the Computer Name got sorted first then by the ProdName and finally by the Version.

We Added a Column in between each existing column as our 'helper' Columns.
In the Helper Columns we used a formula that one of my colleages came up with of =IF(A3=A2, TRUE, FALSE) starting in cell B3.

This put a TRUE in the Helper Column if the Column to the left in the same row matched the value in the row above its value. Thus checking for duplication.

We found when All the helper Columns were True those were our duplicates that needed to be highlighted. A sort in each column to show only the True values narrowed the search down tremedously.

Apologize for not replying sooner... But Thanks for the initial idea of adding helper Columns that lead us to our solution.


Thanks,
Steven Higgins