Reputation: 41
I have two sheets :
Sheet 1 consist of :
Sheet 2 consist of :
And the output should show in M column in Sheet1. I am attaching the sample output here :
So,what I have here is ID in Sheet 1, for eg : ID 'US' has Abhay,Carl and Dev and in Sheet3, I have names in column and ID in Rows. What i want is my Sample output column should populate using macro based on matched values from Sheet3
I am using below logic but something is going wrong :
For i = 2 To 10
j = i + 1
If ThisWorkbook.Sheets("Input").Range("N" & i) = ThisWorkbook.Sheets("Sheet3").Range("A" & i) And ThisWorkbook.Sheets("Input").Range("K" & i) = ThisWorkbook.Sheets("Sheet3").Range("B1") Then
ThisWorkbook.Sheets("Input").Range("O" & i) = ThisWorkbook.Sheets("Sheet3").Range("B" & j)
End If
Next i
Upvotes: 0
Views: 104
Reputation: 1942
There are a few ways to approach this. Below is one of them:
NOTE: for simplicity, I have kept my data on one sheet. You can amend the below formulas as your data is on 2 sheets. Saying that, I have used the same columns as you have in your query
Solution:
Have a "holding column". In my example, I used column J
as the holding column (you can hide this column if you want). In J2
, type the following formula: =IF(ISBLANK($K2), $J1,$K2)
. Copy the formula down to all used rows. Then copy the following formula in M2
: =VLOOKUP($L2,$A$3:$C$8,IF($J2="US",2,3),FALSE)
. As per before, copy the formula down to all used rows. This should give you your results
Upvotes: 0
Reputation: 216
Since you asked for a VBA solution, please see the code below.
Dim colLen As Integer
Dim i As Integer
Dim colPt As Integer
Dim rowPt As Integer
' Counts number of rows on Sheet 1, column B.
colLen = Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row
' Loops through all names on Sheet 1.
For i = 2 To colLen
' Retain US or NA ID for blank cells.
If Sheets(1).Cells(i, 1) <> "" Then
If Sheets(1).Cells(i, 1) = "US" Then
colPt = 2
Else
colPt = 3
End If
End If
' Find name on Sheet 2 and set row.
rowPt = Sheets(2).Range("A:A").Find(Sheets(1).Cells(i, 2)).Row
' Add ID from Sheet 2 to Sheet 3
Sheets(1).Cells(i, 3) = Sheets(2).Cells(rowPt, colPt)
Next i
Assumptions:
I would recommend including the ID in every row instead of treating it as a heading but that's preference. There are formula solutions that would work for this as well if you want to skip VBA.
Upvotes: 1