Need to delete Duplicate columns

Help
1

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.

See the sample Image

Capture12
Capture121238×677 100 KB

If any one know how to solve this update me.

2

You cannot do this via “Application scope”, have to simulate human steps ie open file in excel, goto the column F, right click, delete etc…

Even if you delete, I doubt whether you can open this file using “Application Scope” because column names contain spaces & special characters.

3

@sindhura506

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:

  1. you uncheck the “Add headers” property in your read range.
  2. you use column indices instead of column names.
4

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’

5

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.

6

Capture12
Capture121262×289 50.7 KB

I have excel sheet like this…there are lab results on day wise. some times we got duplicate date columns…so i need to delete that columns…

7

@sindhura506,

you can use macros to do that.

8

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.

Hope this resolves your problem. All the best.

9

No it is not working for me…I need to delete duplicate columns in excel with out using Data table

10

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.

Let me know if this helps.
Thanks,
Hari

11

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…

12

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

End Sub

image
image1366×768 85.7 KB

2 Likes
13

Hi , @Snehal14Kapse
It working …!
Thank you

1 Like