Find rows with same ID but different value in another column

[swapnilk]

Hi,

I have a table like this:

Name	Group
1	A
2	B
3	B
5	D
1	A
10	D
8	F
2	A

Result

Name	Group
2	B
2	A

I would like to select the Name that occurs more than once with different Group.

I am currently using Pivot Table to get the result by adding Name in "Rows" and Group in "Columns" and "Values" and then i manually go through the list to find Name with multiple different Groups.

Can someone please suggest a way to achieve the same result as above, it can be formula, VBA or anything that will make the work faster. Since, now after using Pivot Table i have to manually go through the list to find Name with different Groups, sometimes the data is around 300000 rows.

 

[RasGhul]

Can you confirm what version you have? Assuming you don't have 365 maybe this - VBA maybe a better solution as I'm not how this solution will fair with 300000 rows...

You will need to drag this down far enough to return rows count greater than True in H2;

Find rows with same ID but different value in another column_swapnilk.xlsx
	A	B	C	D	E	F	G	H
1	Name	Group	COUNT	concat		extract	Unique	TRUE
2	1	A	1	1A		1A	FALSE	2
3	2	B	1	2B		2B	TRUE
4	3	B		3B		1A	FALSE
5	5	D		5D		2A	TRUE
6	1	A	1	1A		1	FALSE
7	10	D		10D		1	FALSE
8	8	F		8F		1	FALSE
9	2	A	1	2A		1	FALSE
10
11	Result
12	Name	Group
13	2	B
14	2	A
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNTIF([Name],[@Name])=2,1,0)
D2:D9	D2	=[@Name]&[@Group]
F2:F9	F2	=IF(ROWS($F$2:F2)>COUNTIF(Table1[COUNT],1),1,INDEX(Table1[concat],SMALL(IF(Table1[COUNT]=1,ROW(Table1[Name])-ROW(Table1[[#Headers],[Name]])),ROWS($F$2:F2))))
G2:G9	G2	=COUNTIF($F$2:$F$9,F2)=1
H2	H2	=COUNTIF(Unique,TRUE)
A13:A14	A13	=IFNA(INDEX(Table1[Name],MATCH(IF(ROWS(A$13:A13)>$H$2,"",INDEX(extract,SMALL(IF(Unique=TRUE,ROW(extract)-ROW($F$1)),ROWS(A$13:A13)))),Table1[concat],0)),"")
B13:B14	B13	=IFNA(INDEX(Table1[Group],MATCH(IF(ROWS(A$13:A13)>$H$2,"",INDEX(extract,SMALL(IF(Unique=TRUE,ROW(extract)-ROW($F$1)),ROWS(A$13:A13)))),Table1[concat],0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
extract	=Sheet1!$F$2:INDEX(Sheet1!$F$2:$F$300000,COUNTA(Sheet1!$F$2:$F$300000))	F2:G9, A13:B14
Unique	=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$300000,COUNTA(Sheet1!$G$2:$G$300000))	H2, A13:B14

 

[swapnilk]

Can you confirm what version you have? Assuming you don't have 365 maybe this - VBA maybe a better solution as I'm not how this solution will fair with 300000 rows...

You will need to drag this down far enough to return rows count greater than True in H2;

Thank you for the reply. I have access to 365.

I found an alternate way. Use

=TRANSPOSE(FILTER(A:A, B2=B:B))

to get all Group Names and then use

=IF(COUNTA(C2:I2)-COUNTIF(C2:I2,LOOKUP(2,1/(C2:I2<>""),C2:I2))=0,"OK","DIFF")

to get Names with different Groups, then use filter to show only DIFF.

 

[Peter_SSs]

I have access to 365.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)