Help on Conditional formatting of duplicates in Pivot table with multiple criteria

[rajcobra]

Dear Friends,

I have a workbook with huge data set and a Pivot table, and need to highlight the duplicates in column B based on criteria in Column A.

Kindly help me to create a formula for the same.

In below sample:

Need to highlight duplicates in Column B at every project level in Column A.

Project	Res+Sec	Resource	Ref #	Open
AAA	11030660010002	CRUSHED SAND	TIP114HMR1700224	10050
	11030660010002	CRUSHED SAND	TIP114HMR1700725	1359.47
	12010020340047	UPVC COUPLER WITH GASKET 6" BS EN 1401 SN4	TIP114HMR1700638	899
	12010020340047	UPVC COUPLER WITH GASKET 6" BS EN 1401 SN4	TIP114HMR1700688	564
	19050020040003.1000	A/C BLOWER, JCB 332/C4284, BACKHOE LOADER	001/02HMR1700115	5
	19050020040038.1000	EXPANSION VALVE, JCB 923/10098, BACKHOE LOADER	001/02HMR1700115	5
	19050020040076.1000	RECEIVER DRIER, JCB 30/926117, BACKHOE LOADER	001/02HMR1700115	5
	12050020110001.1004	WIRE NAIL 3"	TIP114HMR1701007	13
	19020030010085	GI SHEET	TIP114HMR1800260	8
BBB	11030660010002	CRUSHED SAND	TIP114HMR1700224	10050
	11030660010002	CRUSHED SAND	TIP114HMR1700725	1359.47
	12010020340047	UPVC COUPLER WITH GASKET 6" BS EN 1401 SN4	TIP114HMR1700638	899
	12010020340047	UPVC COUPLER WITH GASKET 6" BS EN 1401 SN4	TIP114HMR1700688	564
	11030850010020	BITUMASTIC SEALANT	TIP114HMR1700486	25
	12010020340047	UPVC COUPLER WITH GASKET 6" BS EN 1401 SN4	TIP114HMR1700638	899
	12050020110001.1004	WIRE NAIL 3"	TIP114HMR1701007	13
	19020030010085	GI SHEET	TIP114HMR1800260	8

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Regards,
Ross

 

[mole999]

A quick way until you get something more regular
assuming Res+Sec is Col B and Resource is Col C then in col G
=TRIM(UPPER(B2))&TRIM(UPPER(C2))
extend down
then highlight column, use conditional format and highlight exact duplicates.

that reveals the last three are also duplicates

 

[rajcobra]

Thank you, but this will not help...

As I need the formula to check for every project in column A.

 

[mole999]

you only have two projects, and only reference them once each

 

[rajcobra]

I have 180 projects in my worksheet and I need to identify the duplicate Res+sec within each Project and highlight them.