+ Reply to Thread
Results 1 to 9 of 9

changing data in row/columns into only rows

  1. #1
    Registered User
    Join Date
    07-08-2007
    Posts
    6

    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

    \1

    \1

    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

    Thanks in advance for your help!!
    Last edited by brain615; 02-27-2010 at 01:00 PM.

  2. #2
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

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

    G'day Brain,

    The first link to your screenshot is broken.

    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.
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    07-08-2007
    Posts
    6

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

    Quote Originally Posted by JBeaucaire View Post
    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. #5
    Registered User
    Join Date
    07-08-2007
    Posts
    6

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

    Quote Originally Posted by ratcat View Post
    G'day Brain,

    The first link to your screenshot is broken.

    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.
    Thanks for the reply ratcat

    the link should be fixed.

    Here's my example:

    \1

    \1

    \1

    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
    Last edited by brain615; 02-27-2010 at 01:48 AM.

  6. #6
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    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.

    Please let the forum know if your still having problems

    Cheers

    RC
    Attached Files Attached Files

  7. #7
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: changing data in row/columns into only rows

    Hi See this eaxple book. HTH
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  8. #8
    Registered User
    Join Date
    07-08-2007
    Posts
    6

    Re: changing data in row/columns into only rows

    Quote Originally Posted by ratcat View Post
    G'day

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

    See attached.

    Please let the forum know if your still having problems

    Cheers

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

    Your code worked great. Thank you so much for your help!!

  9. #9
    Registered User
    Join Date
    07-08-2007
    Posts
    6

    Re: changing data in row/columns into only rows

    Quote Originally Posted by contaminated View Post
    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!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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