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
toriana
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
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.

@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! 

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!

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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies