+ Reply to Thread
Results 1 to 6 of 6

Auto fillup colomns data from different spreedsheets into a mastersheet

  1. #1
    Registered User
    Join Date
    03-29-2020
    Location
    Kolkata, india
    MS-Off Ver
    Office 2013
    Posts
    5

    Auto fillup colomns data from different spreedsheets into a mastersheet

    Hi,
    I have a question please help me out...

    I have 3 excel spreedsheets, say ABC DEF GHI

    now in three excel sheets there are 5 colomns with same header name however they are not well placed means their places/positions are different in each sheet

    For example in ABC sheet the colomns are Name age dob doj qual,. But in DEF the colomns are Name doj dob age qual and in GHI it is doj Name qual dob age...

    Now I want my excel mastersheet(a forth sheet) where all the datas will automatically gets copied according to it's colomn name, i.e. the names from all three sheets will be copied under name colomn automatically in the mastersheet & so on...

    Thanks in advance

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Auto fillup colomns data from different spreedsheets into a mastersheet

    Suggest you load each into Power Query (available to you as you are using 2013). Move the columns so that they are in correct positions. Append each to the other. Close and Load to Excel Workbook. If you upload a sample of the three SS, then we can give you an actual demonstration with Mcode. Read the banner at the top of this page.
    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

  3. #3
    Registered User
    Join Date
    03-29-2020
    Location
    Kolkata, india
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Auto fillup colomns data from different spreedsheets into a mastersheet

    Hi,
    I have attached a spreed-sheet for sample...Kindly have a look ... The actual sheet contains at least 500 columns so manually it becomes impossible to sort.
    Thanks in advance mate.
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Auto fillup colomns data from different spreedsheets into a mastersheet

    See the attached file which was created using Power Query. Open file click on Data, Queries and Connections. Then look at each of the queries in the right window.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-29-2020
    Location
    Kolkata, india
    MS-Off Ver
    Office 2013
    Posts
    5

    Re: Auto fillup colomns data from different spreedsheets into a mastersheet

    Thank you mate...It almost solves my problem...Only thing that bothers me is that suppose my excel sheet contains 500 columns & among which 30 columns are consists of numbers as input with 2 decimal point, however when I run power query & after appending the tables among those 30 columns, in 3 or 4 columns the decimal points gone missing which I couldn't understand ... Kindly explain the reason & what to do so that the actual value with decimal points can be found.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Auto fillup colomns data from different spreedsheets into a mastersheet

    Check your change formats and see if it was automatically changed. No other ideas what is happening without actually seeing your data.

+ 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. How to add a name in mastersheet and auto creates new row in other sheets
    By NinaNoir in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2015, 07:06 AM
  2. Replies: 5
    Last Post: 08-07-2014, 12:37 PM
  3. VBA code for creating a auto generating mastersheet
    By alhoskins23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2014, 01:22 PM
  4. Replies: 4
    Last Post: 05-24-2012, 10:03 AM
  5. [SOLVED] auto fillup of days/date of month
    By iamhsn in forum Excel General
    Replies: 3
    Last Post: 05-18-2012, 11:05 PM
  6. Compare data in two colomns
    By luffield in forum Excel General
    Replies: 2
    Last Post: 10-23-2009, 09:13 AM
  7. Changing same data in multiple colomns
    By Jholdenrid in forum Excel General
    Replies: 5
    Last Post: 08-20-2009, 02:16 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