How V look up Array Lookup Duplicates

[punnipah]

Hi,


Please help How V look up Array Lookup Duplicates Between Sheet1 and Sheet2.

-Data in Columns "B" (Sheet1) V look up to Columns "B"(Sheet2).
-V lookup Value Columns "C" Get data "Name"





Sheet 1

A	B	C​
No	Credit Card No./Transaction No.	Name
1	559888XXXXXX9930	V lookup Value This Columns
2	559888XXXXXX4787
3	559888XXXXXX4787
4	559888XXXXXX2043
5	559888XXXXXX2043

Sheet 2

A​	B​	C​
No	Credit Card No./Transaction No.	Name
1	559888XXXXXX9930	Thomas
2	559888XXXXXX4787	Brooklyn
3	559888XXXXXX4787	Billiam
4	559888XXXXXX2043	Sky
5	559888XXXXXX2043	Sea

 

[Peter_SSs]

Is this what you mean?

punnipah.xlsm
	A	B	C
1	No	Credit Card No./Transaction No.	Name
2	1	559888XXXXXX9930	Thomas
3	2	559888XXXXXX4787	Brooklyn
4	3	559888XXXXXX4787	Billiam
5	4	559888XXXXXX2043	Sky
6	5	559888XXXXXX2043	Sea
Sheet2

punnipah.xlsm
	A	B	C
1	No	Credit Card No./Transaction No.	Name
2	1	559888XXXXXX9930	Thomas
3	2	559888XXXXXX4787	Brooklyn
4	3	559888XXXXXX4787	Billiam
5	4	559888XXXXXX2043	Sky
6	5	559888XXXXXX2043	Sea
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX(Sheet2!C:C,AGGREGATE(15,6,ROW(Sheet2!C$2:C$6)/(Sheet2!B$2:B$6=B2),COUNTIF(B$2:B2,B2)))

 

[punnipah]

Is this what you mean?

punnipah.xlsm
	A	B	C
1	No	Credit Card No./Transaction No.	Name
2	1	559888XXXXXX9930	Thomas
3	2	559888XXXXXX4787	Brooklyn
4	3	559888XXXXXX4787	Billiam
5	4	559888XXXXXX2043	Sky
6	5	559888XXXXXX2043	Sea
Sheet2

punnipah.xlsm
	A	B	C
1	No	Credit Card No./Transaction No.	Name
2	1	559888XXXXXX9930	Thomas
3	2	559888XXXXXX4787	Brooklyn
4	3	559888XXXXXX4787	Billiam
5	4	559888XXXXXX2043	Sky
6	5	559888XXXXXX2043	Sea
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX(Sheet2!C:C,AGGREGATE(15,6,ROW(Sheet2!C$2:C$6)/(Sheet2!B$2:B$6=B2),COUNTIF(B$2:B2,B2)))

Thank You Very Much.^^

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[punnipah]

Hi,Peter_SSs


I Have VBA Code : Range("C2").Formula = "=INDEX(WEB!W:W,AGGREGATE(15,6,ROW(WEB!W$2:W$50000)/(WEB!W$2:W$50000=B2),COUNTIF(B$2:B2,B2)))"

But sometimes less 50,000 Rows and sometimes more 50,000 Rows. How to count rows in VBA Code used in this code.



Thank You Very much.

You're welcome. Thanks for the follow-up.

 

[Peter_SSs]

I don't think that you have adapted that formula correctly but if it is what you want you can either ..

• Make the 50,000 big enough to cover any data you might have (maybe 100,000?) or
• Do something like this.

Dim LR As Long

LR = Sheets("WEB").Range("W" & Rows.Count).End(xlUp).Row
Range("C2").Formula = Replace("=INDEX(WEB!W:W,AGGREGATE(15,6,ROW(WEB!W$2:W$#)/(WEB!W$2:W$#=B2),COUNTIF(B$2:B2,B2)))", "#", LR)

 

[punnipah]

Thank you very much for the help.

 

[Peter_SSs]

No worries. Glad to contribute.