+ Reply to Thread
Results 1 to 9 of 9

Consolidate multiple tabs with different headers (not using power query)

  1. #1
    Registered User
    Join Date
    02-08-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Consolidate multiple tabs with different headers (not using power query)

    Hello,

    I have a file with 4 different tabs some of which have the same headers but others have different headers. I'm looking for a formula that can consolidate on 1 summary tab without having to use Power Query (system issues not allowing it to work properly). I'd also like the summary tab to reference the tab name from which the data comes from. I've attached a sample worksheet showing the end goal. I've tried using aggregate formulas but I'm not able to get it to work. I'm open to a vba as well but formula would be best.

    Thanks!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Consolidate multiple tabs with different headers (not using power query)

    Hi, tml2424.

    I guess you will find this thread useful for your query.

    https://www.excelforum.com/excel-for...orksheets.html

    Good luck.

  3. #3
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Consolidate multiple tabs with different headers (not using power query)

    if you don't mind to use a lot of helpers.

    1. Build list of avilable tab
    R2=IF(T2>0,MAX(R$1:R1)+1,0)
    S2 input tab name
    T2=COUNTA(INDIRECT("'"&S2&"'!A:A"))-1

    2. check number of tab that not blank
    Q1=MAX(R1:R5)

    3. create merged listed
    L2=IF(N1=O1,IF(MAX(L$1:L1)+1<=$Q$1,MAX(L$1:L1)+1,0),L1)
    M2=IF($L2>0,VLOOKUP($L2,$R$1:$T$5,2,FALSE),"")
    N2=IF($L2>0,VLOOKUP($L2,$R$1:$T$5,3,FALSE),"")
    O2=IF($L2>0,IF(L2=L1,O1+1,1),0)

    4. formula output area
    A2=IF($L2>0,$M2,"")
    B2=IF($L2>0,IFERROR(INDEX(INDIRECT("'"&$M2&"'!1:1000000"),$O2+1,MATCH(B$1,INDIRECT("'"&$M2&"'!1:1"),0)),""),"")
    (copy b2 thru G2)

    copy all formula as much as need.

    Regards.
    Attached Files Attached Files

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Consolidate multiple tabs with different headers (not using power query)

    Another option:
    Step by step guideline=
    1. Click "Define Name" on the formula tab, Type a name for example "Sheets" then Copy this code into it =TRANSPOSE(GET.WORKBOOK(1))&T(NOW()) then Enter "Ok"

    2. Then save your file as "Excel Macro Enabled Worksheet"

    3. Just type this formula on cell I2=
    =IFERROR(INDEX(MID(Sheets;FIND("]";Sheets)+1;255);ROW(A3);1);"") and copied down

    4. Then on J2 put this formula =COUNTA(INDIRECT("'"&$I2&"'!A2:A100")) and copied down

    5. Put this on A2 and copied down =LOOKUP(ROW(A1);SUMIF(OFFSET(J$1;;;ROW($1:$99);"<>")+1;I$2:I$99)&""

    6. Final put this on B2 and press all together CTRL+SHIFT+ENTER butttons all together because is array formula
    =IFERROR(INDEX(INDIRECT("'"&$A2&"'!A2:D100");SMALL(IF((INDEX(INDIRECT("'"&$A2&"'!A2:D100");0;MATCH(B$1;INDIRECT("'"&$A2&"'!A1:D1");0))<>"");MATCH(ROW($A$2:$A$100);ROW($A$2:$A$100));"");COUNTIF($A$2:$A2;$A2));MATCH(B$1;INDIRECT("'"&$A2&"'!A1:D1");0));"")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-08-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Consolidate multiple tabs with different headers (not using power query)

    Azumi, this works really well. Thank you!

  6. #6
    Registered User
    Join Date
    02-08-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Consolidate multiple tabs with different headers (not using power query)

    Azumi - I just noticed an issue with the formula but I'm not sure what the fix is. If any of the data cells were to be blank, for example if I remove the name Jane Doe on Tab 1, then the summary tab auto populates with the name in the next row, Mary Doe, versus just leaving that blank. Do you know I could fix that in the formula? (See attachment for example)
    Attached Files Attached Files
    Last edited by tml2424; 01-21-2022 at 07:49 PM.

  7. #7
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Consolidate multiple tabs with different headers (not using power query)

    Hope this fix it
    Attached Files Attached Files
    Last edited by AliGW; 01-22-2022 at 07:11 AM. Reason: PLEASE don't quote unnecessarily!

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

    Re: Consolidate multiple tabs with different headers (not using power query)

    In B2 then copied across.

    =IFERROR(INDEX(INDIRECT("'"&$A2&"'!A2:D100"),COUNTIF($A$2:$A2,$A2),MATCH(B$1,INDIRECT("'"&$A2&"'!A1:F1"),0)),"")
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  9. #9
    Registered User
    Join Date
    02-08-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    66

    Re: Consolidate multiple tabs with different headers (not using power query)

    Thank you menem. Also, worked great.

+ 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] Using Power Query to Consolidate Defined Cells from Multiple Workbooks
    By The_Snook in forum Excel General
    Replies: 2
    Last Post: 02-24-2022, 07:43 AM
  2. Using Power Query to Consolidate Defined Cells from Multiple Workbooks
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2021, 06:10 PM
  3. Replies: 2
    Last Post: 08-22-2021, 02:57 AM
  4. How to create pivot table with multiple headers in power query
    By CherryLee in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-07-2021, 10:16 PM
  5. Power Query: Changing Headers
    By andrewc in forum Excel General
    Replies: 0
    Last Post: 09-23-2020, 02:07 PM
  6. [SOLVED] Power Query and imported table headers
    By Tresfjording in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2019, 06:38 PM
  7. Consolidate 3 tabs into one tab with only 1 row for headers
    By FLani in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2013, 12:40 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