Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Merge HELP! (2007)

    Hi Hans,

    I have a sort of similar problem to this with a very monotenous daily task. I have 3 sheets, summary, data 1 (master data, but basic) and data 2 (much more in-depth). Firstly I have to compare data 1 to the summary (which is easy enough and generally matches). Once complete, I have to match data 2 which is much more in depth to data 1. Data 1 (once matched to the summary) is the master data required but is superseded by the more indepth data 2. Data 2 may contain rows of data which is not required, because it is not in data 1, therefore requires deletion. More likely though, there will be data in data 1 which is not in data 2,and will therefore need to be copied into data 2.

    I probably make this sound a lot more complex than it actually is. Though this is as I say, a very long winded and boring daily task. Any help that you can offer with a vba autoscript would be massively appreciated. I know I may be asking too much here, but I have learned from you guys (and girls) recently that almost nothing is impossible.

    I will get some raw data and post an attachment tomorrow.

    Cheers til then.....

    Nath

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    Yes, it would be helpful if you could post a workbook that gives us an idea of the setup you're working with.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Hans,

    I have attached a sample workbook as promised.

    Both the summary and data 1 are correct. Data 2 has several problems. Firstly there are 2 data rows missing, so I would need them to be brought accross from data 1. Secondly, two of the value's in data 2 are incorrect so need to be replaced with the values from data 1. Finally, column C in data 1 needs to be copied to data 2. All matched using column B.

    Hope this makes sense,

    Many Thanks
    Nath
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    What's the point of having Data1 and Data2 sheets? After the corrections, they will contain mostly the same data. You might as well add the extra columns from Data2 to Data1 and get rid of Data2.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Sorry Hans, we may be at cross wires here. The summary is the master figure that we are aiming for. Data 1 is an item breakdown of the summary (made up of 2 very basic reports), this is kind of useless information but correct in terms of item count, value and transaction id!! Data 2 (made up of 6 reports with much more information, thus the end result required) can be incorrect in parts. there may be missing data, incorrect data or both. Therefore we have to adjust data 2 to data 1 accordingly. In other words, we fall back on data 1 to correct data 2. Even though I have set them out in the same fashion, data 1 only really contains the original transaction id (,value and cp, whereas all the columns in data 2 are filled with info.

    Hope this makes a little more sense now?

    Thanks
    Nath

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    The manual workaround that we currently have for this is as follows:

    one worksheet. data 1 (made up of 2 reports showing transaction id, & value) on the left hand side.
    data 2 (made up of 6 reports and about 10 columns of data) on the right hand side.

    data sort the left side by tran id. data sort the right side by tran id. two columns of formula's between left and right. comparing value to value and tran id to tran id. if the values dont match, adjust right hand side. if any items are missing on the right, add the raw data from the left. Until right is equal to left, at which point we lose the left side and work with the right side.

    That's just the first element of a daily file build, beleive me, the rest does'nt get much more interesting, or exciting. :-(

    Thanks
    Nath

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Merge HELP! (2007)

    Perhaps I am missing something, but why not just copy the appropriate columns from Data1 into Data2 that should be updated?

    Steve

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Unfortunately Steve, it is'nt. data 1 is only useful in terms of the correct item count and values.The mass data that is required is populated as data 2. However this can be incorrect, with missing data rows and incorrect values. Therefore we have to get data 2 to match up to data 1. As well as the 'cp' shows in data 1 but not data 2, hence the need to merge the two sets of data to build a complete file.

    Thanks
    Nath

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    It still doesn't make much sense to me, but try the attached code.
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Hans,

    Huge thanks for your time. The code is on the right track, close but not quite. I will take more time tomorrow to review, disect and attempt to get an understanding.

    I may return......... (if you don't mind)

    Many Thanks
    Nath

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Hans,

    Actually, your code is absolutely perfect!! It does exactly what I want!! I have used it on some real data which has thrown up one minor problem. I have two transactions with the same ID but different values 10 & 35. Running the code converts both the value's in data 2 to 10. (I presume being the first available match??). Therefore leaving me with a 25 imbalance in data 2. Can you think of a way to get around this? Maybe matching based on a concatenate of the tran id and value,as opposed to just the tran ID?? But then I'm not sure if that would conflict with the general rule of replacing data 2 value with data 1 value??? Any thoughts??

    You've been a massive help, Thankyou!

    Nath

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    The code does look for the first match on column B. Matching on columns B and I isn't an option because that would make it impossible to apply corrections.
    Why are there multiple transactions with the same ID? Isn't the purpose of an ID to be unique?

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Hi Hans,

    I therefore need to establish which output would give a better result over a period of time. You are right about the transaction id, you would expect each one to be unique, however for a minute portion of the data, this is unavoidable.

    Would it be possible to point me in the direction of a match based on a concatenate of id and value, removing the instruction to update the value field in data 2. That way I can compare a history of output's and make a choice.

    Again, thanks for all your help.

    Nath

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Merge HELP! (2007)

    You originally mentioned that you wanted to remove rows from Data2 whose ID doesn't occur in Data1.
    If you match on the combination of ID and value, and if you remove non-matching rows, you'd lose the rows with an incorrect value. If you *don't* remove non-matching rows, you'll end up with lots of extra rows, causing the summary not to balance. See attached version.
    Attached Files Attached Files

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Merge HELP! (2007)

    Hi Hans,

    My knowledge of code is very minimal. My abilities only really stretch as far as recording macro's, analysing them and disecting them from there. If it can't be recorded (as in this instance), I really fail to understand the code. Though I am learning as I go along.

    The second code that you provided, turned all the data in data 2 to 5.00 values on one days data and 50.00 on another days. So I compared both scripts, trying to understand. Your comments helped a lot (Thanks), but a lot of the code I don't understand. I think though (and please correct me if I am wrong) the first script starts in cell B2 and progresses downwards. The second script starts at cell M2 and progresses down?? But there is nothing in column M. Am I therefore right in assuming that you had concatenated columns B&I into M? If so, the code does not do this, so would that need to be set up manually prior to running the code, or can that be written in to the code??

    Could the match be created using the first 16 characters of column B only, ignoring the 17th character if there is one?
    Instead of deleting rows, could they be moved to a separate woksheet, this way any required manual corrections could still be made later if required?

    I know that I am asking a lot here, but your assistance will hopefully help to grow my knowledge. If you have any spare time in the future, could you offer more detailed explanation of the code, explaining things like:

    Set ws1 = Worksheets("Data 1")
    m1 = ws1.Cells(ws1.Rows.Count, 2).End(xlUp).Row
    Set rng1 = ws1.Range("M2:M" & m1)

    For r2 = m2 To 2 Step -1

    ws2.Cells(r2, 3) = ws1.Cells(r1, 3)

    m2 = m2 + 1
    For c = 1 To 9
    ws2.Cells(m2, c) = ws1.Cells(r1, c)

    Sorry if I am being a pain!!

    Thanks

    Nath

Page 1 of 2 12 LastLast

Posting Permissions

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