matching rows and columns

[Aberdham]

Hi guys,

I have around 200 rows in one tab and 600 columns in tab 2. I would like to find the rows and columns that match with each other and delete the columns that doesn't appear in the row names in tab 1.

so the row names in tab one are something like this:

and the column names in tab two are like this:

all the row names in tab 1 will appear in the column names in tab 2, so I would like to eliminate the names in tab 2 that does not show up in tab 1

Thanks in advance.

 

[JoeMo]

This assumes Tab1 column is col A starting in cell A1, and Tab2 is row 1 starting in cell A1.
Sheets are assumed to have tab names "Tab1" and "Tab2".

Sub DeleteColIf()
Dim Tab1 As Worksheet, Tab2 As Worksheet, R1 As Range, R2 As Range, i As Long, x
Set Tab1 = Worksheets("Tab1"): Set Tab2 = Worksheets("Tab2")
Set R1 = Tab1.Range("A1:A" & Tab1.Cells(Rows.Count, "A").End(xlUp).Row)
Set R2 = Tab2.Range("A1", Tab2.Cells(1, Columns.Count).End(xlToLeft))
For i = 1 To R2.Columns.Count
    On Error Resume Next
    x = Application.Match(R2(i), R1, 0)
    On Error GoTo 0
    If IsError(x) Then R2(i) = "#N/A"
Next i
With Tab2
    On Error Resume Next
    .Rows(1).SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Delete
End With
End Sub

 

[Aberdham]

This assumes Tab1 column is col A starting in cell A1, and Tab2 is row 1 starting in cell A1.
Sheets are assumed to have tab names "Tab1" and "Tab2".

Sub DeleteColIf()
Dim Tab1 As Worksheet, Tab2 As Worksheet, R1 As Range, R2 As Range, i As Long, x
Set Tab1 = Worksheets("Tab1"): Set Tab2 = Worksheets("Tab2")
Set R1 = Tab1.Range("A1:A" & Tab1.Cells(Rows.Count, "A").End(xlUp).Row)
Set R2 = Tab2.Range("A1", Tab2.Cells(1, Columns.Count).End(xlToLeft))
For i = 1 To R2.Columns.Count
    On Error Resume Next
    x = Application.Match(R2(i), R1, 0)
    On Error GoTo 0
    If IsError(x) Then R2(i) = "#N/A"
Next i
With Tab2
    On Error Resume Next
    .Rows(1).SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Delete
End With
End Sub

Thank you for your response.

I thought there would be a formula that I could use so that I don't have to use all the functions with VBA.

for example my tab1 is called INFO tab and tab2 is called prices tab. all my row names are located in INFO tab in column A, and there are other texts in column B,C and D
as for my prices tab the column names are located in first row.

 

[JoeMo]

Thank you for your response.

I thought there would be a formula that I could use so that I don't have to use all the functions with VBA.

for example my tab1 is called INFO tab and tab2 is called prices tab. all my row names are located in INFO tab in column A, and there are other texts in column B,C and D
as for my prices tab the column names are located in first row.

Native worksheet formulas return calculated values, but they can't delete rows. VBA is needed for that. Replace the code I posted with that below which I modified to use your sheet names. If you need help on installing and using the code, post back.

Sub DeleteColIf()
Dim Tab1 As Worksheet, Tab2 As Worksheet, R1 As Range, R2 As Range, i As Long, x
Set Tab1 = Worksheets("INFO"): Set Tab2 = Worksheets("Prices")
Set R1 = Tab1.Range("A1:A" & Tab1.Cells(Rows.Count, "A").End(xlUp).Row)
Set R2 = Tab2.Range("A1", Tab2.Cells(1, Columns.Count).End(xlToLeft))
For i = 1 To R2.Columns.Count
    On Error Resume Next
    x = Application.Match(R2(i), R1, 0)
    On Error GoTo 0
    If IsError(x) Then R2(i) = "#N/A"
Next i
With Tab2
    On Error Resume Next
    .Rows(1).SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Delete
End With
End Sub