How to find duplicate column (not the cell values)

[Madiya]

I am receiving a file daily which contains lots of columns.
Some times, file contains same column twice.
I need to check and remove the whole column which is repeated.
I only need to check col headings in row 1 for duplicates and not the entire column data.

Pl help.

Regards,
Madiya.

 

[Claus Busch]

Hi,

Am Thu, 13 Jun 2013 02:04:51 -0700 (PDT) schrieb Madiya:

I am receiving a file daily which contains lots of columns.
Some times, file contains same column twice.
I need to check and remove the whole column which is repeated.
I only need to check col headings in row 1 for duplicates and not the entire column data.

your headers in A1:Z1.
Then try for column number:
=MODE(IF(A1:Z1<>"",MATCH(A1:Z1,A1:Z1,)))

or for the header:
=INDEX(A1:Z1,MODE(IF(A1:Z1<>"",MATCH(A1:Z1,A1:Z1,))))

Both formulas are array formulas to enter with CTRL+Shift+Enter


Regards
Claus Busch

 

[Madiya]

I am receiving a file daily which contains lots of columns.

Some times, file contains same column twice.

I need to check and remove the whole column which is repeated.

I only need to check col headings in row 1 for duplicates and not the entire column data.



Pl help.



Regards,

Madiya.

Claus,
Thanks for reply.
I have tried it and it gives me name of 1st duplicate column. If there is more duplicate column, I will not know it unless rest of the macro execution throws error.

Also it will be more helpfull if solution is in vba so that a msgbox can be desplayed with each duplicate column name.

I am expecting 3 to 5 duplicate columns in general.

Thanks again.

Regards,
Madiya

 

[Claus Busch]

Hi Madiya,

Am Thu, 13 Jun 2013 02:35:01 -0700 (PDT) schrieb Madiya:

I have tried it and it gives me name of 1st duplicate column. If there is more duplicate column, I will not know it unless rest of the macro execution throws error.

Also it will be more helpfull if solution is in vba so that a msgbox can be desplayed with each duplicate column name.

I am expecting 3 to 5 duplicate columns in general.

then try:

Sub DuplicateTest()
Dim rngC As Range
Dim myStr As String

For Each rngC In Range("A1:Z1")
If WorksheetFunction.CountIf(Range(Cells(1, 1), _
rngC), rngC) > 1 Then
myStr = myStr & rngC.Value & " " & _
rngC.Address(0, 0) & Chr(10)
End If
Next
MsgBox myStr
End Sub


Regards
Claus Busch