Removing duplicate data

[vicktan]

Hi all, 

Pain point: Every month, I am required to manually copy and paste some data from each class list in an Excel file into the consolidated list in another Excel file and am have found a way to automate this process by using Power Automate. 

My Power Automate workflow looks something like this: 

Recurrence: 1 month 

List rows present in a table (Excel online): I have linked my individual class list here 

Condition: (value in excel) does not contain (null)

        If yes: Add a row into a table: I have linked my consolidated class list here and matched the data accordingly to the individual class list. 

However, here comes the issue: 

Since this flow takes place every month, I see that there are some duplicate data in my consolidated sheet which comes about from prior data in the individual class sheet. Since the process recurs every month, existing data which have already been pasted in the consolidated sheet in the prior month, would be copied and pasted again in the next month. 

By any chance, does anyone have a solution for this? Thank you! 

In case you would like to view my data sets, they look something like this:

For Consolidated Sheet:

Register number	Full Name	First Name	Last Name	Class	Test 1	Test 2	Test 3
1	Amanda Seah	Amanda	Seah	1A	94	12	43
2	Allen Chua	Allen	Chua	1A	32	32	21
3	Alan Lee	Alan	Lee	1A	57	62	83
4	Alicia Teo	Alicia	Teo	1C	83	54	72
5	Aidan Woo	Aidan	Woo	1C	28	58	92
6	Apple Chew	Apple	Chew	1D	94	20	54
7	Aloysious Chew	Aloysious	Chew	1D	43	23	29
10	Benjamin Ong (pasted from class 1B list from last month)	Benjamin	Ong	1B	53	23	30
11	Bennett Chua (pasted from class 1B list from last month)	Bennett	Chua	1B	42	43	28

For 1B Class list sheet (that should be updated in the consolidated file):

Register number	Full Name	First Name	Last Name	Class	Test 1	Test 2	Test 3
10	Benjamin Ong	Benjamin	Ong	1B	53	23	30
11	Bennett Chua	Bennett	Chua	1B	42	43	28
12	Brenda Tan	Brenda	Tan	1B	43	23	32
13	Boon Ang	Boon	Ang	1B	92	54	43
14	Berry Ho	Berry	Ho	1B	83	65	12

[yashag2255]

Hi @vicktan 

You can use arrays to compare if data already exists. A sample implementation of this for Dynamics 365 is shown here: https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Delete-duplicate-records-items-fro...

You can replicate similarly for Excel. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[sumurthy]

Using Run Script action from Excel online connector would be the right approach, IMO. Since the input and output consists of Excel files, you'll need flexibility around adding adding new data, modify logic, format data, etc. Office Scripts allows you to write all your logic in the form of a script (TypeScript) and run that within Power Automate. 

See here for more details: https://docs.microsoft.com/office/dev/scripts/develop/power-automate-integration

[vicktan]

Hi @yashag2255 , 

Thank you for your reply. Was looking through the post directed by you and it seems like the duplicates were called out manually. Is there any way that I can do so by having everything being automated (ie I do not have to find out which of the data were duplicated)? 

Thank you so much and I look forward to your reply!