Leo Heuser wrote...
One way:

In Sheet2 B1:

=3DIF(COLUMN()-COLUMN($B1)+1<=3DCOUNTIF(Sheet1!$A$1:$A$100,$A1),
OFFSET(Sheet1!$B$1,MIN(IF((Sheet1!$A$1:$A$100=3D$ A1)*(COUNTIF(
OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$100)),$A1 )=3DCOLUMN()-
COLUMN($B1)+1),ROW(Sheet1!$A$1:$A$100)-1)),0),"-")
..=2E.

Solves the OP's problem as stated, but not generally. This formula
relies on the source range beginning in row 1.

Small point: Domenic's COLUMNS($B1:B1) is a much nicer way to generate
a sequence as a horizontal array than COLUMN()-COLUMN($B1)+1. Wish I'd
'a thought of it.

More significantly,

MIN(IF((Sheet1!$A$1:$A$100=3D$A1)*(COUNTIF(OFFSET( Sheet1!$A$1,,,
ROW(Sheet1!$A$1:$A$100)),$A1)=3DCOLUMN()-COLUMN($B1)+1),
ROW(Sheet1!$A$1:$A$100)-1))

could be shortened to

MIN(IF(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$ 1:$A$100)),$A1)
=3DCOLUMN()-COLUMN($B1)+1,ROW(Sheet1!$A$1:$A$100)-1))

since the outermost IF condition makes the (Sheet1!$A$1:$A$100=3D$A1)
condition unnecessary.

Finally, efficiency. The final expression above involves MIN iterating
over an array derived from calling COUNTIF on 100 derived ranges of
size varying from 1 to 100 rows. The COUNTIF call is O(N^2). Domenic's
alternative,

SMALL(IF(Sheet1!$A=AD$1:$A$100=3D$A1,ROW(Sheet1!$A $1:=AD$A$100)
-ROW(Sheet1!$A$1)+1),COLUMNS($B1:B1)=AD))

involves implicit sorting of a 100 entry array, which is O(N*log(N)) if
my light testing of SMALL and LARGE is accurate, in which case they use
Quicksort.

So, bundling all the ideas together, and using the defined name Tbl to
refer to the source data range on the other worksheet, try the array
formula

=3DIF(COLUMNS($B1:B1)<=3DCOUNTIF(INDEX(Tbl,0,1),$A 1),
OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=3D$A1,ROW(Tbl)-CELL("Row",Tbl)),
COLUMNS($B1:B1)),1,1,1),"")

Final consideration: Leo's formula requires 7 levels of function calls.
The final formula above requires 6 levels of function calls.