Only get one value from a list of duplicates

[Takes2ToTango]

Hi All,

I need help with a formula.

I have attached a screenshot below. I have a list of data where the order number is the same in multiple rows. When I pivot table, it will sum up all Qty with the same order number. I am trying to get it so it only picks up the first one and then ignores the rest. I should end up with the 4th column with only one singular '20' in and the rest blank. I hope this explains it enough!

 

[ExcelToDAX]

Assuming your table is called Table1, you can do the following:

In a new sheet called Sheet3, in cell H2, enter the formula:

=UNIQUE(Table1[#All])

In Name Manager, we can create a table called DistinctTable and set it to Sheet3!$H$2# like the screenshot below


Now we can create a pivot table and reference the DistinctTable named range.

 

[Takes2ToTango]

Many thanks.

I have done that and it has worked great!

Is there a way to attach that non-duplicated information to another pivot table I have? I need the sum of all qty done in the week number.

 

[ExcelToDAX]

You should be able to just reference the DistinctTable in any of those pivot tables. I might be confused by your question. Can you explain a little more what you are trying to accomplish?