+ Reply to Thread
Results 1 to 11 of 11

Deriving row data depending on the column chosen

  1. #1
    Registered User
    Join Date
    04-08-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Deriving row data depending on the column chosen

    Hi,

    I work in the modern trade channel and needed a help for this.there is a customer sales database that we have.


    Customer Name Product Category Unique SKU Desc
    Tesco Fruit Drinks Apple juice 250 ml


    and the month wise sales data follows.

    I have this data across 20 customer names and for 200 unique skus.
    If I choose the month as July 2017 I want the L3M ( last three months) namely June,May, April of 2017 to be averaged and displayed and LY( last year same mntH) July 2016 data to be displayed for the category chosen.
    Is there any formula or an excel macro that can help me do this? Thanks in advance.

    If needed aI can share a sample excel dump as well

    Regards

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Deriving row data depending on the column chosen

    Hi, welcome to the forum

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Deriving row data depending on the column chosen

    Hi,

    Welcome to the Forum.

    Can you post a sample file in excel format without any sensitive/confidential info?

    See the following URL for help on how to upload a file.
    http://www.excelforum.com/members/da...ch-a-file.html

  4. #4
    Registered User
    Join Date
    04-08-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Deriving row data depending on the column chosen

    thanks so much for the suggestions Ford and cbatrody.

    Really appreciate the same.

    I have sent a sample dummy data. I also outlined how my results should look like depending on what I click/ enter as my choice.

    Regards
    Attached Files Attached Files

  5. #5
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Deriving row data depending on the column chosen

    Hi,

    Your dates/months in the header row are not in date format. So I had to resort to a lengthy formula:

    Try the following in G12:

    Please Login or Register  to view this content.
    In H12:

    Please Login or Register  to view this content.
    both array entered, confirmed by pressing CTRL+SHIFT+ENTER

    copy these formulas in G18 & H18

    see the attached file.
    Attached Files Attached Files

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Deriving row data depending on the column chosen

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    2
    Customer Name Product Category Unique SKU Desc APR'16 MAY'16 JUN'16 JUL'16 AUG'16 SEP'16 OCT'16 NOV'16 DEC'16 JAN'17 FEB'17 MAR'17 full year 2016-17 APR'17 MAY'17 JUN'17 JUL'17
    3
    Tesco Fruit Drinks Apple juice 250 ml 11.3 5.5 6.6 2.9 2.4 0.8 1.3 0.2 0.9 -0.1 2.8 5.4 40.0 3.1 2.2 -0.1
    4
    Walmart Fruit Drinks Pineapple juiice 1 L -0.1 3.1 4.9 4.4 2.7 1.4 1.7 1.1 1.2 0.9 1.3 2.4 25.0 2.1 1.3 0.2
    5
    Metro Biscuits Plain biscuit 250 gm 41.5 30.9 21.8 9.1 10.2 6.2 8.7 5.2 5.1 7.3 15.7 21.0 182.7 19.6 18.1 5.3
    6
    7
    8
    Month Unique SKU Desc L3M LY same month
    9
    JUL'17 Apple juice 250 ml 1.7 2.9


    Try

    F9
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    G9
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Check the attached file.
    Attached Files Attached Files
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Deriving row data depending on the column chosen

    Quote Originally Posted by shukla.ankur281190 View Post
    Try

    F9
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    G9
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Check the attached file.
    I get "#VALUE!" error on both F9 & G9 when I change the 'Month' from JUL'17 to APR'17 in D9!

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Deriving row data depending on the column chosen

    Did you check my attachment ? I think it is working perfectly and need only to remove total column.

  9. #9
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Deriving row data depending on the column chosen

    Quote Originally Posted by shukla.ankur281190 View Post
    Did you check my attachment ? I think it is working perfectly and need only to remove total column.
    I did, I don't know if it is something to do with the Office version on my Laptop (I am on Professional Plus 2016).
    Attached Images Attached Images
    Last edited by cbatrody; 09-20-2017 at 04:52 AM.

  10. #10
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Deriving row data depending on the column chosen

    Ok I found problem, problem is with your system date format which would be may Be in DD/MM/YYY or DD-MM-YYYY.

    Try

    F9
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    G9
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    My System date format I have set DD.MM.YYYY that is why it was not working.

    Hope it helps you.,

  11. #11
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Deriving row data depending on the column chosen

    Quote Originally Posted by shukla.ankur281190 View Post
    Ok I found problem, problem is with your system date format which would be may Be in DD/MM/YYY or DD-MM-YYYY.
    No, still doesn't work. Thanks anyways, never mind as long as it works for the OP.


    @findshals,

    If you can get rid of the "full year total" column, try the following in F9:

    Please Login or Register  to view this content.

    In G9:
    Please Login or Register  to view this content.
    both array entered - CTRL+SHIFT+ENTER to confirm.

    See the attached file.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Show content depending on whats chosen in dropdown
    By Phyxiusx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2017, 05:13 AM
  2. [SOLVED] Total count of employees depending on chosen manager
    By Harvey Raphael in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-17-2014, 03:44 AM
  3. Replies: 1
    Last Post: 02-21-2014, 05:33 PM
  4. Replies: 7
    Last Post: 06-25-2013, 06:59 AM
  5. Adding a value depending on Drop-down-list chosen value
    By freaken00 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2013, 05:24 AM
  6. Copying data depending on chosen date to another worksheet
    By Bishonen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2012, 08:22 AM
  7. Automatically select data depending on what's chosen in a drop-down list
    By Cormentia in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-24-2011, 11:55 AM

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