shrinking the data set but returning multiple values

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
i have an original set of data that is very large.

I have narrowed the records i want to show down to a few hundred and i would like to only show those records.

i would usually just use vlookup for this but the problem is that there can be more than one occurance of any given id code. so i vlookup would not return all the results.

does anyone have a suggestion on how to accomlish this?

thanks
Book1
ABCDEFGHI
1originallistdataitem1dataitem2newlistnewlistshouldshowdataitem1dataitem2
21a25551a1a2555
31a36661d1a3666
41a47775r1a4777
51d58881d5888
61d69991d6999
71g711105r111554
81f81221
91t91332
103f101443
115r111554
Sheet1
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Book1
ABCDEFGHI
1original listdata item 1data item 20Include List6
21a255511anew listdata item 1data item 2
31a366621d1a2555
41a477735r1a3666
51d588841a4777
61d699951d5888
71g71110 1d6999
81f81221 5r111554
91t91332    
103f101443 
115r1115546
12
Sheet1


D1 must house a 0.

D2, copied down:

=IF(ISNUMBER(MATCH(A2,$E$2:$E$4,0)),LOOKUP(9.99999999999999E+307,$D$1:D1)+1,"")

G1:

=LOOKUP(9.99999999999999E+307,D1:D11)

G3, copied across and down:

=IF(ROWS(G$3:G3)<=$G$1,LOOKUP(ROWS(G$3:G3),$D$2:$D$11,A$2:A$11),"")
 
Upvote 0
aladin,

Im not sure i follow your logic.

would this also work if 3f was in the new list between 1d and 5r?

maybe my example was not the best.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top