Duplicate removal in excel

[blazino17]

I am building a process that needs me to check for dupicates before progressing to the next stage. I have checked through the MS Excel VBO and i cant find any action that can perform the action i want to perform. How can i remove the duplicates on the column Id?

[michaeloneil]

Hi @blazino17 

Im not sure if the latest version of the vbo contains an action for this so first thing to do is make sure you have the latest version of the excel vbo from the digital exchange. If you have the latest one and it doesnt include the action to remove duplicates then you can try getting the range from excel and adding it as a collection in Blueprism. Then you can use the vbo Utility - Collection Manipulation Extended and there is an action in there called extract duplicates. If this doesnt work for you or isnt suitable I built a previous action for excel that removes duplicates, i can provide the code if you are comfortable building your own action.

[blazino17]

Thank you Michael for responding to me, I have the latest version of Excel vbo and it includes the action remove duplicates but from the Business object definition page it was stated that it can be used to remove blank rows which is not what i want to achieve at the moment. Please see attached below.

Also,I was not able to see the aforementioned vbo Collection Manipulation Extended on the Dx portal so as to use the action extract duplicate.

I would also appreciate having the code to remove duplicates in Excel, as I am eager to learn how to create my own actions.

Secondly, once this has been achieved, I would also like to ask: how do you format a column to remove spaces, commas, and Fullstop?

[michaeloneil · Accepted solution]

The code is shown below. As for removing text and whitespace, i would keep that simple and use a formula to do that. In a blank row add a formula similar to =TRIM(SUBSTITUTE(SUBSTITUTE(A1,".","",1),",","",1))

Dim wb, ws As Object
Dim excel, sheet, range As Object

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()

ws.Range(Reference).RemoveDuplicates (Columns:=Column_number, Header:= 2)

[Mohamad_747]

@blazino17  

Do you still need help on this ?

If yes, show us your desired output and we will create a custom code for you

[blazino17]

The code worked perfectly, and the formula TRIM(SUBSTITUTE(SUBSTITUTE(A1,".","",1),",","",1)) produced the expected result when applied manually in Excel as shown below. However, I’m trying to create an action that performs this operation across all cells in my  Name column. I attempted using the "Set Cell Value" action, but it applied the same value to every cell in the column. so I believe I may be missing something or doing something incorrectly. I am also thinking of creating a loop  but it doesnt sound robust because what if i have 500 row datas. please what do i do. 

[michaeloneil · Accepted solution]

Ah sorry i maybe havent given full instructions there, the formula should be inserted into one cell initially and then that cell dragged down to autofill the rest. The formula references are dynamic so it will auto update as the formula is dragged down. I believe in the latest excel vbo there is an aciton to drag values downward so for the example you have given insert the cell value to F1 and then drag down to F 10

[blazino17]

Thank you so much, Michael.

I have successfully resolved my issues  just as you have directed. The action in the MS Excel VBO is Autofill, and I’m truly happy and grateful!