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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
use the html maker in nbvc's sig.... thi way people can see your data tables
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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),"")
 

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
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.
 

Forum statistics

Threads
1,141,218
Messages
5,705,076
Members
421,377
Latest member
FerdiFuchs

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
Top