How to check if all values are same

[HaniroothC]

Hello forum,

I need help with the below scenario. I have part numbers with varying 6 digit codes. I need to check if the part numbers has same six digits or if there is a difference. Attached a sample. I have a file with 100k lines. Can someone please assist me if there is a easier way to find it.

Appreciate your response. Thanks!

[#2]

@HaniroothC I would create a pivot table with Part Number and 6 Digit in the Row area. See attached.

[#3]

Hi Riny, thanks for the response. I tried using pivot table, but since the records I have is large using a pivot table becomes difficult. Is there a possible solution where I can only get the part numbers that have different 6 digits? A logical formula or something?

[#4 · Accepted solution]

@HaniroothC Perhaps not the prettiest solution but if you change the layout of the PT a bit and then create a unique list of part numbers, you can use COUNTIF to count the number of times a part number occurs in the PT. Filter out the ones with a count of 1 and you are left with the ones you need to look into.

Or perhaps with Power Query, but since you are on a Mac, you need to have a current MS365 subscription.

[#5]

@Riny_van_Eekelen Thanks so much for the response. It works! :)

[#6]

@HaniroothC 

This provides a somewhat devious approach using the 365 FILTER function

= LET(
      distinctParts, UNIQUE(table),
      uniqueParts,   UNIQUE(table,,TRUE),
      multipleOccur, UNIQUE(VSTACK(distinctParts, uniqueParts),,TRUE),
      multipleOccur
  )

The key line stacks a list of distinct parts with a second list containing those parts that occur only once.  In the new list the combinations that originally occurred once are now duplicated.  Removing them returns the desired list.