Get column header from 1 sheet & compare rows with another sheet

sugibala

New Member
Joined
Sep 6, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi

can any one help me in getting the below scenario.

i have 2 sheets in excel. sheet 1 with data. sheet2 has column headers as reference in column B2
i want to take column headers from sheet1 . Iterate through sheet2 (column 2) .If my header value matches then take the B3 value from sheet 2.It can b M(Mandatory field check), B(Boolean value check) and so on..
I have the code snippet for doing the check operations but it is hardcoded with the columnname which i want to be dynamic (retrieve the column cell based on matching column header) .

And i also struggle to get the header and find it in sheet2. can any of u help. I am attaching the 2 excel sheet and code
VBA Code:
Dim intResult As Integer
Dim Data_sh1 As Worksheet, User_Details_sh2 As Worksheet
Dim Data_Lr As Long, User_Details_lr As Long, lc As Long, lc1 As Long, i As Long, j As Long
 
Dim a() As Variant, b() As Variant


Set Data_sh1 = Sheets("Service User") 'origin


Set User_Details_sh2 = Sheets("User Details")  'destination




'last row on origin sheet


Data_Lr = Data_sh1.Range("A" & Rows.Count).End(xlUp).Row


'last row on destination sheet


User_Details_lr = User_Details_sh2.Range("A" & Rows.Count).End(xlUp).Row + 1




'Store headers in the "a" variable of the origin sheet


lc = Data_sh1.Cells(1, Columns.Count).End(xlToLeft).Column


a = WorksheetFunction.Transpose(Data_sh1.Range("A1", Data_sh1.Cells(1, lc)).Value)
 
 'Store headers in the "b" variable of the destination sheet


lc1 = User_Details_sh2.Range("B" & Rows.Count).End(xlUp).Row
 


b = WorksheetFunction.Transpose(User_Details_sh2.Range("B2", User_Details_sh2.Cells(1, lc1)).Value)
 
 MsgBox lc
 
Dim TimeZone_i As Integer
Dim TimeZone_rng As Range
 Dim Picklist_TimeZone As Long


 With Data_sh1
    For TimeZone_i = 1 To .Range("A1").SpecialCells(xlCellTypeLastCell).Column
    
        Set TimeZone_rng = .Range("A1:A" & TimeZone_i)
        If WorksheetFunction.CountIf(User_Details_sh2.Range("B:B"), TimeZone_rng) = 0 Then 'picklist reference sheet column E
            MsgBox User_Details_sh2.Range("B:B").Cells.Value
            Picklist_TimeZone = Picklist_TimeZone + 1
        Else
            TimeZone_rng.Interior.Color = vbWhite


        End If
    Next
End With

individual mandatory check code hardcoded with column cell .which i want dynamic too
''Get the last row of a sheet
'LR = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
'
'Dim External_ID_i As Long
'Dim External_ID_c As Long
'Dim External_ID_myRange As Range
'Dim External_ID_myCell As Range
'Dim MyList(2) As String ' create array with definite size and value
'MyList(0) = "Open" ' assign value
'MyList(1) = "Closed"
'
'Set External_ID_myRange = Range("A1:A" & LR)
'
'For Each External_ID_myCell In External_ID_myRange '
'     External_ID_c = External_ID_c + 1
'    If IsEmpty(External_ID_myCell) Then
'        External_ID_myCell.Interior.Color = RGB(255, 87, 87)
'        External_ID_i = External_ID_i + 1
'
'    Else
'        External_ID_myCell.Interior.Color = vbWhite
'    End If
'
'
'Next External_ID_myCell
'
'If External_ID_i > 0 Then
'MsgBox _
'"External ID (Column A) : There are total " & External_ID_i & " empty cells out of " & External_ID_c & ".Input a Number."
'

in the attached sheets, sheet1 finds the header in sheet2 of any order, then it takes the value M/B. in my sheet1, lastname row5 s empty which has to be highlighted since its mandate.the same way for country.
 

Attachments

  • generic_sheet1.png
    generic_sheet1.png
    182 KB · Views: 7
  • generic_sheet2.png
    generic_sheet2.png
    182 KB · Views: 7

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,144,280
Messages
5,723,470
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top