Transpose data in Excel

[Katchap902]

Hi Everyone,
I have some complex data in a table I want to transpose in excel. In the table below I want take the 'element' column and transpose it so each individual element is listed as a column number per sample tag. The first image is how the data is currently presented and the second image is how I want it displayed . I have 100000's of records to do this for.....

 

[Katchap902]

Hi, Another quick question- when I try to run the VBA with all records (118,000) I keep getting the error below. However if I break it onto ~20,000 parcels I do not get the error. Any insight?

 

[BSALV]

you're welcome.

 

[BSALV]

transpose gives problems above 65.000, but index, no ???
if you replace the yellow line with the same, but hardcoded, is it okay then ?

s = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5)

 

[Katchap902]

transpose gives problems above 65.000, but index, no ???
if you replace the yellow line with the same, but hardcoded, is it okay then ?

s = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5)

Wow, yes that worked a dream! Thank you so much, you saved the day