How to find duplicate column (not the cell values)

  • Thread starter Thread starter Madiya
  • Start date Start date
M

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.
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
Back
Top