Identify duplicates of two columns combined

[pratikkhanna]

Problem statement - There are multiple departments and a particular software can be used in more than one department. In this case S1 is being used by Dept A and Dept B. I need to identify such softwares. I can't use the inbuilt duplicate function because that will also highlight S1 version B also being used. Also it will highlight S3 as it's different versions are being used by the same department Dept B. So in this case it should just highlight S1 as it is being used by 2 different departments. The requirement is to find the same software being used by more than 1 department. Have tried Vlookup but no luck.[ATTACH]855598[/ATTACH

[6StringJazzer]

You didn't show an example of what you wanted for a result, so here is a formula that puts DUPLICATE in column D when both columns B and C together are duplicated.
In D2

Please Login or Register  to view this content.

copy down

Values as displayed

	A	B	C	D
1	Department	Software	Version
2	Dept A	S1	a	DUPLICATE
3	Dept B	S1	a	DUPLICATE
4	Dept C	S2	a
5	Dept A	S1	b
6	Dept B	S3	a
7	Dept C	S4	b
8
9

Underlying formulas

	A	B	C	D
1	Department	Software	Version
2	Dept A	S1	a	=IF(COUNTIFS($B:$B,$B2,$C:$C,$C2)>1,"DUPLICATE","")
3	Dept B	S1	a	=IF(COUNTIFS($B:$B,$B3,$C:$C,$C3)>1,"DUPLICATE","")
4	Dept C	S2	a	=IF(COUNTIFS($B:$B,$B4,$C:$C,$C4)>1,"DUPLICATE","")
5	Dept A	S1	b	=IF(COUNTIFS($B:$B,$B5,$C:$C,$C5)>1,"DUPLICATE","")
6	Dept B	S3	a	=IF(COUNTIFS($B:$B,$B6,$C:$C,$C6)>1,"DUPLICATE","")
7	Dept C	S4	b	=IF(COUNTIFS($B:$B,$B7,$C:$C,$C7)>1,"DUPLICATE","")
8				=IF(COUNTIFS($B:$B,$B8,$C:$C,$C8)>1,"DUPLICATE","")
9				=IF(COUNTIFS($B:$B,$B9,$C:$C,$C9)>1,"DUPLICATE","")

[pratikkhanna]

Yes the solve should be another column mentioning duplicates. But the version may not always be same in other department. I just reuploaded the XLS after making this change.

[6StringJazzer]

In the later version of the your file there are no duplicates, because the Version is different in all cases. If that is not correct, you need to explain in more detail what you are looking for. You should show desired results.

[pratikkhanna]

Thanks, I have uploaded the file again with the desired results and trying to summarize the requirement here -
1. Probably the word duplicate is interpreted differently so I have used the word Shared in my XLS
2. Since software S1 is shared between Dept A and B, that is what the 4th column should mark. This is irrespective of the version.
3. S3 also comes twice, but it's being used by the same Dept(different versions), so should not be marked.

[HansDouwe]

Please try in D2 and copy down:

Please Login or Register  to view this content.

[pratikkhanna]

Its should not show Shared in row number 6 as the Department is the same. Only for different Dept's it should mark it as Shared. If you have already marked Shared for Dept A - S1 combination (Any version), don't need to mark it again for a different version

[windknife]

Try this in D2 and copy down.

Please Login or Register  to view this content.

[HansDouwe]

I don't understand you.

S1 in row 6 is not only used by DEPT A,
S1 is also used by DEPT B.

See row 3.

[pratikkhanna]

hansDouwe : There are 2 instances of Dept A + S1 combination: Row 2 and row 6. Both of them should not show as Shared. Once Row2 tells me that this is shared with another Dept, in this case Dept B. I don' t need that count again in Row6.

[HansDouwe]

In that case try

Please Login or Register  to view this content.

[pratikkhanna]

This works in the example which I shared, but this actually needs to be applied to ~9000 rows. When I did that, it's returning blank for Shared rows. I debugged it further and pls see the attached error #N/A. This is how I modified the formula:

=IF(COUNTA(UNIQUE(FILTER($A$2:$A$8695,$F$2:$F$8695=F25)))>1,IF(MATCH(A25&F25,$A$2:$A$8695&$F$2:$F$8695,0)=ROW()-1,"Shared",""),"")

[HansDouwe]

About the error, please upload a sample file and show the error.

And does Post #11 the job?

[pratikkhanna]

Seems like that a data issue : This worked : =IF(COUNTA(UNIQUE(FILTER($A$2:$A$8695,$F$2:$F$8695=F25)))>1,IF(MATCH(A25&F25,$A$2:$A$8695&$F$2:$F$8695,0)=ROW()-1,"Shared",""),"")

Thanks windknife and HansDouwe!!

[windknife]

You are welcome.
--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.