+ Reply to Thread
Results 1 to 6 of 6

Help for v-lookup approach instead of manually cell linking approach - Excel 2010

  1. #1
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    104

    Help for v-lookup approach instead of manually cell linking approach - Excel 2010

    Hi Excel Forum,

    1.In the attached Excel file, Table 1 mimics my original large data set
    2.The original dataset is having 25 data points under variable Quarter; and 15 villages under each Quarter

    Background:
    I wanted to make a separate chart for each village, showing how pre and post income is trending with Quarters

    I have just created a chart for first 2 villages, namely AA_1 and AA_2 (pl see Chart 1 and 2 in the attached)

    To create this chart, I should pre-process data like in Table 2 and Table 3.

    Question:
    Could anyone help me to create the pre-processed two tables using v-lookup approach. You would see I have created table2 and 3 using a very clumsy manual cell linking approach.

    Thank you

    Mirisage
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: Help for v-lookup approach instead of manually cell linking approach - Excel 2010

    here you go:
    post_this.xlsx

    to get it to work properly, I added a helper column to your table 1 with the formula =A5&B5 in cell C5, then dragged it down then used the vlookup formulas in tables 2 and 3

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,327

    Re: Help for v-lookup approach instead of manually cell linking approach - Excel 2010

    Try this approach. In E5, copied down...
    =A5&B5
    This will combine the date and village
    Then use this in G5, copied down and across...
    =INDEX($A$4:$E$16,MATCH($F5&"AA_1",$E$4:$E$16,0),MATCH(G$4,$A$4:$E$4,0))
    If you can put the "village" ref in its own cell, you could reference it instead of hard-coding it
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    05-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Help for v-lookup approach instead of manually cell linking approach - Excel 2010

    Did you try Pivot Table Chart

    Select range A4:D16 >> Go to Insert Menu Tab >> Under Pivot Table Item click on the drop down and Select Pivot Table Chart

    In the Report Filter Select Village >> Axis Field will be Quarter >> and the Values Field will be Pre-Income and Post Income.

    Once the Pivot Chart is generated...either on the Pivot Table or the Pivot Chart select the filter for the appropriate villages.

    Note that you might need to fill in the quarters on all rows in column A

  5. #5
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    104

    Re: Help for v-lookup approach instead of manually cell linking approach - Excel 2010

    Hi scott,
    Your suggestion is great, thank you very much.

    Hi FDibbins,
    Your code works very well. Thank you very much.

    Hi jubiesxl,
    Pivot Table Chart is the exactly what I was trying to ask in my next round of post. Now you have answered it, thank you very much.

    Thanks again to every one of you!
    Mirisage

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,327

    Re: Help for v-lookup approach instead of manually cell linking approach - Excel 2010

    Happy to help and thanks for the feedback

+ 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