+ Reply to Thread
Results 1 to 9 of 9

Consolidation of various files based on cell value/Filter

  1. #1
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Consolidation of various files based on cell value/Filter

    Hi,

    Thank you all for your help,
    I need your help once again for consolidating various files into my master file , I found similar codes from http://www.rondebruin.nl/fso.htm site but my requirement is bit different from those.

    I have a master file in my C:/Master folder with Heading in row 2( Same as all other files)
    and I have some 100 files in C:/working_Files folder( Heading in 301th row and datas from there on)

    I need a macro in my master file to consolidate the data from all those 100 files ( starts from 302 row in all the files) and put in my master file from row 3 onwards one afterthe other)
    however this should happen based on a condition
    If I enter the Date in Cell A1 in my master file( Say 05-Dec-2010) Then look in Column AC(Processed Date) in all those 100 files, filter the data by the above date and get the data to my master file
    Next time if I change the date and run again, then get data only for this date and paste below. But by mistake if I keep the same date and run the macro then it should tell me that "I am trying to import duplicate data"

    Thanks
    Cross post : http://www.vbaexpress.com/forum/showthread.php?t=35262

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidation of various files based on cell value/Filter

    Something like this:
    Please Login or Register  to view this content.

    Working with dates gets tricky, you'll have to play with the AutoFilter part to make sure the filter is being applied in the proper format, or that your [A1] cell is already in the proper format.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: Consolidation of various files based on cell value/Filter

    Thanks JB,
    It almost works fine but

    1. It works only If I give in the value in number format eg: 05/12/2010 to 40518 and change the same in all other files

    2. & It clears the old data , I do not want to clear the old data, it should only stops importing the data for the said date is already imported if it is a new date then import the data and paste below

    eg:
    First I enter 05/12/2010 and run a macro it imports all the data from other files based on the column 29 to my master file( say data is filled till row 2500)
    next day if I keep the same date in Cell A1 as 05/12/2010 it should tell me that data is already imported,
    so that i will change the date to 06/12/2010 and run the macro again now import all the data from those files and paste below 2500 row

    Thanks for your help on this..

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidation of various files based on cell value/Filter

    1) I can't tweak the code for the date formats unless I can see an excerpt from your worksheet and this report sheet we're creating.

    2) Rather than hardcoding "always append", I've added a question to simply give you the option.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: Consolidation of various files based on cell value/Filter

    Hi Thanks,

    I modified the code a bit and i get error.
    1. The data starts from Row 2 instead of 302
    2. The data starts from Column B instead of Col A

    below is the code
    Please Login or Register  to view this content.
    I have attached two files 1 Master File and one source file ( The Format of the source file same as all other 100 files)
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: Consolidation of various files based on cell value/Filter

    Hi

    can you help me on this?

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidation of various files based on cell value/Filter

    When activating an AUTOFILTER, the Field:=???? should be the column you want to apply the filter to. Since I'm activating the Autofilter on the entire first row, this makes the normal column "numbers" work. So Field:=29 in my original version simply means column AC.

    Now, adjust that number to whatever the actual column number is that you want to apply the date filter to.

    ===========
    Looking at your code in Tracker, the high level of redundancy can be used to greatly reduce the amount of code. For instance, you can feed an array of column numbers into the same 'test' over and over again to check each on for "blanks" instead of having to code each column test individually. For instance, here's a potentially streamlined version of one of your macros:

    Please Login or Register  to view this content.

    Notice the underlined array of column values? Each is tested one at a time. If all pass, then the AllDone variable stays TRUE and the validation completes with time stamps and username. But if one of them fails, the AllDone is set to FALSE and the loop exits there.

    You can use that method so that each validation macro is essentially the exact same length as this one, just the array is different. For Validate_PO_Holds, the array would be Array(18, 3, 5, 4, 8, 15, 19, 20, 21), for Validate_Order_Creation the array would be Array(33, 3, 5, 4, 8, 15, 19, 20, 21, 16, 17), and so on.


    You know, now that I think about this, you really only need ONE macro to do all of this validation instead of 50 different ones. If you can feed the array values in at the time the macro is called, then the same macro would work for all validation scenarios.

    Perhaps a main validation macro called ValidateMain with a syntax like so:
    Please Login or Register  to view this content.
    Then the when you call this macro, you feed in the correct array of columns to check instead of different macros:
    Please Login or Register  to view this content.

    Since the MAIN_VALIDATE macro already activates the TRACKER sheet, that may be redundant on the the ValidateMain macro, too.

    ==========
    Another way to remove all the formulas from select sheets:
    Please Login or Register  to view this content.


    Take a look through here. With no data to speak of to test on, I was editing blind, but this should get you 95% of the way there. If you like the new array method and the ValidateMain macro, you can delete all the other individual macros as redundant.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    03-28-2008
    Location
    India, bangalore
    MS-Off Ver
    Excel 2003,2007
    Posts
    216

    Re: Consolidation of various files based on cell value/Filter

    Hi JB
    Thanks for your help.. i will play with this code and come back to you..
    however how about the consolidation from various files?
    Assuming all data existsin tracker sheet and i want to consolidate to my Maser sheet ( Attached earlier)

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidation of various files based on cell value/Filter

    Quote Originally Posted by aravindhan_31 View Post
    ...how about the consolidation from various files?
    What about it? Isn't that what the original macro I gave you does? Make sure you edit not only the row to copy from, but also the Field:= to apply the autofilter to.

    BTW, the Criteria1:= accepts formatted commands. Instead of having to convert all dates in all sheets to raw numeric format, just leave the source sheets alone and format the criteria to the correct date format found in the source sheets.


    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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