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
source to share
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.
source to share
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.
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.
source to share