Hello,
Can someone help clarify how I can consolidate the first four sheets of data in attached workbook by copying and pasting them into the "Consolidated" sheet directly below each data set? I can not seem to figure it out because the number of rows in each sheets (Name1, Name2, Name3, Name4) can vary so that would change the particular row you are pasting the data into on the "Consolidated" sheet?
Thank you!
You have not provided any sample data to demonstrate. Please provide same with sample data so that a realistic solution can be presented.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Please see attached. 7 rows on first tab, 23 rows on second tab, 11 rows on third tab, 7 rows on fourth tab. All should be copy and pasted below each other on Consolidated tab so it can then be pivoted.
Total Consolidated data would consist of 48 rows in this example
Are you still using Excel 2016?
With 365 you can use this:
Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
oh my gosh.. that is amazing. Did not realize you could do this with a formula. Thank you!
With Power Query, Append the tables.
1. Highlight each range and convert to a Table.
2. Apply the Mcode as shown in the Power Query Editor.
3. Close and Load
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
Please Login or Register to view this content.
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Does the formula need to be adjusted if I put actual names in for the tabs and not using "Name1" through "Name4"?
I typed in names for those sheets as Ryan, Tim, Annie, Kelsey and the formula does not work now
Ok, two things:Originally Posted by Hoover5896
1. Name1 needs to be replaced by the name on the first (leftmost sheet) name. In this case, Ryan. Name4 needs to be replaced by the last (rightmost before the Consolidated sheet. In this case, Lucy.
2. If the sheet/tab names have spaces, then you need to use single quotes to surround the sheet names.
For example:Formula:Please Login or Register to view this content.
Last edited by TMS; 05-14-2024 at 04:01 PM.
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks