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.
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.
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.
maybe try PowerQuery
Hi Sandy.
Thanks for the Power Query solution.
Does this allow for extension of the source data to include more companies?
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
There could be 1000 companies so that wouldn't be practical. Thanks anyway.
You are too fast Post was updated but Ok, no problem
Have a nice day
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....
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
Haha. Thanks anyway Sandy.
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
Last edited by sandy666; 07-16-2018 at 11:01 PM.
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.
VBA
editPlease Login or Register to view this content.
I extended my data to 380 companies (60,000 rows)
Last edited by jindon; 07-16-2018 at 11:41 PM.
Happy it works for you
Cya next time
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks