# changing data in row/columns into only rows

1. ## changing data in row/columns into only rows

First, thanks to anyone who is looking at this for your help

I am trying to write a formula, which I will use the screenshots below to help explain

http://i800.photobucket.com/albums/y...615/Sheet2.jpg

http://i800.photobucket.com/albums/y...n615/total.jpg

You can see in the sheet 'total' that I have rows of countries and columns of years. In the sheet 'sheet2' there is a row for each country and year. I need to transfer the values from 'total' to column 'CTERRTTL' in 'sheet2'.

For example, cell C2 of 'sheet2' should result in the value of cell B6 of 'total'; and C8 of 'sheet2' should result in the value of cell H6 of 'total'

Ideally, the formula will be written into sheet2 C2, and dragged down

I have tried experimenting with the match, index, and lookup formulas, but aren't that familiar with them and can't make it work

2. ## Re: Need help with forumla; changing data in row/columns into only rows

G'day Brain,

It will be easier to post a small example of dummy info with a manual info of before and after results. This will make the helpers of this forum easier to work with and achieve a quicker result for you.

3. ## Re: Need help with forumla; changing data in row/columns into only rows

Try this in C2, you'll have to adjust the INDEX range to cover the whole inner table, and the two MATCH ranges to cover the left column range and the top row range...

=INDEX(Total!\$B\$2:\$Z\$1000, MATCH(\$A2, Total!\$A\$2:\$A\$1000, 0), MATCH(\$B2, \$B\$1:\$Z\$1, 0))

Copy that down and across your table.

4. ## Re: Need help with forumla; changing data in row/columns into only rows

Originally Posted by JBeaucaire
Try this in C2, you'll have to adjust the INDEX range to cover the whole inner table, and the two MATCH ranges to cover the left column range and the top row range...

=INDEX(Total!\$B\$2:\$Z\$1000, MATCH(\$A2, Total!\$A\$2:\$A\$1000, 0), MATCH(\$B2, \$B\$1:\$Z\$1, 0))

Copy that down and across your table.
I tried this and got an "#N/A" result

Here's how I edited your code to match my table size,

=INDEX(total!\$B\$2:\$AC\$126, MATCH(\$A2, total!\$A\$2:\$A\$126, 0), MATCH(\$B1, total!\$B\$1:\$AC\$2, 0))

I should also mention that the sheet 'sheet2' contains some countries which the 'total' sheet does not. In those cases (where there is no match to country name) the formula result should (ideally) be zero

5. ## Re: Need help with forumla; changing data in row/columns into only rows

Originally Posted by ratcat
G'day Brain,

It will be easier to post a small example of dummy info with a manual info of before and after results. This will make the helpers of this forum easier to work with and achieve a quicker result for you.

Here's my example:

http://i800.photobucket.com/albums/y...totalsheet.jpg

http://i800.photobucket.com/albums/y...615/before.jpg

http://i800.photobucket.com/albums/y...n615/after.jpg

The first screenshot is the source of the data the formula will pull from

The second screenshot is where the formula is created

The third screenshot is the results when the formula is done. Because there was no 'Canada' country in the source data, its result is zero

Thanks again for the help; let me know if its still not clear

6. ## Re: changing data in row/columns into only rows

G'day

Sorry my hangover is still bothering me. With supplying info I meant uploading a workbook example. Doesn't matter anywho.

See attached.

Cheers

RC

7. ## Re: changing data in row/columns into only rows

Hi See this eaxple book. HTH

8. ## Re: changing data in row/columns into only rows

Originally Posted by ratcat
G'day

Sorry my hangover is still bothering me. With supplying info I meant uploading a workbook example. Doesn't matter anywho.

See attached.

Cheers

RC
Sorry for my misunderstanding and forum inexperience. In the future I will upload examples

9. ## Re: changing data in row/columns into only rows

Originally Posted by contaminated
Hi See this eaxple book. HTH
I tried using ratcats solution first (since he posted first) and it worked - so I didn't look at yours. However I still want to thank you for your reply and help. so -- THANK YOU!!

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