Apologies for late response, I've been in deepest Galloway, where moble internet signals are replaced with biting midges ...
See the attached workbook.
1/. In A2
=IF(MAX($A$1:$A1)=MAX(Sheet1!$A:$A),"",SMALL(Sheet1!$A:$A,1+COUNTIF(Sheet1!A:A,"<="&N($A1))))
Drag/Fill Down as required.
This will return your "S No"s, without duplicates and blanks.
2/. B2 is unchanged
3/. In C2
=IF(OR($B2="",ROUNDUP((COLUMNS($C$1:C$1)/6),0)>COUNTIF(Sheet1!$B:$B,$B2)),"",INDEX(Sheet1!$C:$H,MATCH($B2,Sheet1!$B:$B,0)+ROUNDUP((COLUMNS($C$1:C$1)/6),0)-1,IF(MOD(COLUMNS($C$1:C$1),6)=0,6,MOD(COLUMNS($C$1:C$1),6))))
Drag Down as required then Across until all rows in the column return blanks.
The changes are (Marked in red)
a/. Increase the number of columns to be read from Sheet1!$C:$E (3 columns) to say, Sheet1!$C:$H (6 columns)
b/. Change all the values 3 to 6, or the number of columns you need to read.
Bookmarks