+ Reply to Thread
Results 1 to 12 of 12

need to arrange column wise data to row wise with monthly wise

  1. #1
    Registered User
    Join Date
    07-02-2016
    Location
    kolkata,india
    MS-Off Ver
    2008
    Posts
    6

    need to arrange column wise data to row wise with monthly wise

    i have data like below
    CONS_NUM BILL_UNITS BC_CD
    0200501200 101 200608
    0200501200 58 200609
    0200501200 100 200610
    0200501200 75 200611
    0200501200 32 200612
    0200501200 32 200701
    0200501200 66 200702
    0200501200 61 200703
    0200501200 58 200704
    0200501200 54 200705
    0200501200 92 200706
    0200501200 77 200707
    0200501200 68 200708
    0200501200 68 200709
    0200501200 70 200710
    0200501200 98 200711
    0200501200 36 200712
    0200501200 28 200801
    0200501200 36 200802
    0200501200 43 200803
    0200501200 67 200804
    0200501200 51 200805
    0200501200 99 200806
    0200501200 78 200807
    0200501200 81 200808
    0200501200 84 200809
    0200501200 77 200810
    0200501200 85 200811
    0200501200 32 200812

    need to arrange like below
    CONS_NUM
    0200501200
    jan feb mar apr may jun jul aug sep oct nov dec
    101 58 100 75 32
    32 66 61 58 54 92 77 68 68 70 98 36
    28 36 43 67 51 99 78 81 84 77 85 32


    could you please help me .. manually it's taking so much time

    Thanks in advance.. plz help me

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,426

    Re: need to arrange column wise data to row wise with monthly wise

    It would take us a long time to enter that data before being able to work on possible solutions.

    It would help if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Pete

  3. #3
    Registered User
    Join Date
    07-02-2016
    Location
    kolkata,india
    MS-Off Ver
    2008
    Posts
    6

    Re: need to arrange column wise data to row wise with monthly wise

    i have attached the data. please suggest.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,426

    Re: need to arrange column wise data to row wise with monthly wise

    In the attached file I have listed the 3 Cons_Num values in column E - this is the data source for the data validation drop-down in G2 (coloured yellow), where you can select which of those values you want to display the summary results for. I've set up a summary table of years and months, and used this array* formula in I3:

    =IFERROR(INDEX($B$2:$B$321,MATCH(1,($A$2:$A$321=$G$2)*($C$2:$C$321=--($H3&TEXT(COLUMNS($I:I),"00"))),0)),"")

    which can then be copied across and down to fill the summary table.

    *NOTE an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual < Enter >.

    Now you can use the drop-down in G2 to select another value and see the values in the summary table change accordingly.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-02-2016
    Location
    kolkata,india
    MS-Off Ver
    2008
    Posts
    6

    Re: need to arrange column wise data to row wise with monthly wise

    Thanks a lot Pete..
    if i need to add more consumers data then how could i add those data so that it would be populated in drop down and i can see the summary results for all consumers.
    I have attached full data sheet if you can apply the same on full data.
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,426

    Re: need to arrange column wise data to row wise with monthly wise

    I'm not going to set it up again for you from scratch. All you need to do is to list your individual Cons_Num values in column E below the three that are already there, and with your cursor on G2 select Data | Data Validation and change the Source Range to suit your longer list.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

    EDIT: once you have added you data to columns A:C you will need to change the ranges that the formulae in the summary table act upon. The easiest way to do that is to select all the cells with the formulae in then do CTRL-H (Find & Replace) to change $321 to whatever your last row now is.

    Hope this helps.

    Pete
    Last edited by Pete_UK; 07-02-2016 at 02:45 PM.

  7. #7
    Registered User
    Join Date
    07-02-2016
    Location
    kolkata,india
    MS-Off Ver
    2008
    Posts
    6

    Re: need to arrange column wise data to row wise with monthly wise

    Sorry for inconvenience Pete.. i have added more data and added those in cons_nums in drop down, changed the range as well as you suggested but it's not working for added data..
    could you please help.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,426

    Re: need to arrange column wise data to row wise with monthly wise

    Did you change the Source List range for the data validation in cell G2?

    Pete

  9. #9
    Registered User
    Join Date
    07-02-2016
    Location
    kolkata,india
    MS-Off Ver
    2008
    Posts
    6

    Re: need to arrange column wise data to row wise with monthly wise

    hi pete, i made the changes in G2 as well.. in data validation i have changed the cell references in the Source box of data validation that's why the new value appeared in drop down.
    i have attached the sheet in this message where i have added one more consumer data. please verify and suggest.
    Attached Files Attached Files

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,426

    Re: need to arrange column wise data to row wise with monthly wise

    You have missed the leading zero from the value in cell E5 - it should contain this: 0215900400

    Then when you select this in G2 you will see the values.

    So, copy all your data into columns A to C,
    Put a list of individual Cons_Nums in column E in the same format that they occur in your data,
    Adjust the Source List in the data Validation for G2, and
    Use Find & Replace (CTRL-H) to change the data range to suit the extent of your data.

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    07-02-2016
    Location
    kolkata,india
    MS-Off Ver
    2008
    Posts
    6

    Re: need to arrange column wise data to row wise with monthly wise

    Thanks a lot it's working fine now.. great job .. it saved my time ....

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,426

    Re: need to arrange column wise data to row wise with monthly wise

    Glad to hear it - if I had done it all for you then you wouldn't have learned very much.

    Pete

+ 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. [SOLVED] Transpose Column wise data into Row wise w.r.t using macro
    By thara.p24 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-21-2015, 05:17 AM
  2. vba code for automating branch wise day wise amount to summary file
    By pranithpm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2014, 04:18 AM
  3. Excel code to represent data from row wise to column wise
    By sreejeshc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-12-2013, 03:45 AM
  4. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  5. Replies: 4
    Last Post: 08-01-2012, 09:50 AM
  6. [SOLVED] Data row wise, formula column wise
    By Fred Smith in forum Excel General
    Replies: 4
    Last Post: 12-09-2005, 11:55 AM
  7. [SOLVED] Delete row wise duplicates & colomun wise simultaneously excel
    By Dipankar in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-06-2005, 09:05 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