How to find and highlight duplicate values in a excel column using powershell

Question

I have duplicate values in a excel column. I want to find those duplicate values and highlight them with any color. I want to do so using powershell as I have many excel files. I can see that there is a RemoveDuplicate method, however I just want to find and highlight the duplicates. Sample data :

nikolinode · Answer

SamWhite&nbsp;&nbsp;Maybe this VBA solution will help you.&nbsp;&nbsp;&nbsp;I would be happy to know if I could help.&nbsp;NikolinoI know I don't know anything (Socrates)&nbsp;* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

samwhite · Answer

NikolinoDE&nbsp; Thanks, please suggest how to do the same via powershell

nikolinode · Answer

SamWhite&nbsp;As for powershell, I'm sure not the right one.Sry but here has to be someone else who is familiar with powershell.I'm not even sure whether your question will be answered here in the Excel forum, maybe in another forum here in the community.Thank you for your patience and time and sry that I could not give you a useful solution.Wish you a nice day / night with health and joy.&nbsp;NikolinoI know I don't know anything (Socrates)

samwhite · Answer

NikolinoDE&nbsp;Can you explain me the logic you used in the macro? Also I cannot find enough documentation on it, but do you know how to use the xldupeunique as described in the link below:&nbsp;https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xldupeunique?view=excel-pia

nikolinode · Answer

SamWhite&nbsp;I'm not sure if you mean this, but send it to you as info anyway.Function statementhttps://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statementNote: Functions cannot be used like sub-procedures. If you program VBA functions, you must know that these cannot be used like procedures. It is not possible to use a function to make changes to areas or other objects in your worksheet. For example, a function cannot change the color of a cell, even if this may seem useful in certain cases. Functions are only there to return values.Program your own functions (Unfortunately only in German to read)https://www.microsoft-press.de/chapter.asp?cnt=getchapter&amp;id=ms-5209.pdfRead xlDupeUnique enumeration?https://www.mrexcel.com/board/threads/read-xldupeunique-enumeration.1079128/&nbsp;In the end I have to admit that I saw this command at some point on the internet that it can be used and as a good thinker of retrospect, I have picked it up and use it wherever possible. How exactly it works and why I can't explain, just say that I can use it and it works when I need it. Sry, I'm not a big light in VBA, generally I don't have a lot of knowledge ... always a little bit of something... but with a lot of patience, perseverance and love to learn and share something new :).&nbsp;Thank you for your understanding and patience&nbsp;NikolinoI know I don't know anything (Socrates)&nbsp;

Forum Discussion

How to find and highlight duplicate values in a excel column using powershell

6 Replies

Resources