Home

Help w/ "Remove Duplicates" function on the "Data" tab

%3CLINGO-SUB%20id%3D%22lingo-sub-847653%22%20slang%3D%22en-US%22%3EHelp%20w%2F%20%22Remove%20Duplicates%22%20function%20on%20the%20%22Data%22%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847653%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20am%20working%20with%20a%20large%20data%20set%20(12%20columns%20with%2045%2C000%20rows%20of%20entries)%20which%20is%20updated%20each%20day%20which%20in%20turn%20auto-updates%20a%20dashboard%20(within%20the%20document%20on%20another%20sheet)%20I%20have%20created%20to%20analyze%20this%20data.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ECurrently%2C%20I%20am%20using%20the%20%22remove%20duplicates%22%20function%20to%20delete%20duplicates%20between%20three%20columns%20of%20my%20data%20set.%20I%20have%20attached%20a%20screenshot%20of%20the%20function%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20432px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131023i85450EA23AEE454B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excel%20help.png%22%20title%3D%22excel%20help.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20would%20be%20fine%2C%20however%20this%20function%20does%20not%20work%20with%20the%20new%20data%20that%20is%20added%20each%20day%20and%20I%20must%20I%20manually%20perform%20this%20function.%20Is%20there%20a%20way%20create%20a%20rule%20so%20that%20my%20sheet%20will%20always%20delete%20the%20duplicates%20between%20these%20three%20columns%20even%20when%20additional%20data%20is%20added%3F%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you%20for%20the%20help!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ENote%3A%20I%20am%20also%20using%20%3Dcountifs%20%26amp%3B%20%3Dsumifs%20in%20my%20%22dashboard%22%20table%20to%20count%2Fsum%20things%20within%20a%20certain%20date%20range.%20If%20I%20can%20add%20something%20to%20my%20formulas%20to%20ALSO%20exclude%20duplicates%20with%20three%20criteria%2C%20this%20would%20be%20a%20good%20solution%20as%20well.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20apologize%20if%20this%20doesn't%20make%20sense%2C%20I%20would%20be%20happy%20to%20clarify!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-847653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848183%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20w%2F%20%22Remove%20Duplicates%22%20function%20on%20the%20%22Data%22%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848183%22%20slang%3D%22en-US%22%3EYou%20could%20use%20the%20Data%2C%20From%20Table%20button%20to%20remove%20the%20duplicates.%20Just%20control%2Bclick%20the%20three%20columns%20%2C%20click%20the%20Remove%20Rows%20drop-down%20and%20then%20select%20%22Remove%20Duplicates%22.%20AFter%20that%20click%20the%20%22Close%20%26amp%3B%20Load...%22%20drop-down%20and%20choose%20%22Close%20%26amp%3B%20Load%20to...%22%20and%20set%20it%20to%20return%20a%20table%20on%20a%20new%20worksheet.%20Now%20make%20sure%20your%20dashboard%20formulas%20use%20this%20new%20table%20instead%20of%20the%20original.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848891%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20w%2F%20%22Remove%20Duplicates%22%20function%20on%20the%20%22Data%22%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3Bok%20great!%20I%20will%20give%20this%20a%20try.%20And%20this%20should%20be%20applied%20automatically%20to%20any%20additional%20data%20entered%20into%20the%20table%3F%20I%20really%20appreciate%20the%20response!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848913%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20w%2F%20%22Remove%20Duplicates%22%20function%20on%20the%20%22Data%22%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848913%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E!%20I%20do%20have%20a%20follow%20up%20question%3A%20is%20there%20a%20reason%20that%20I%20cannot%2Fshould%20not%20close%20and%20load%20to%20the%20default%20sheet%3F%20Will%20that%20impact%20any%20additional%20data%20entered%3F%26nbsp%3B%20I%20will%20definitely%20play%20around%20with%20things%20though...%20I%20can't%20believe%20I%20have%20never%20seen%20this%20function%20through%20all%20of%20the%20excel%20classes%20I%20took%20in%20college!%20I'm%20very%20excited%20to%20learn%20more%20and%20am%20so%20grateful%20you%20pointed%20me%20in%20this%20direction!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-848979%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20w%2F%20%22Remove%20Duplicates%22%20function%20on%20the%20%22Data%22%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-848979%22%20slang%3D%22en-US%22%3EYou%20can%20place%20the%20table%20anywhere%20as%20long%20as%20you%20don't%20try%20to%20place%20it%20on%20the%20source%20table.%20Also%20there%20must%20be%20sufficient%20room%20for%20the%20resulting%20table%20of%20course.%3CBR%20%2F%3E%3CBR%20%2F%3ETip%3A%20if%20you%20can't%20figure%20out%20how%20to%20do%20something%20in%20that%20new%20interface%2C%20remember%20to%20include%20%22Power%20Query%22%20in%20your%20Google%20search.%3C%2FLINGO-BODY%3E
New Contributor

I am working with a large data set (12 columns with 45,000 rows of entries) which is updated each day which in turn auto-updates a dashboard (within the document on another sheet) I have created to analyze this data.

Currently, I am using the "remove duplicates" function to delete duplicates between three columns of my data set. I have attached a screenshot of the function:excel help.png

 

This would be fine, however this function does not work with the new data that is added each day and I must I manually perform this function. Is there a way create a rule so that my sheet will always delete the duplicates between these three columns even when additional data is added?

Thank you for the help!

Note: I am also using =countifs & =sumifs in my "dashboard" table to count/sum things within a certain date range. If I can add something to my formulas to ALSO exclude duplicates with three criteria, this would be a good solution as well. 

I apologize if this doesn't make sense, I would be happy to clarify!

4 Replies
Highlighted
You could use the Data, From Table button to remove the duplicates. Just control+click the three columns , click the Remove Rows drop-down and then select "Remove Duplicates". AFter that click the "Close & Load..." drop-down and choose "Close & Load to..." and set it to return a table on a new worksheet. Now make sure your dashboard formulas use this new table instead of the original.
Highlighted

@Jan Karel Pieterse ok great! I will give this a try. And this should be applied automatically to any additional data entered into the table? I really appreciate the response! 

Highlighted

Hello @Jan Karel Pieterse! I do have a follow up question: is there a reason that I cannot/should not close and load to the default sheet? Will that impact any additional data entered?  I will definitely play around with things though... I can't believe I have never seen this function through all of the excel classes I took in college! I'm very excited to learn more and am so grateful you pointed me in this direction!

Highlighted
You can place the table anywhere as long as you don't try to place it on the source table. Also there must be sufficient room for the resulting table of course.

Tip: if you can't figure out how to do something in that new interface, remember to include "Power Query" in your Google search.
Related Conversations
Need some help figuring out edge://sync-internals/
HotCakeX in Discussions on
0 Replies
WVD On/Off based on activity
Adam Black in Windows Virtual Desktop on
1 Replies
Notifications from Planner in Teams
nwehl in Microsoft Teams on
0 Replies
What data does Microsoft collect?
Tim_Gent in Microsoft Teams on
1 Replies