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.

Are you able to use PowerQuery?

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

Unfortunately we donot have privilege to download any softwares. Looking for any other solutions as this seems to be urgent in nature. Thank you

No problem

Have a nice day

are you willing to do some manual labour?


in B2
=RIGHT(A1,LEN(A1)-4)

in C2
=RIGHT(A2,LEN(A2)-6)

in D2
=ROW()


select 6 cells
B1:D2
copy to clipboard


select from B1 to end of sheet (3 columns)
paste from clipboard


copy to clipboard
go elsewhere
paste special:Values

sort by last column
then drop those empty rows at end
(and drop the last column)

Assuming you start the formulas in row 2 with row 1 being headers, you could use
=SUBSTITUTE(INDEX(A:A,2*ROW()-3),"Pin:","")
=SUBSTITUTE(INDEX(A:A,2*ROW()-2),"User ","")