VLookup multiple values on very large data file

[GoodDaySir0]

I'm trying to perform a vlookup to display any and all values associated with the lookup value. See example below:

What I have: Book1.xlsx - Sheet1
[TABLE="width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Won[/TD]
[/TR]
[TR]
[TD]Sara[/TD]
[TD]Won[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]Sara[/TD]
[TD]Tied[/TD]
[/TR]
[TR]
[TD]Sara[/TD]
[TD]Won[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Tied[/TD]
[/TR]
</tbody>[/TABLE]


What I want: Book2.xlsx - Sheet1

[TABLE="width: 400"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Results[/TD]
[TD]Results[/TD]
[TD]Results[/TD]
[TD]Results[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]Sara[/TD]
[TD]Won[/TD]
[TD]Tied[/TD]
[TD]Won[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Won[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Lost[/TD]
[TD]Tied[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Lost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Here's the formula I've been using (plugging into B2 on Book2 and hitting CTRL+SHIFT+ENTER):

{=INDEX([Book1.xlsx]Sheet1!$A$2:$B$8,SMALL(IF([Book1.xlsx]Sheet1!$A$2:$B$8=$A2,ROW([Book1.xlsx]Sheet1!$A$2:$B$8)-1),COLUMNS($B2:B2)),2)}

The problem I'm running into is that my Book1 has about 1,000,000 rows of data and my Book2 has about 500,000 IDs. Also one single ID can have up to about 20 results. When I run this formula for more than 5 cells at a time, I get the error message saying excel ran out of resources while attempting to calculate one or more formulas.

Is there a better way of displaying all the results for each ID within a single row (per ID)?

Thanks!

 

[AlKey]

Enter this formula to get uniqe names list in D2 and copy down

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$8,MATCH(0,INDEX(COUNTIF(D$1:D1,A$2:A$8),,),))))

Enter this array formula in E2 and copy across and down

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX($B:$B,SMALL(IF($A$2:$A$8=$D2,ROW($A$2:$A$8)),COLUMNS($E:E)))))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Book1
	A	B	C	D	E	F	G
1	Name	Results		Transposed
2	Jim	Won		Jim	Won
3	Sara	Won		Sara	Won	Tied	Won
4	Mike	Lost		Mike	Lost	Tied
5	Sara	Tied		Peter	Lost
6	Sara	Won
7	Peter	Lost
8	Mike	Tied
Sheet1

 

[GoodDaySir0]

Enter this formula to get uniqe names list in D2 and copy down

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$8,MATCH(0,INDEX(COUNTIF(D$1:D1,A$2:A$8),,),))))

Enter this array formula in E2 and copy across and down

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX($B:$B,SMALL(IF($A$2:$A$8=$D2,ROW($A$2:$A$8)),COLUMNS($E:E)))))

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

	A	B	C	D	E	F	G
Name	Results	Transposed
Jim	Won
Sara	Won	Tied	Won
Mike	Lost	Tied
Peter	Lost

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]Jim[/TD]
[TD="bgcolor: #FAFAFA"]Won[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]Sara[/TD]
[TD="bgcolor: #FAFAFA"]Won[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]Mike[/TD]
[TD="bgcolor: #FAFAFA"]Lost[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Sara[/TD]
[TD="bgcolor: #FAFAFA"]Tied[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]Sara[/TD]
[TD="bgcolor: #FAFAFA"]Won[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Peter[/TD]
[TD="bgcolor: #FAFAFA"]Lost[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Mike[/TD]
[TD="bgcolor: #FAFAFA"]Tied[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Hi AlKey,

Thanks for the response. The formula isn't working because my IDs are numerical IDs instead of names like Sara, Jim, Mike. Sorry, I should have specified that.

Would you be able to make this work for numerical IDs instead of names?

Thanks!

 

[AlKey]

Enter this formula to get uniqe names list in D2 and copy down

=IFERROR(INDEX(A$2:A$8,MATCH(0,INDEX(COUNTIF(D$1:D1,A$2:A$8),,),)),"")

Enter this array formula in E2 and copy across and down

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$8=$D2,ROW($A$2:$A$8)),COLUMNS($E:E))),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Book1
	A	B	C	D	E	F	G	H
1	Name	Results		Transposed
2	1001	Won		1001	Won
3	1002	Won		1002	Won	Tied	Won
4	1003	Lost		1003	Lost	Tied
5	1002	Tied		1004	Lost
6	1002	Won
7	1004	Lost
8	1003	Tied
Sheet1

 

[GoodDaySir0]

Hi AlKey,

Thanks for the response. The formula isn't working because my IDs are numerical IDs instead of names like Sara, Jim, Mike. Sorry, I should have specified that.

Would you be able to make this work for numerical IDs instead of names?

Thanks!

Nevermind! I converted the ID numbers to text so it's working now. Unfortunately it's still giving me an error message because of the large numbers of rows I'm working with.

 

[AlKey]

In this case maybe you should look for a VBA solution rather than formula.