Using countif for unique values says I have duplicates

[beastiaan]

Hey everybody,

So I am making an interactive table and list to be able to get data on a production process. I work in an injection moulding company as a mechanical engineering intern and my assignment is to get insight into our production process.

Right now I have created two tables, one with columns: Product ID, date, measurement no., weight.
The other table has columns: measure no., product id, date and shotweight.

The second table is created entirely from data of the first table. The reason for the measurement number to have its own column is to be able to recognize a shot coming out of a machine. (a shot is the output of a moulding machine of 1 cycle, one cycle can produce more than 1 product. So a shotweight is the sum of all weights under 1 measurement no.). In table 1 the measurement numbers identify the shot. So you could have no. 1 four times, where product number is the same, date the same and weight of 4 units of the same product.

To be able to analyze the results I am using pivot tables. I want to be able to insert 1 single slicer to sort both my pivot tables of shotweights and product weights. The slicer is supposed to filter by product no. The problem is that in both tables the product no. contains duplicates since they are actual measurements and a product can be measured several times. Therefore I created a third table only containing unique product numbers. I made this table using a countif function ***(=IFERROR(INDEX(Weegdata[Product ID];MATCH(0;INDEX(COUNTIF($Y$1:Y1;Weegdata[Product ID]););0));"")***. I used this instead of a unique function, because the unique function can't be made into a table. After creating the table I tried to make a connection between the two tables with weights and the table with unique product numbers. Then i got an error that the connection could not be made because of duplicates in the product number table.

I have no idea how to fix this problem and to be able to sort the pivot tables of both my weight data by product number. I think I could be able to fix the problem if the data wouldn't change anymore, then I could just use the remove duplicates option. Unfortunately data is added everyday and new products can be added as well. Any tips on how to be able to sort both my weight pivot table and my shotweight pivot table by the same slicer of product number would be greatly appreciated.

EDIT: I added an attachment to this post with a sample of the excel sheet I am working on. In the sheet some extra explanation on my issue can be read.

Thanks :)

Bastiaan

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[beastiaan]

I solved the issue by using a list of all product numbers that are known within our company, unfortunately it will have to be updated once a new product enters the list of all products. I realised I could not get all the unique numbers for a slicer out of my weight data, because I am collecting data on cycle times and production output as well. Sometimes data is missing there and only using the unique product numbers in my weight data was not sufficient.