+ Reply to Thread
Results 1 to 17 of 17

Reorganise data from a financial report

  1. #1
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Reorganise data from a financial report

    I have a set of data on Sheet1 (attached).

    I would like to rearrange this data so that it appears as in the Expected Result sheet.

    This needs to run until the last cell containing data in Sheet1.

    Any questions please ask.
    Attached Files Attached Files

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    Here are the step as I see them;
    Step 1 - Find the Next Company Name - next to cell containing "Total for:" - copy cell contents to Result sheet
    Step 2 - Find the first centre - next to cell containing "Total for centre:" - copy cell contents to Result sheet
    Step 3 - Copy all the data from Columns D to F to the results sheet
    Step 4 - Find the next centre (if any) and repeat Step 3
    Step 5 - Find the next Company (if any) and repeat Steps 2 & 3

    Capture.JPG
    Last edited by kersplash; 07-16-2018 at 10:29 PM.

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Reorganise data from a financial report

    maybe try PowerQuery
    Attached Files Attached Files

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    Hi Sandy.

    Thanks for the Power Query solution.

    Does this allow for extension of the source data to include more companies?

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Reorganise data from a financial report

    Manual intervention is needed due to the Custom Column: Company1, Company2, Company3 ... etc
    there is no any condition to identify each company... but I think after looked again there Company could be taken from Total

  6. #6
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    There could be 1000 companies so that wouldn't be practical. Thanks anyway.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Reorganise data from a financial report

    You are too fast Post was updated but Ok, no problem

    Have a nice day

  8. #8
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    I just had a thought....

    If I extract the list of company names to a separate table. Could this be used in Power Query to do the job?

    ie.
    Companies
    Company 1
    Company 2
    Company 3
    Company 4
    Company 5, etc....

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Reorganise data from a financial report

    Last row for each company is (column1): Total for: so new column "Company" ==>> if column1 equal "Total for:" show volumn2 else null then Fill Up
    but problem is with additional rows on top and at the bottom. You need to define how many rows to remove.
    My example is very simple because I would like to show it's possible without VBA

    Maybe everything could be automated but not at 4:30 AM

  10. #10
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    Haha. Thanks anyway Sandy.

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Reorganise data from a financial report

    Check this , much simpler and automated (ambition )

    forget about previous attachment - it was overcomplicated

    edit: if the source data structure does not change then everything should dance and sing

    add a fourth company to the source data then refresh the query table and see if it will work
    Attached Files Attached Files
    Last edited by sandy666; 07-16-2018 at 11:01 PM.

  12. #12
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    Mate, I don't know if I got the latest attachment, but I extended my data to 380 companies (60,000 rows), and without double checking it looks like it did the job!!!!

    You can go to bed knowing you've made me a very happy person.

    Now to learn how you did it.

    Thanks Sandy.

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

    Re: Reorganise data from a financial report

    VBA
    Please Login or Register  to view this content.
    edit
    I extended my data to 380 companies (60,000 rows)
    Last edited by jindon; 07-16-2018 at 11:41 PM.

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Reorganise data from a financial report

    Happy it works for you

    Cya next time

  15. #15
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    Quote Originally Posted by jindon View Post
    VBA
    Thanks Jindon,

    Excellent, now I have 2 things I need to work out how you did.

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

    Re: Reorganise data from a financial report

    You are welcome and thanks for the rep.

    Very simple.

    1) Find the cells that have "Total for:" in Column A, and generate the array for their row references.
    If you step through, you will find Array "x" holds the row that have "Total for:".

    2) Loop x and find Numerical cells/areas(dates in this case) in between x(i) & x(i + 1)
    3) Loop each areas in 2) and put each result in an array "a" for output.

  17. #17
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Reorganise data from a financial report

    Quote Originally Posted by jindon View Post
    Very simple.
    Beautiful. I've added your explanation to my code once my brain stops hurting.

+ 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. Formula needed to reorganise data
    By AussieExcelGuy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2018, 03:11 AM
  2. Financial report
    By Lovefinance in forum Excel General
    Replies: 0
    Last Post: 10-06-2014, 06:38 AM
  3. [SOLVED] Delete rows, then reorganise remaining data
    By Steve_123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2014, 04:08 AM
  4. [SOLVED] Transpose and reorganise data
    By MichaelMejer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-07-2013, 07:06 AM
  5. [SOLVED] Reorganise data to present it
    By sserge in forum Excel General
    Replies: 2
    Last Post: 05-15-2012, 09:29 PM
  6. Help please. How do I automatically reorganise a data table?
    By sherlock99 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2011, 11:08 AM
  7. [SOLVED] Using F9 Financial Report Program
    By Bob in forum Excel General
    Replies: 0
    Last Post: 10-11-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