VLOOKUP on AUTOFILTERED VISIBLE CELLS

[user2021]

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 id	source id	source entity id
1001	xtrader	xt-1
1002	xtrader	xt-2
1003	xtrader	xt-3
1004	xtrader	xt-4
1005	xtrader	xt-5
1006	xtrader	xt-6
1007	xtrader	xt-7
1008	xtrader	xt-8
1009	xtrader	xt-9
1010	xtrader	xt-0
1001	optex	op-1
1002	optex	op-2
1003	optex	op-3
1004	optex	op-4
1005	optex	op-5
1006	optex	op-6
1007	optex	op-7
1008	optex	op-8
1009	optex	op-9
1010	optex	op-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 id	source id - xtrader	source id - optex
1001	xt-1	op-1
1002	xt-2	op-2
1003	xt-3	op-3
1004	xt-4	op-4
1005	xt-5	op-5
1006	xt-6	op-6
1007	xt-7	op-7
1008	xt-8	op-8
1009	xt-9	op-9
1010	xt-0	op-0

 

[alansidman]

With Power Query, Pivot your data

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
	A	B	C
1	entity id	xtrader	optex
2	1001	xt-1	op-1
3	1002	xt-2	op-2
4	1003	xt-3	op-3
5	1004	xt-4	op-4
6	1005	xt-5	op-5
7	1006	xt-6	op-6
8	1007	xt-7	op-7
9	1008	xt-8	op-8
10	1009	xt-9	op-9
11	1010	xt-0	op-0
Table1

 

[user2021]

With Power Query, Pivot your data

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
	A	B	C
1	entity id	xtrader	optex
2	1001	xt-1	op-1
3	1002	xt-2	op-2
4	1003	xt-3	op-3
5	1004	xt-4	op-4
6	1005	xt-5	op-5
7	1006	xt-6	op-6
8	1007	xt-7	op-7
9	1008	xt-8	op-8
10	1009	xt-9	op-9
11	1010	xt-0	op-0
Table1

With Power Query, Pivot your data

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
	A	B	C
1	entity id	xtrader	optex
2	1001	xt-1	op-1
3	1002	xt-2	op-2
4	1003	xt-3	op-3
5	1004	xt-4	op-4
6	1005	xt-5	op-5
7	1006	xt-6	op-6
8	1007	xt-7	op-7
9	1008	xt-8	op-8
10	1009	xt-9	op-9
11	1010	xt-0	op-0
Table1

i prefer a VBA code, because this is for a daily report that am working with rows more than 30000+

 

[alansidman]

Power Query will work with more than one million rows. I don't see the issue.

 

[Peter_SSs]

Do you need to actually filter the Data sheet or use vba? Could you just use these formulas in the Result sheet?

user2021.xlsm
	A	B	C
1	entity id	source id	source entity id
2	1001	xtrader	xt-1
3	1002	xtrader	xt-2
4	1003	xtrader	xt-3
5	1004	xtrader	xt-4
6	1005	xtrader	xt-5
7	1006	xtrader	xt-6
8	1007	xtrader	xt-7
9	1008	xtrader	xt-8
10	1009	xtrader	xt-9
11	1010	xtrader	xt-0
12	1001	optex	op-1
13	1002	optex	op-2
14	1003	optex	op-3
15	1004	optex	op-4
16	1005	optex	op-5
17	1006	optex	op-6
18	1007	optex	op-7
19	1008	optex	op-8
20	1009	optex	op-9
21	1010	optex	op-0
Data

user2021.xlsm
	A	B	C
1	entity id	source id	source id
2		xtrader	optex
3	1001	xt-1	op-1
4	1002	xt-2	op-2
5	1003	xt-3	op-3
6	1004	xt-4	op-4
7	1005	xt-5	op-5
8	1006	xt-6	op-6
9	1007	xt-7	op-7
10	1008	xt-8	op-8
11	1009	xt-9	op-9
12	1010	xt-0	op-0
Result

Cell Formulas
Range	Formula
B3:C12	B3	=VLOOKUP($A3,FILTER(Data!$A$2:$C$75000,Data!$B$2:$B$75000=B$2),3,0)

 

[user2021]

Do you need to actually filter the Data sheet or use vba? Could you just use these formulas in the Result sheet?

user2021.xlsm
	A	B	C
1	entity id	source id	source entity id
2	1001	xtrader	xt-1
3	1002	xtrader	xt-2
4	1003	xtrader	xt-3
5	1004	xtrader	xt-4
6	1005	xtrader	xt-5
7	1006	xtrader	xt-6
8	1007	xtrader	xt-7
9	1008	xtrader	xt-8
10	1009	xtrader	xt-9
11	1010	xtrader	xt-0
12	1001	optex	op-1
13	1002	optex	op-2
14	1003	optex	op-3
15	1004	optex	op-4
16	1005	optex	op-5
17	1006	optex	op-6
18	1007	optex	op-7
19	1008	optex	op-8
20	1009	optex	op-9
21	1010	optex	op-0
Data

user2021.xlsm
	A	B	C
1	entity id	source id	source id
2		xtrader	optex
3	1001	xt-1	op-1
4	1002	xt-2	op-2
5	1003	xt-3	op-3
6	1004	xt-4	op-4
7	1005	xt-5	op-5
8	1006	xt-6	op-6
9	1007	xt-7	op-7
10	1008	xt-8	op-8
11	1009	xt-9	op-9
12	1010	xt-0	op-0
Result

Cell Formulas
Range	Formula
B3:C12	B3	=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