Hi all, I’m Trying to Delete Duplicate columns not rows.
In excel i have same column name (Column Heading) for two columns. While reading the excel file i got this message " Column name already belongs to this data table - Duplicate Name Exception". so,I want to delete duplicate column.
I need headers also based on headers i’m taking some values so, i can’t remove headers.
Whenever you extract a table from an excel sheet, the column names should always be unique. If you’ve gone through the basics of excel automation, you’d know column index and column name is what you identify a row value with. Which is why the column names shall always be unique.
Now, since you don’t have any option here, this is what you do:
you uncheck the “Add headers” property in your read range.
I need Headers because in that excel i have Test Name, Unit, Test Case, like this columns based on Headers name i’m extracting some values. so, i need to check ‘Add Headers’
I suppose you should extract that part of the table using a separate read range, but to understand your requirement better and to come up with an exact solution, I’d need to see the entire table.
I don’t see those columns you mentioned in the screenshot you attached. It would be helpful if you could show us what your exact requirement is by either attaching your excel sheet here. If it’s confidential, you can always create a dummy and attach that.
The problem you have is only with the date columns. Hence, as I said earlier, divide your table into two parts.
From column “Level” to column “RefValue2”, extract the table, and check the “Add headers” property.
For the part of the table which will have the dates, use another read range activity, and this time, uncheck “Add headers” checkbox.
You now have two data tables. One with the headers retained, and another with headers “Column0” , “Column1”, “Column2” and so on. Use Indices for the second column.
Hi Sindhu,
Why don’t you put your data from second row ( Headers) read the data with out the headers checked in which means you have your headers but as a data and if you want to delete your duplicate column do some comparison between the first row items and you will get the desired result.
Thank you…I’m saving this data from another application…I’m right clicking the application and saving this file to excel after that i’m able to do the process…so i’m unable to put my data from 2nd row…
Use Invoke VBA Activity Inside Excel Application scope. Inside this provide the Text file path, Macro name & SheetName inside Properties panel. And Paste the below macro code inside Text file.
Sub Delete_Duplicate_Columns(SheetName as String)
Dim lastCol As Long
Dim thisCol As Long
With Sheets(SheetName)
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For thisCol = lastCol To 2 Step -1
If Application.Match(.Cells(1, thisCol).Value, Range(.Cells(1, 1), .Cells(1, lastCol)), 0) <> thisCol Then
.Cells(1, thisCol).EntireColumn.Delete xlShiftToLeft
End If
Next thisCol
End With