Export to excel show unique values

[emanuelarcas]

Hello,

I have this list exported from SP and contains duplicated values. This list will always contain duplicated data every time is refreshed.
I need to find a solution to have the list with unique values even if it is refreshed.

The remove duplicates and data validation functions is not working for what I need.

Thanks for the help.

Export to excel show unique values

 

[NdNoviceHlp]

Hi emanuelarcas and Welcome to the Board. U didn't mention where your data is and what's supposed to happen when a duplicate is found. Dave

 

[emanuelarcas]

Hi Dave. Here it is what I need: https://imgur.com/a/IKC2r

I don't know how to upload here the file. Please let me know if you need more information.

Thanks for the help.

 

[NdNoviceHlp]

Where is the month input? So U want to choose a month(J), enter a name in E12 with name list in (K) and sum unique tickets(L) for the name. Output in F12. What is your sheet name and what row does the data start on? Dave

 

[Fazza]

Please go to the details of the query & add the word DISTINCT into the SQL.
Then everytime it refreshes you'll get the list you're after.

So change it from whatever it is now, like SELECT field names FROM table
to make it SELECT DISTINCT fields names FROM table

 

[emanuelarcas]

Hello. there is no SQL code. The file is an export from SP and I need to build this formula to have the number of QC per agent/month and related to unique ticket ID.

 

[emanuelarcas]

I have this formula that works fine to pull out the count of QC, but is not distinguishing the unique tickets IDs:
=IF(ISNUMBER(COUNTIF(Sheet3!V:V,"="&Months!A1)),COUNTIFS(Sheet3!W:W,"="&C2, Sheet3!N:N,"=Yes", Sheet3!V:V,"="&Months!A1,Sheet3!F:F,"=QC"),"")

where
V=source month
W=source userID
N=source valid for user
F=source escalation type

Here is a pic on how it looks https://pasteboard.co/H4xze5T.png

 

[emanuelarcas]

The Month input can be a separate sheet with all the moths. The month column format can be TEXT"mmmm yyyy", so is not necessary to by as shown in J.
The output would be like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]UserID[/TD]
[TD]Month[/TD]
[TD]Unique QC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]January[/TD]
[TD]here is the formula to count the number of QCs with unique ticket number and related to January.[/TD]
[/TR]
[TR]
[TD]akolasa[/TD]
[TD]February[/TD]
[TD]here the same for Feb.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]March[/TD]
[TD]here same for Mar.[/TD]
[/TR]
</tbody>[/TABLE]

So the Sheet1 would have the above setup and the data will be pulled out from Sheet2 where is the data like in this image: https://pasteboard.co/H4xrxoi.png
I added an additional condition the result have to have "Yes" for the column Valid for userID as shown in column E from the picture.

The sheet is an export from a SP list which is updated with new data every day. The new data is refreshed in this export.

I hope I answered all your questions.
Thank you again for all your trouble.

 

[NdNoviceHlp]

Are U wanting a formula solution or can it be VBA. Dave

 

[emanuelarcas]

The formula would be the best as the file will be uploaded on the SharePoint and the VBA is not working online (so I understand). Emanuel