Data Merge HELP! (2007)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

I have moved your part of the thread to a separate thread, to avoid confusion with the original thread which is still active.

I’m sorry, I forgot to mention that I did indeed create formulas in column M in both Data 1 and Data 2 that concatenate the values from column B and column I. I used

=B2&"|"&I2

and filled down. This could be written into the code without problem.

I’ll see if I can come up with something, although it is getting a bit much.

Reply | Quote

The macro in the attached workbook creates formulas in column M (and deletes them at the end), and it creates a worksheet named Deleted if it doesn’t exist yet.
It matches on the concatenation of the first 16 characters from column B and the value from column I, separated by a | character to avoid ambiguity.

Reply | Quote

Hans,

100% perfect! Exactly what I needed. Hopefully now, learning from your code I should be able to build in the next sections.

I cannot thank you enough. The next time you are in my neck of the woods, the first beerz are on me!!

Cheers
Nath.

Reply | Quote

About the snippets of code you asked about:

Set ws1 = Worksheets(“Data 1”)
ws1 is a variable of type Worksheet. The above line sets it to the Data 1 worksheet.

m1 = ws1.Cells(ws1.Rows.Count, 2).End(xlUp).Row
ws1.Cells(ws1.Rows.Count, 2) is the bottommost cell in column B (the second column) on ws1 (the Data 1 sheet).
.End(xlUp) is the VBA equivalent of pressing End, up arrow: it takes us to the last non-blank cell in column B.
.Row is the row number of this cell. So m1 is assigned the row number of the last non-blank cell in column B.

Set rng1 = ws1.Range(“M2:M” & m1)
This sets the variable rng1 of type Range to the range in column M (the column where we will place the concatenation formulas) that corresponds to the data range in column B. For example, if m1 = 37, it will be the range M2:M37.

For r2 = m2 To 2 Step -1
The variable r2 starts at the row number of the last non-blank cell in column B on the Data 2 sheet, and is decreased one at a time until we end up at the 2nd row. This lets us loop backwards through the rows. It is necessary to loop backwards because we will be deleting rows from Data 2 along the way. If we would loop forward, we’d be pulling the rug from under our feet by deleting rows.

ws2.Cells(r2, 3) = ws1.Cells(r1, 3)
This line sets the value of a cell in column C (the 3rd column) on Data 2 to the value of the cell in column C in Data 1 where we found a match.

m2 = m2 + 1
m2 is the row number of the last non-blank cell in column B in Data 2. When we find a row in Data 1 that has no match in Data 2, we add its info to Data 2 below the existing rows. To do this, we increase m2 by 1.

For c = 1 To 9
ws2.Cells(m2, c) = ws1.Cells(r1, c)
Next c
This copies the values of cells in columns A to I (1 to 9) from the “current” row in Data 1 to the new last row in Data 2.

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote