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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

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,192
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,704
Messages
5,524,416
Members
409,576
Latest member
az168

This Week's Hot Topics

Top