Results 1 to 7 of 7
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Combining Data (2002)

    Looking for some help again. I have three separate workbooks with the data - code, warehouse, period, qty. One workbook lists item sales, another purchases, the third item usage. Some codes may exist in each workbook, some in only two, whilst the majority will only exist in one workbook.
    I need to have all the records in one workbook, where the code exists then put the qty in the appropriate column, otherwise include the record. I have attached a sample of how the final records would look.
    I have looked at merge but it does not appear to be what i want, unless I am missing something (which is not unusual!)
    What options do I have to achieve this combining. The sales records total about 120000 rows, whilst the purchase and useage records are about 16000 rows each.
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data (2002)

    Some VBA would probably be the best bet to merge these. First, some questions.

    1- The column headings in your example workbooks don't match the description in your message. Could you match them up for us? For Example, what column heading goes with qty.

    2- How many and which or the Code, Warehouse, Period, and Qty have to match up before the records are considered to be the same? If they all don't have to match, from which of the workbooks do the ones that do not match. For example, if qty does not have to match for the records to be considered the same, and the qty is not the same in all workbooks, which do you want in the merged workbook?

    3- What are the workbook names?

    4- What columns are the values in in the original workbooks.

    5- Could you include samples from the other three workbooks as separate worksheets in your example workbook that could be used to test the code? The data can be modified to protect any confidentiality.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data (2002)

    Out of curiosity: you say you have 120000 sales records. Are these on 2 or more sheets?

    with 65000+ rows in a sheet........?

    or do the sales records exist in "column fashion" on the same sheet?

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Combining Data (2002)

    Hi Leagre, sorry for the confusion.
    1.The sales workbook would have a column heading of Sold, Purchasing Purch, Usage QtyUse.
    2. The match is Code, Warehouse, Period, if so what is the heading to put the qty in the appropriate column. If no match then add record to master workbook.
    3. Workbook names are R093Usage, R097aPhist, r098SalesStats.
    4. Currently the are in column D, but I could easily move them to match the 'target" column placement
    5. I have included sample data in the attached. Note that my original data sample does not match the 'real' data.
    Paul Coyle
    Approach love and cooking with reckless abandon

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Combining Data (2002)

    Yes, there are two sheets in this workbook and this is only some of our data!!
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining Data (2002)

    OK, the attached workbook contains a macro that I think does what you want to do. You will need to modify the line of code below to match the path to the directory where your files are located:

    <pre> strPath = "C:WorkMerge"
    </pre>


    If your workbooks really do have more that 65,535 records, then the code will need a lot more work. The code currently will break when the merged worksheet exceeds that number of records. Let me know if this works and if you want help to extend it to more than 65,535 records.
    Legare Coleman

  7. #7
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Combining Data (2002)

    Thank you Legare, I have saved the file and will look at your code shortly.
    To keep things down to a manageable size I thought that I would break the current data down into 17 workbooks, each data set covering one period.
    Many thanks for your efforts and prompt help, as always I am humbled by how this lounge works. <img src=/S/clever.gif border=0 alt=clever width=15 height=15> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •