Pull the subsequent data for repeat values (Vlookup/Index - Macth)

[pvkvimalan]

Hi,

I have a large data set with more than 15k rows. in one file (Say: File A) there is an SKU and Bin Location for all rows. And in the new file that I'm now working (Say: File B) contains the SKU number and I require to pull the corresponding Bin location. The challenge is that in File A both SKU and Bin Location will contain many repetitions. These are not duplicates but the same item can be found in the same bin location more than once. Also, the Same SKU could be located in various Bins. So I need the formula to pull the Bin Location subsequently one after other. I'm inserting the link below of a dummy data for better understanding of the issue.

Dummy data-Excel

Thanks in Advance

 

[hernantorres23]

Hi, are you try with a pivot table?
Look this result ? , then with F5 you can fill easily, all blanks fields

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Existing Data				Existing Data	Required Result	Remarks
2	SKU	Bin Location			SKU	BIN Location				SKU	Bin Location	Count of Bin Location
3	AA1	B-1			AA2	B-1				AA1	B-1	2
4	AA2	B-1			CC3	B-1					B-4	2
5	AA3	B-1			AA1	B-1	1st AA1 is in Bin location B-1				B-5	2
6	AA1	B-1			BB1	B-2					B-6	3
7	CC3	B-1			AA1	B-1	2nd AA1 is in same Bin Location B-1			AA2	B-1	1
8	BB4	B-1			CC2	B-3					B-2	2
9	CC4	B-1			BB2	B-1					B-3	2
10	AA2	B-2			AA1	B-4	3rd AA1 is in Bin Location B-4				B-4	1
11	AA3	B-2			BB2	B-2					B-5	1
12	AA4	B-2									B-6	1
13	BB2	B-2								AA3	B-1	1
14	CC1	B-2									B-2	1
15	BB1	B-2									B-3	1
16	AA2	B-2									B-4	2
17	CC3	B-3									B-5	2
18	AA2	B-3									B-6	1
19	BB3	B-3								AA4	B-2	1
20	AA4	B-3									B-3	2
21	CC2	B-3									B-6	1
22	AA2	B-3								BB1	B-2	1
23	AA3	B-3								BB2	B-2	1
24	AA4	B-3									B-4	1
25	BB2	B-4									B-5	2
26	AA1	B-4									B-6	1
27	CC1	B-4								BB3	B-3	1
28	AA2	B-4									B-6	1
29	AA3	B-4								BB4	B-1	1
30	CC1	B-4								CC1	B-2	1
31	AA3	B-4									B-4	2
32	AA1	B-4								CC2	B-3	1
33	BB2	B-5									B-5	1
34	AA2	B-5									B-6	1
35	AA3	B-5								CC3	B-1	1
36	CC4	B-5									B-3	1
37	BB2	B-5								CC4	B-1	1
38	AA1	B-5									B-5	1
39	AA1	B-5
40	CC2	B-5
41	AA3	B-5
42	AA1	B-6
43	BB3	B-6
44	AA1	B-6
45	BB2	B-6
46	AA2	B-6
47	AA3	B-6
48	AA4	B-6
49	CC2	B-6
50	AA1	B-6
51
Sheet1

 

[pvkvimalan]

Hi, @hernantorres23 Thank you for your reply. I had a Pivot table as an option but it didn't satisfy my requirements fully, however, I got a reply from another forum with the following formula which worked out for me.

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$3:$A$50)/($A$3:$A$50=E3),COUNTIF($E$3:E3,E3))),"")