List out duplicate items in a list with different costs/values

[H3MSY]

Hi all, I've used the search function but i cant find an answer , hopefully this is a simple one.

I work on collating costs and prices for projects made up of thousands of items. The problem i come up against is different costs are given to me by different groups feeding into me for the same part number.

For ease of explanation, lets say I have two columns on my main data input sheet, a list of part numbers with a cost alongside. (Column A & B)

Is there a way (pivot table? / Formula? on a separate tab), that if there are duplicate part numbers with different a cost to list that part number and the different costs that are against that part number so i can then go into the list on the data tab filter by that part number and change the wrong cost.

Thanks in advance for any help.

 

[ExcelToDAX]

You could add two columns:

Cell C1 should be:

=INDEX(UNIQUE(A:B),,1)

And cell D1 should be:

=LET(tbl,CHOOSE({1,2},UNIQUE(A:A),COUNTIF(C1#,UNIQUE(A:A))),INDEX(FILTER(tbl,INDEX(tbl,,2)=2),,1))

 

[ED38]

Hi H3MSY,
I suggest you create :
1. a 1st column dedicated to the part number (using left function potentially combined with search/find if there is a specific sign between the two;
2. a 2nd column counting the occurrence of each sku
3. filter all our columns including these 2 above
4. select the occurrences above 1
5. The list of part number with multiple values now appears so you can have a look and make a decision on the one to delete

 

[DanteAmor]

Try:

varios 24sep2024.xlsm
	A	B	C
1	Part	Price	Dup
2	AAA123	23	Dup
3	BB3456	34	Dup
4	CC7890	45
5	AAA123	23	Dup
6	BB3456	34	Dup
7	CC7890	45
8	AAA123	67	Dup
9	BB3456	78	Dup
10	CC7890	45
Hoja2

Cell Formulas
Range	Formula
C2:C10	C2	=IF(COUNTIFS(A:A,A2,B:B,"<>"&B2),"Dup","")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Hoja2!$A$1:$C$10	C2:C10

After filtering column C by the values equal to "Dup", and you will have the parts with different prices

 

[DanteAmor]

Or you can use the following to obtain duplicates with a different price, formula in E2.

=LET(a,A2:A10,b,B2:B10,c,A2:B10,SORT(IF(BYROW(c,LAMBDA(br,COUNTIFS(a,CHOOSECOLS(br,1),b,"<>"&CHOOSECOLS(br,2))))>0,c,""),1,-1))

Ex:

 

[H3MSY]

Try:

varios 24sep2024.xlsm
	A	B	C
1	Part	Price	Dup
2	AAA123	23	Dup
3	BB3456	34	Dup
4	CC7890	45
5	AAA123	23	Dup
6	BB3456	34	Dup
7	CC7890	45
8	AAA123	67	Dup
9	BB3456	78	Dup
10	CC7890	45
Hoja2

Cell Formulas
Range	Formula
C2:C10	C2	=IF(COUNTIFS(A:A,A2,B:B,"<>"&B2),"Dup","")

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Hoja2!$A$1:$C$10	C2:C10

After filtering column C by the values equal to "Dup", and you will have the parts with different prices

Many thanks for this method.. filtering by "dup" is an option but my datasheet is maybe 50 columns wide so filtering can get cumbersome. What i would like is a simple table on another tab that would just summarise any duplicates like this...

 

[H3MSY]

Or you can use the following to obtain duplicates with a different price, formula in E2.

=LET(a,A2:A10,b,B2:B10,c,A2:B10,SORT(IF(BYROW(c,LAMBDA(br,COUNTIFS(a,CHOOSECOLS(br,1),b,"<>"&CHOOSECOLS(br,2))))>0,c,""),1,-1))

Ex:
View attachment 117251

that could work!!! ignore my reply to your initial post, I think our posts crossed! I'll give this a try now. Thanks for your help