Convert data from columns to rows with M code

You can put this formula in D3 of the Input Data sheet:

=IF(B3="","",IF(COUNTIF(B$3:B3,B3)=1,MAX(D$2:D2)+1,MAX(D$2:D2)&"_"&COUNTIF(B$3:B3,B3)))

and then copy down to the bottom of your data (or beyond, if you wish). In the other sheet you can use these formulae:

B3: =IFERROR(INDEX('Input Data'!B:B,MATCH(ROWS($1:1),'Input Data'!$D:$D,0)),"")

C3: =IF(B3="","","|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1),'Input Data'!$D:$D,0))&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_2",'Input Data'!$D:$D,0)),"")&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_3",'Input Data'!$D:$D,0)),"")&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_4",'Input Data'!$D:$D,0)),"")&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_5",'Input Data'!$D:$D,0)),""))

That might look a bit daunting, but it is more straightforward if I show it like this:

=IF(B3="","","|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1),'Input Data'!$D:$D,0))
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_2",'Input Data'!$D:$D,0)),"")
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_3",'Input Data'!$D:$D,0)),"")
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_4",'Input Data'!$D:$D,0)),"")
&IFERROR("|"&INDEX('Input Data'!$C:$C,MATCH(ROWS($1:1)&"_5",'Input Data'!$D:$D,0)),""))

This can accommodate up to 5 items joined together with the | character separating them, but it is quite easy to add more terms if required. If you had a later version of Excel, you could use the TEXTJOIN function to simplify this.

Copy both formulae down as far as you need them.

Hope this helps.

Pete