Dynamic remove duplicates

[ashishkhurana93]

I want to remove duplicates from column A dynamically using some formula .
Input can have any row deletion or insertion.So output should dynamically adjust to it.
Is it possible using formula?


Please find attached excel file with the input and desired output

[sandy666]

I don't know about formula but you can do that with PowerQuery

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

[alansidman]

Additionally, here is a VBA solution.

Please Login or Register  to view this content.

[OzTrekker]

I suggest you try the Advanced Filter.
Have a look here.

[ashishkhurana93]

Advance filter wont make it dynamic.

Also I am not well versed with VBA so I am not looking for VBA solution.

@sandy666: Can you list down the steps to use power query

Alternatively, if anyone has any formula based solution, please post it

[alansidman]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[sandy666]

Can you list down the steps to use power query

first you need to install PQ
then
load table into PQ editor
remove duplicates
load query table into the sheet
play