Data from Multiple columns with identical headings to be under the same heading

Originally Posted by martindwilson

like this? this is for four catagories
for say 6 cats change last bit of index to
(COLUMN(A1)-1)+ROW(A1)*6-5

Hi that works good but I do not want to limit it to just Column A to L. There are thousands of columns and many more column headers that are in the spreadsheet and I want to include other column headers too..Is there any way to have the cloumn and the amount of data it can handle go to infinity?

row to 4 columns-1.xlsx

Originally Posted by martindwilson

change the bit as i said at the end to where your columns start to repeat
so if there are 50 repeating columns
(COLUMN(A1)-1)+ROW(A1)*50-49

doesnt matter where you start put that formula in say k12 EXACTLY as it is do not change column(a1) or row (a1) leave it as it is just change the end bit
here's 50 repeating columns
note if your data starts in column a you can use
=INDEX($2:$2,(COLUMN(Z1)-1)+ROW(Z1)*50-49) where $2:$2 is row 2
else as last column is XFD
so say $d$2:$xfd$2

Hi Martin, I think I am almost there. What if I do not know how many categories (unique column names) are in the data but I just want to match the data to the column names? Also, how do I paste the fomulas down the rows? (See attached errors in Sheet2). Thank you so much for your time.