+ Reply to Thread
Results 1 to 26 of 26

transpose data and summing for all customers horizontally

  1. #1
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    transpose data and summing for all customers horizontally

    hi
    I need help by search way to transfer data from vertical to horizonal , and summing all the values for all values are existed in COL (UPAID) & (PAID ) then should subtracting between the columns for each customer ,and each month so when subtract total COL (UNPAID ) from col(PAID) and the COL (UNPAID) is bigger than COL (PAID) then should show the result contain for bracket and red color for instance in customer1 in month JAN as show in sheet result . so I put the result in sheet result how should be the values for each customer and each month how calculate the values .
    not: every time increase customer and sheets so this is a simple sample to understand me .
    any thing is not clear , please inform me, please
    Attached Files Attached Files
    Last edited by MKLAQ; 07-21-2021 at 05:19 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,046

    Re: transpose data and summing for all customers horizontally

    Where in the worlds is Latherer???

    Are you interested in a PowerQuery solution?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    thanks I want by vba

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,046

    Re: transpose data and summing for all customers horizontally

    OK.

    Administrative Note:

    Welcome to the forum.

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Thank you for helping us to help you.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    Please Login or Register  to view this content.
    Last edited by jindon; 07-21-2021 at 08:12 AM.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,261

    Re: transpose data and summing for all customers horizontally

    Macro code
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 07-21-2021 at 08:18 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    @ jindon thanks but it gives error mismatch in this line
    HTML Code: 

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    Working, so no idea.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    @kvsrinivasamurthy thanks but it should show bracket for any minus value under the month and if it's possible create all of data in sheet result without write any thing

  10. #10
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    @jindon you're right I no know where I made mistake
    your code works very good just I would hide the formula in last column (BALANCE) just show as value

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    You are always like that...
    You should have mentioned in you opening post.
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    @jindon I note it doesn't show bracket the value under each month and column BALANCE instead of minus . can you fix it, please ?
    Last edited by MKLAQ; 07-21-2021 at 09:06 AM.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    Upload a workbook showing EXACTLY how you want.

  14. #14
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    as you see any value gives minus should be into bracket
    Attached Files Attached Files
    Last edited by MKLAQ; 07-21-2021 at 09:19 AM.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    I said,...........
    Quote Originally Posted by jindon View Post
    Upload a workbook showing EXACTLY how you want.
    If you don't want to upload your EXACT expected result, just forget it.

  16. #16
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    actually I did it .after you run code you will find the differnce . see what i highlighted cells and when run the code you will understand the differnce.
    I updated the file in post 14
    Last edited by MKLAQ; 07-21-2021 at 09:21 AM.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    Sorry, but no idea about what you are talking, so wait for someone else.

  18. #18
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    thanks Jindon . what I'm asking is minor , even you don't understand me but you've solved my question .thanks so much for your assistance and cooperation

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    Why can't you upload a mock up workbook, don't try to explain in WORDS to avoid any confusion.

    What bracket? red? black? with or without - sign etc.

    I don't want to do a guess work.

  20. #20
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    actually I do my best but I find difficaultly to expleain clearly so I thought when you see my file in post#14 you will understand what i mean when as you code it will show he value - 8920 , but i just want converting to (8920) any value is minus should red color and locates between bracket . actually i searched in the interntet about this idea I'v found this
    HTML Code: 
    and add to your code yes it succedds but not completly it will show black color not red .

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    How can any one understand what you are trying to say just highlighting the cell by yellow color????
    Please Login or Register  to view this content.
    Last edited by jindon; 07-21-2021 at 09:55 AM.

  22. #22
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    thanks
    may you see file , please ? there are some cells doesn't highlight by red as last column
    Attached Files Attached Files

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,145

    Re: transpose data and summing for all customers horizontally

    Delete the strange line of yours
    Please Login or Register  to view this content.

  24. #24
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    first sorry if I caused you some confusion . this works excellantly .
    thanks again for every thing .

  25. #25
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,261

    Re: transpose data and summing for all customers horizontally

    Modified code
    Please Login or Register  to view this content.
    Last edited by kvsrinivasamurthy; 07-21-2021 at 11:14 AM.

  26. #26
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    281

    Re: transpose data and summing for all customers horizontally

    @kvsrinivasamurthy well done ! that's great
    thanks again

+ 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. Replies: 2
    Last Post: 08-30-2019, 11:29 AM
  2. [SOLVED] Summing Horizontally
    By Excel-Access in forum Excel General
    Replies: 8
    Last Post: 06-16-2019, 09:06 AM
  3. [SOLVED] Summing up horizontally
    By yiyi2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2018, 11:45 PM
  4. [SOLVED] transpose and consolidate data horizontally to vertically
    By Excel_learner in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2014, 03:50 PM
  5. [SOLVED] Macro to transpose vertical data horizontally from one sheet to another
    By Scott_88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2013, 06:27 AM
  6. Transpose every 220 rows horizontally
    By hkayani08 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-13-2012, 05:59 AM
  7. summing balance for customers
    By jgomez in forum Access Tables & Databases
    Replies: 14
    Last Post: 10-07-2011, 02:34 PM

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