# Making Cell Values Equal to Cell, but drag formula opposite way

1. ## Making Cell Values Equal to Cell, but drag formula opposite way

Hey Excel Community,

I have a massive amount of data, and I need to make s formula to save time. Here is a sample I attached.

I need a formula that can do what's displayed that's not simply "transpose data"

Help is much appreciated.

Thanks,

2. ## Re: Making Cell Values Equal to Cell, but drag formula opposite way

It's not clear to me what is allowed or not allowed in your desired solution. My first thought is to simply use the TRANSPOSE() function (https://support.office.com/en-us/art...9-4b6dfac76027 ), but you said that you are not wanting to just transpose the data. =TRANSPOSE(A1:B5) array entered (cntrl-shift-enter)

I could see using and INDEX() function for this sort of thing (https://support.office.com/en-us/art...2-b56b061328bd ). The bulk of the work in using the INDEX() function is understanding the algorithm you want to use to fill the "row number" and "column number" arguments. For the simple transpose operation your example shows, you could use simple COLUMN() or COLUMNS() functions to get the row # and ROW() or ROWS() functions to get the column number. =INDEX(\$A\$1:\$B\$5,COLUMN(A1),ROW(A1)) copied down and across. This exact function works specifically for a simple transpose operation, but you could put more complex algorithms into row and column in order to perform other operations.

Will either of those work for you, or is there something I am missing?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1