CODEWITHSUNDEEP
excelvba
ACSO
ACSO

Reputation: 41

Matching the data across column and rows using VBA

I have two sheets :

Sheet 1 consist of :

Sheet1

Sheet 2 consist of :

Sheet2

And the output should show in M column in Sheet1. I am attaching the sample output here :

Sample Output

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

Answers (2)

Zac
Zac

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

Michal Schmitt
Michal Schmitt

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:

  1. Sheet 1 is the main worksheet, sheet 2 has the lookup data.
  2. All names in the lookup data are unique.

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

Related Questions