VLOOKUP on AUTOFILTERED VISIBLE CELLS

user2021

New Member
Joined
Mar 10, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I am trying to vlookup for visible cells. Have 2 sheets Data (that has the required data) and Result(where i need the desired result). On the Data sheet I want to Autofilter on "sourceid" column and all that has "xtrader", i want to do a vlookup based on "entity id" and get my "source entity id" in my result sheet. similarly i want the entity id when i autofilter on "optex" as well.

Data Sheet
entity idsource idsource entity id
1001xtraderxt-1
1002xtraderxt-2
1003xtraderxt-3
1004xtraderxt-4
1005xtraderxt-5
1006xtraderxt-6
1007xtraderxt-7
1008xtraderxt-8
1009xtraderxt-9
1010xtraderxt-0
1001optexop-1
1002optexop-2
1003optexop-3
1004optexop-4
1005optexop-5
1006optexop-6
1007optexop-7
1008optexop-8
1009optexop-9
1010optexop-0

Below is my code - when i run this code, the values i get in result sheet is "#N/A". (I wrote this code just for getting "source entity id" based on "xtrader" only as a trial).

Dim sht, sht1 As Worksheet
Dim i As Long, LR As Long, LR1 As Long
Dim Rng As Range

Set sht = ActiveWorkbook.Worksheets("result")
Set sht1 = ActiveWorkbook.Worksheets("data")

LR = sht.UsedRange.Rows.Count
LR1 = sht1.UsedRange.Rows.Count

Set Rng = sht1.Range("B2:B" & LR1).Cells.SpecialCells(xlCellTypeVisible)

sht1.Range("A1:C1").AutoFilter Field:=2, Criteria1:="xtrader"
With sht
For i = 2 To LR
Range("B" & i).Value = (Application.VLookup(sht1.Range("B2:B" & LR1).Cells.SpecialCells(xlCellTypeVisible).Range("A" & i).Value, sht1.Range("A2:C75000"), 3, False))
Next i
End With

I get all "#N/A" values in my result sheet column B when i run the above code. But my result sheet should be as below -

entity idsource id - xtradersource id - optex
1001xt-1op-1
1002xt-2op-2
1003xt-3op-3
1004xt-4op-4
1005xt-5op-5
1006xt-6op-6
1007xt-7op-7
1008xt-8op-8
1009xt-9op-9
1010xt-0op-0
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,457
Office Version
  1. 365
Platform
  1. Windows
With Power Query, Pivot your data

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"source id"]), "source id", "source entity id")
in
    #"Pivoted Column"

Book7
ABC
1entity idxtraderoptex
21001xt-1op-1
31002xt-2op-2
41003xt-3op-3
51004xt-4op-4
61005xt-5op-5
71006xt-6op-6
81007xt-7op-7
91008xt-8op-8
101009xt-9op-9
111010xt-0op-0
Table1
 

user2021

New Member
Joined
Mar 10, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
With Power Query, Pivot your data

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"source id"]), "source id", "source entity id")
in
    #"Pivoted Column"

Book7
ABC
1entity idxtraderoptex
21001xt-1op-1
31002xt-2op-2
41003xt-3op-3
51004xt-4op-4
61005xt-5op-5
71006xt-6op-6
81007xt-7op-7
91008xt-8op-8
101009xt-9op-9
111010xt-0op-0
Table1

With Power Query, Pivot your data

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"source id"]), "source id", "source entity id")
in
    #"Pivoted Column"

Book7
ABC
1entity idxtraderoptex
21001xt-1op-1
31002xt-2op-2
41003xt-3op-3
51004xt-4op-4
61005xt-5op-5
71006xt-6op-6
81007xt-7op-7
91008xt-8op-8
101009xt-9op-9
111010xt-0op-0
Table1
i prefer a VBA code, because this is for a daily report that am working with rows more than 30000+
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,457
Office Version
  1. 365
Platform
  1. Windows
Power Query will work with more than one million rows. I don't see the issue.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
Do you need to actually filter the Data sheet or use vba? Could you just use these formulas in the Result sheet?

user2021.xlsm
ABC
1entity idsource idsource entity id
21001xtraderxt-1
31002xtraderxt-2
41003xtraderxt-3
51004xtraderxt-4
61005xtraderxt-5
71006xtraderxt-6
81007xtraderxt-7
91008xtraderxt-8
101009xtraderxt-9
111010xtraderxt-0
121001optexop-1
131002optexop-2
141003optexop-3
151004optexop-4
161005optexop-5
171006optexop-6
181007optexop-7
191008optexop-8
201009optexop-9
211010optexop-0
Data


user2021.xlsm
ABC
1entity idsource idsource id
2xtraderoptex
31001xt-1op-1
41002xt-2op-2
51003xt-3op-3
61004xt-4op-4
71005xt-5op-5
81006xt-6op-6
91007xt-7op-7
101008xt-8op-8
111009xt-9op-9
121010xt-0op-0
Result
Cell Formulas
RangeFormula
B3:C12B3=VLOOKUP($A3,FILTER(Data!$A$2:$C$75000,Data!$B$2:$B$75000=B$2),3,0)
 

user2021

New Member
Joined
Mar 10, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Do you need to actually filter the Data sheet or use vba? Could you just use these formulas in the Result sheet?

user2021.xlsm
ABC
1entity idsource idsource entity id
21001xtraderxt-1
31002xtraderxt-2
41003xtraderxt-3
51004xtraderxt-4
61005xtraderxt-5
71006xtraderxt-6
81007xtraderxt-7
91008xtraderxt-8
101009xtraderxt-9
111010xtraderxt-0
121001optexop-1
131002optexop-2
141003optexop-3
151004optexop-4
161005optexop-5
171006optexop-6
181007optexop-7
191008optexop-8
201009optexop-9
211010optexop-0
Data


user2021.xlsm
ABC
1entity idsource idsource id
2xtraderoptex
31001xt-1op-1
41002xt-2op-2
51003xt-3op-3
61004xt-4op-4
71005xt-5op-5
81006xt-6op-6
91007xt-7op-7
101008xt-8op-8
111009xt-9op-9
121010xt-0op-0
Result
Cell Formulas
RangeFormula
B3:C12B3=VLOOKUP($A3,FILTER(Data!$A$2:$C$75000,Data!$B$2:$B$75000=B$2),3,0)
I need to do a filter and use VBA, because this is for my daily report and the data i have is more than 30000 rows
 

Forum statistics

Threads
1,141,072
Messages
5,704,133
Members
421,329
Latest member
mippy

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