data extract

[mark]

I am having trouble figuring out how to extract certian
data from one sheet and display onto a different.I have
tried the combination of index and match functions but
this will only seem to work if the model has all its #'s
and desc's on one row.

I have:
model # desc
123 11 aaaa
123 02 ssss
456 63 dddd
456 78 ffff
789 98 gggg
789 97 hhhh

On a seperate worksheet I want to put Model-123 in A1 and
have all #'s and desc's populate that match 123.

any help will be appreciated

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
B1:
=INDEX('sheet1'!$B1:$B$100,SMALL(IF('sheet1'!$A$1:$A$100=$A$1,ROW('shee
t1'!$A$1:$A$100)),ROW(1:1)))

C1:
=INDEX('sheet1'!$C1:$C$100,SMALL(IF('sheet1'!$A$1:$A$100=$A$1,ROW('shee
t1'!$A$1:$A$100)),ROW(1:1)))

and copy both formulas down

 

[Guest]

control shift enter doesent do anything.

-----Original Message-----
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
$A$1:$A$100=$A$1,ROW('shee
t1'!$A$1:$A$100)),ROW(1:1)))

and copy both formulas down

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Frank Kabel]

Hi
after inserting these formula in the formula bar you have to finish the
entry with CTRL+sHIFT+ENTER.
What does happen in your case? what do you see in the cell afterwards?

 

[Guest]

WOW...You are one impressive excel'er!!!. One PCUA seems
to be left out. It is the Number with the smallest value

 

[Guest]

It is actually returning every other match.

-----Original Message-----
WOW...You are one impressive excel'er!!!. One PCUA seems
to be left out. It is the Number with the smallest value have
to finish the A1
.

 

[Frank Kabel]

Hi
what is the exafct formula you have entered and in which cells you have
placed the formulas?

 

[Mark]

Exact Formula:
A1-model#
B1-=INDEX(Sheet1!$B1:$B$100,SMALL(IF(Sheet1!
$A$1:$A$100=$A$1,ROW(Sheet1!$A$1:$A$100)),ROW(1:1)))
B2-=INDEX(Sheet1!$B2:$B$100,SMALL(IF(Sheet1!
$A$1:$A$100=$A$1,ROW(Sheet1!$A$1:$A$100)),ROW(2:2)))

Etc.
The only part of the formula that changes is the index
From cell and the Row(x:x) at the end

 

[Frank Kabel]

Hi
sorry, my fault. I missed an '$' for the first range. In B1 use:
=INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!
$A$1:$A$100=$A$1,ROW(Sheet1!$A$1:$A$100)),ROW(1:1)))

and it should work. Cell B2 should read:
=INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!
$A$1:$A$100=$A$1,ROW(Sheet1!$A$1:$A$100)),ROW(2:2)))

only the ROW(1:1) part should change

 

[Guest]

Yeah I tried that but when I paste it down to the other
cells get the #NUM! error

 

[Frank Kabel]

Hi
you'll get the #NUM error after there're no more rows of data. To hide
these error use a formula like
=IF(ISERROR(INDEX(...)),"",INDEX(...))

and replace INDEX(...) with the formula I provided you