Help in display of data

Hi Team,

I am new to this forum . i got a requirement to display the data currently displayed in vertical column to horizontal rows(around 100k)

Example.
Pin:144396
User EM351879
Pin:144396
User EM351879
Pin:125426
User JKN34798

The expected result is
Pin User
144396 EM351879
144396 EM351879.
......

Earlier in this forum there was a solution given for a similar problem as below,
=SUBSTITUTE(INDEX(A$1:A$11,(ROW())*2-1,1),"pointsBalance:","")
=SUBSTITUTE(INDEX(A$1:A$11,(ROW())*2,1),"User ","")

Attempted the same by substituting the above formula but was getting Invalid Cell reference error after rown no 5000.Please guide how to resolve this.