locked
Copy columns from multiple sheets as per the name RRS feed

  • Question

  • Hi there -

    I need to create a mastersheet that will contain 214 columns as per the Name column (Column A) of mastersheet.

    I have 214 worksheets and each sheet have same number of columns and I want to pick only one column as per the Name column (Column A). not every Name data available in those 214 worksheets.

    I have name column in mastersheet as well (Column A) and want to gather data for those names from 214 worksheets.

    each worksheet contain 64 column and I need column no. 64 from 214 worksheets.

    my sheets name as Jan1995, Feb1995,Mar1995,....Oct2012

    my sample file can be accessible via below link

    https://drive.google.com/file/d/1mDrc4eHsfdVNv1RL_l1V-5cBOwc9pey8/view?usp=sharing

    Regards, Sara

    Thursday, August 6, 2020 6:28 AM

Answers

  • How about

    =VLOOKUP($A2,Age!$A$1:$HG$828,MATCH($B$1,Age!$1:$1,0),0)

    in D2, then fill down.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhD Wednesday, August 19, 2020 11:59 PM
    Wednesday, August 19, 2020 11:33 AM
  • Your sample workbook won't yield any results since the dates in row 1 begin in 2000, and the only data worksheets are for 1995...

    But you can use the following formula in B2, then fill to the right and down:

    =IFERROR(VLOOKUP($A2,INDIRECT("'"&TEXT(B$1,"mmmyyyy")&"'!A1:BL828"),64,FALSE),"")

    Warning: recalculating will take a long time: there are 176978 cells with formulas, and INDIRECT is relatively slow.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhD Friday, August 7, 2020 3:01 AM
    Thursday, August 6, 2020 11:13 AM

All replies

  • I tried VLOOKUP but when I copying the formula and paste it in next column of mastersheet it won't change the worksheet name and I need to manually change the name as Feb1995

    =VLOOKUP($A2,'Jan1995'!$A$1:$BL$828,64,0)

    =VLOOKUP($A2,'Feb1995'!$A$1:$BL$828,64,0)

    Also I am getting #N/A for those names which are not included in worksheets and I want a blank cell for those.



    • Edited by SaraPhD Thursday, August 6, 2020 6:34 AM correction
    Thursday, August 6, 2020 6:33 AM
  • Your sample workbook won't yield any results since the dates in row 1 begin in 2000, and the only data worksheets are for 1995...

    But you can use the following formula in B2, then fill to the right and down:

    =IFERROR(VLOOKUP($A2,INDIRECT("'"&TEXT(B$1,"mmmyyyy")&"'!A1:BL828"),64,FALSE),"")

    Warning: recalculating will take a long time: there are 176978 cells with formulas, and INDIRECT is relatively slow.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhD Friday, August 7, 2020 3:01 AM
    Thursday, August 6, 2020 11:13 AM
  • Have you looked at using a database to hold your data.  I have never had much luck holding a lot of data in workbook.  It eventually gets corrupted.  Working with SQL to extract, manipulate and insert data is much more reliable.  It is easy to connect from Excel to database to create reports or insert data.  I'm currently using an Azure SQL DB.  It costs $5/month.  You can use SQL Server Express (free) and SQL Server Management Studio (free).
    Thursday, August 6, 2020 3:00 PM
  • Hi Hans, 

    the data starts from Jan1995 but the column 64 is for year Jan2000 and similarly in Feb1995, column 64 is for Feb2000. 

    can we use any macro for doing this ? 

    Regards,

    Sara

    Thursday, August 6, 2020 11:22 PM
  • I am trying the above formula but it is giving me blank cells.

    I think, it is using table_array as the Mastersheet and not the worksheet Jan1995,Feb1995.  

    I have changes the Row 1 labels as Jan1995, Feb1995 and then apply the formula(see URL to access update sample file).

    https://drive.google.com/file/d/1mDrc4eHsfdVNv1RL_l1V-5cBOwc9pey8/view?usp=sharing

    Regards, Sara

    Thursday, August 6, 2020 11:46 PM
  • Thanks for the information and suggestion @mogulman52. I will check if I can handle SQL

    Regards, Sara

    Friday, August 7, 2020 12:34 AM
  • I got the columns as per names.

    Thanks,

    Sara

    Friday, August 7, 2020 3:01 AM
  • I get "access denied" when I try to download your sample file.

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Friday, August 7, 2020 8:40 AM
  • Hello Hans,

    A quick question -  I want to copy column from a mastersheet into multiple sheets as per the name column (column 1) in each sheet.

    Like

    Sheet1(Jan2000) copy Column 2 (Jan2000) from mastersheet as per the name column of Sheet1

    Sheet2(Feb 2000) copy Column 3 (Feb 2000) from mastersheet as per the name column of Sheet1  and so on.

    I am trying the Indirect Excel function you mentioned earlier but unable to give mastersheet referrence and varying column referrence? can I do this with Indirect function ? I tried Vlookup (=VLOOKUP($A7,Age!$A$1:$HG$828,COLUMNS(Age!$B:B)+1,0)) but that didnt serve the purpose.

    https://drive.google.com/file/d/1eyP3QmdooGT4Ew5x4y5WOuQqz29n6Z9p/view?usp=sharing

    Please advise.


     

    Wednesday, August 19, 2020 10:55 AM
  • How about

    =VLOOKUP($A2,Age!$A$1:$HG$828,MATCH($B$1,Age!$1:$1,0),0)

    in D2, then fill down.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    • Marked as answer by SaraPhD Wednesday, August 19, 2020 11:59 PM
    Wednesday, August 19, 2020 11:33 AM
  • Thanks, heaps Hans, I appreciate all your help on this.
    Regards, Sara

    Thursday, August 20, 2020 12:01 AM