Filling in a column based on value in other column

[Fyrefox]

Hi all,

It sounds easy but couldn't find the solution in excel (preferably without macro's).
I have two sheets.
Sheet 1 contains a list.
In sheet 2 I have to fill in the last column, based on the list in sheet 1 (preferably using a formula). First column is just 5 times A,B,C,D.

In sheet 1

A	2
A	1
B	7
B	4
B	2
C	9
D	8
D	12
D	6
D	3

<tbody>
</tbody>

<tbody>
</tbody>

So, in sheet 2 I have to fill in:

A	2
A	1
A	No data
A	No data
A	No data
B	7
B	4
B	2
B	No data
B	No data
C	9
C	No data
C	No data
C	No data
C	No data
D	8
D	12
D	6
D	3
D	No data

<tbody>
</tbody>

Thank you very much!!

Kind regards,
K.

 

[Fluff]

How about
=IFERROR(INDEX(Sheet1!$B$2:$B$11,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)/(Sheet1!$A$2:$A$11=A2),COUNTIF($A$2:A2,A2))),"No data")

 

[Fyrefox]

How about
=IFERROR(INDEX(Sheet1!$B$2:$B$11,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)/(Sheet1!$A$2:$A$11=A2),COUNTIF($A$2:A2,A2))),"No data")

A real genius, thank you very very much!!

 

[Fluff]

Glad to help & thanks for the feedback