Have a common column but different row numbers on both sheets

I have a dataset in different excel sheets.

Both sheets have one common column. Column name: name in sheet 1 and name (class) in sheet 2)

Sheet 1: Column Name:

  • Name, class, gender

Sheet 2: Column Name:

  • Name (class), Subject, Grade

I want to be able to click on a cell in any sheet and direct me to sheet3, which shows the relevant data related to that selected cell. For example, if I clicked on a name in sheet 1, that name and the corresponding data for that name would be shown on sheet 3. The line numbers for names in sheet 1 and 2 are different from each other.

CODES:

DetailsUsedRange = ThisWorkbook.Sheets("Sheet1").UsedRange.Rows.Count

With ActiveCell
    Name = ThisWorkbook.Sheets("Sheet1").Cells(.Row, 9) 
    NoName = InStr(1, "(" & ThisWorkbook.Sheets("Sheet2").Cells(.Row, 1) & ")", (Name)) 
    MsgBox NoName 
    MsgBox Name 
    For i = DetailsUsedRows To 1 Step -1 
        If (NoName <> 0) Then ThisWorkbook.Sheets("Student Viewer").Cells(2, 3).Value = ThisWorkbook.Sheets("Sheet1").Cells(.Row, 1).Value
..... 
        end if 
    next i 

      

I want it to display like this. But the name (IC) prevents me from receiving different data under the same name and ic

+3


source to share


2 answers


My idea would be to use vlookup in vba with dynamic range.

Dim i As Long, k As Long

i = Sheets("sheet2").UsedRange.Rows.Count

k = 1

With ActiveCell

    Sheets("Sheet3").Range("A2") = ActiveCell & "(" & ActiveCell.Offset(0, 1) & ")"

    For J = 2 To i
        k = WorksheetFunction.Match(Sheets("Sheet3").Range("A2"), Sheets("sheet2").Range("A2:A9"), 0) + k
        If k > i Then
            Exit For
        End If
        Sheets("sheet3").Range("A" & J) = Sheets("Sheet3").Range("A2")
        Sheets("Sheet3").Range("B" & J) = WorksheetFunction.VLookup(Sheets("sheet3").Range("A2"), Sheets("Sheet2").Range("A" & k & ":C" & i), 2, 0)
        Sheets("Sheet3").Range("C" & J) = WorksheetFunction.VLookup(Sheets("sheet3").Range("A2"), Sheets("Sheet2").Range("A" & k & ":C" & i), 3, 0)
    Next
End With

Sheets("sheet3").activate

      



You will need to change the sheet references and possible ranges. But what my code does is it will accept activecell. Take the name and class and place them in "Name (class)" format, then paste it on sheet3 ("A2"). Then do a vlookup on sheet2 to find name matches, then give the object and score in the following columns on Sheet3.

EDIT updated code to fix the bug.

0


source


Assumptions:

  • Both Sheet1 and Sheet2 start at Row 3 (Header), the actual data starts at Row4.
  • The output is sent on the first line of Sheet3 (A1).

This refers to the regular mod (for example Module1

)

Sub Get_Data(val As String, source1 As Worksheet, _
        source2 As Worksheet, dest As Worksheet)

    Dim lr As Long
    With source1
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("A3:C" & lr)
            .AutoFilter 1, val
            .SpecialCells(xlCellTypeVisible).Copy dest.Range("A1")
        End With
        .AutoFilterMode = False
    End With

    With source2
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("A3:C" & lr)
            .AutoFilter 1, val
            .Offset(0, 1).Resize(, 2).SpecialCells( _
                xlCellTypeVisible).Copy dest.Range("D1")
        End With
        .AutoFilterMode = False
    End With
End Sub

      

This refers to your Sheet1 code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo halt
    Application.EnableEvents = False
    Dim r As Range, lr As Long

    With Me
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        Set r = .Range("A4:A" & lr)
        Sheet3.Cells.ClearContents
        If Not Intersect(Target, r) Is Nothing _
        And Len(Target.Value2) <> 0 Then
            Get_Data Target.Value2, Me, Sheet2, Sheet3
            Sheet3.Activate ' Just to view Sheet3
        End If
    End With

moveon:
    Application.EnableEvents = True
    Exit Sub
halt:
    MsgBox Err.Description
    Resume moveon
End Sub

      



Now if you want to get the same effect in Sheet2, you also need to add the same event to it.
Note that you only need to add parameters to Get_Data

sub like this:

Get_Data Target.Value2, Sheet1, Me, Sheet3

      

Btw, Sheet1, Sheet2 and Sheet3

are codenames.
This is the default when creating a new workbook.
Now, to check if you are using the correct name of the codes, you need to check it in the properties window.

enter image description here

Thus, this happens every time you choose a name. If you want to add more control, you can pass code Selection_Change

to the event Double_Click

. This way, you will only see the entry if you double-click the name.

0


source







All Articles