Table 1 has 9 columns and 983 rows (excluding header)

Table 2 has 15 columns and 6295 rows (excluding header)

I wish to merge these into a new table, call it Table 3.
Every row of Table 1 should (fingers crossed) already have a matching row somewhere within Table 2. A match is defined when columns 1,2,3 of Table 1 match columns 3,4,5 of Table 2.

The merged table should contain 983 rows (from Table 1) and 21 columns (9 + 15 – the matching 3).

Any idea how to do that in Excel?

(If necessary, I don’t mind if it is done in several steps eg. producing a table with 24 columns and 6195 rows before deleting unwanted rows and columns).

The simplest way that comes to mind is:

1. In each table create a new column with the combined contents of columns 1,2,3 or 4,5,6 using =A1&B1&C1 and copy down.
2. Use the Match function, with exact match option, to find the matching row number in Table 2 for each row in Table 1. =Match(J1, Table2MatchCol, 0) where J is the new column in Table 1 and Table2MatchCol is a range name for the new column in Table2.
3. Use the Index function to extract the required data from Table 2 in the new combined table.

The Rev Dodgson said:

The simplest way that comes to mind is:

1. In each table create a new column with the combined contents of columns 1,2,3 or 4,5,6 using =A1&B1&C1 and copy down.
2. Use the Match function, with exact match option, to find the matching row number in Table 2 for each row in Table 1. =Match(J1, Table2MatchCol, 0) where J is the new column in Table 1 and Table2MatchCol is a range name for the new column in Table2.
3. Use the Index function to extract the required data from Table 2 in the new combined table.

Excellent, will try that.

The Rev Dodgson said:

The simplest way that comes to mind is:

1. In each table create a new column with the combined contents of columns 1,2,3 or 4,5,6 using =A1&B1&C1 and copy down.
2. Use the Match function, with exact match option, to find the matching row number in Table 2 for each row in Table 1. =Match(J1, Table2MatchCol, 0) where J is the new column in Table 1 and Table2MatchCol is a range name for the new column in Table2.
3. Use the Index function to extract the required data from Table 2 in the new combined table.

Not quite working, because columns 1,2 and 4,5 are floating point so are not matching because of round-off errors.

then change the ‘match’ argument from 0 to either 1 or -1

this will either give you the largest closest match or the lowest closest match

or just truncate the decimals after 4 or 5 places

diddly-squat said:

then change the ‘match’ argument from 0 to either 1 or -1

this will either give you the largest closest match or the lowest closest match

That will only work if the columns are sorted. If they aren’t it will return the first column with a value greater or less than the match value.

You can use the text function to convert the numbers to a string with a specified format:

=Text(a1,xx)&Text(b1,xx)&c1

Only problem is I’ve forgotten how the format is specified (it’s not with xx) and I don’t have time to look it up.

Try the help, or search my blog for text function.

The Rev Dodgson said:

If they aren’t it will return the first column with a value greater …

First row that should be.

• =Text(a1,xx)&Text(b1,xx)&c1

or truncate, as previously mentioned, or round …

furious said:

• =Text(a1,xx)&Text(b1,xx)&c1

or truncate, as previously mentioned, or round …

Yes, round would be the easiest.

The Rev Dodgson said:

furious said:

• =Text(a1,xx)&Text(b1,xx)&c1

or truncate, as previously mentioned, or round …

Yes, round would be the easiest.

I need to round, because I’ve realised that the mismatch is because one of the two that I want to compare with has already been rounded.

“The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: =ROUND. The result of this function is 23.78.”

I’d have to round in a separate column as I don’t want to destroy the original. OK, I think everything’s under control.

mollwollfumble said:

I’d have to round in a separate column as I don’t want to destroy the original. OK, I think everything’s under control.

You could add rounded columns, or do =ROUND&ROUND&C1 so there is just one extra column for each table.

mollwollfumble said:

The Rev Dodgson said:

furious said:

• =Text(a1,xx)&Text(b1,xx)&c1

or truncate, as previously mentioned, or round …

Yes, round would be the easiest.

I need to round, because I’ve realised that the mismatch is because one of the two that I want to compare with has already been rounded.

“The ROUND function rounds a number to a specified number of digits. For example, if cell A1 contains 23.7825, and you want to round that value to two decimal places, you can use the following formula: =ROUND. The result of this function is 23.78.”

I’d have to round in a separate column as I don’t want to destroy the original. OK, I think everything’s under control.

you can round in the expression if you want… that way you don’t destroy the original value

Comparing floating-point numbers is very dodgy even if those numbers have been rounded. Properly comparing floating point numbers involves determining if the difference lies between two numbers. However, this can’t be done in the case of MATCH. However, for this case, either the two numbers are converted to integers for which the problem of comparing floating-point numbers isn’t present, or converting the two numbers into text which can also be compared without problem.

KJW said:

Comparing floating-point numbers is very dodgy even if those numbers have been rounded. Properly comparing floating point numbers involves determining if the difference lies between two numbers. However, this can’t be done in the case of MATCH. However, for this case, either the two numbers are converted to integers for which the problem of comparing floating-point numbers isn’t present, or converting the two numbers into text which can also be compared without problem.

Thanks KJW. It turned out that dividing the floating point numbers by 3 before rounding helped the match. For example suppose the true number was 10.49 which had been previously rounded up to 10.5, then rounding both to zero decimal places will give 10 for the first but 11 for the second – no match. But dividing by 3 and rounding to 1 decimal place gives a match. It turned out that the mismatch was variable, and at the end I was still stuck with about 20 cases that had to be matched by hand, no great difficulty. Problem solved.

mollwollfumble said:

KJW said:

Comparing floating-point numbers is very dodgy even if those numbers have been rounded. Properly comparing floating point numbers involves determining if the difference lies between two numbers. However, this can’t be done in the case of MATCH. However, for this case, either the two numbers are converted to integers for which the problem of comparing floating-point numbers isn’t present, or converting the two numbers into text which can also be compared without problem.

Thanks KJW. It turned out that dividing the floating point numbers by 3 before rounding helped the match. For example suppose the true number was 10.49 which had been previously rounded up to 10.5, then rounding both to zero decimal places will give 10 for the first but 11 for the second – no match. But dividing by 3 and rounding to 1 decimal place gives a match. It turned out that the mismatch was variable, and at the end I was still stuck with about 20 cases that had to be matched by hand, no great difficulty. Problem solved.

Why not round to 1 decimal place then?

This isn’t really an issue with comparing floating point numbers, since the numbers are converted to text anyway when you combine the cells. The problem is that some had already been rounded, so if you round everything to the minimum precision used already, everything should match.

Unless the rounding had been done inconsistently of course.

You might also like a look at:
https://newtonexcelbach.wordpress.com/2016/10/21/nearest-lookup-function/
which has a rant on the problems with Excel Match and Lookup functions.

But my nearest function needs numerical data, so it wont work on your combined cell search.

> Unless the rounding had been done inconsistently of course.

Exactly. Inconsistently. For several different reasons.

I had previously gone through manually removing “duplicates”. These could differ by up to 100 times the typical rounding error.

In a worst case with pure rounding error, the rounding error can and does exceed the difference between genuinely different values in columns one and two. To further confuse the issue, I had previously generated the big file by merging two smaller files with different levels of rounding error. Most rounding had happened accidentally when I cut and paste columns from Excel to Text, before bringing them back into Excel.

The dividing by 3 turned out to be important. If column A is the same as exact column B but rounded to n digits, then comparing them by rounding both columns to n-1 digits will give an error exactly 5% of the time. With 6300 rows, a mismatch 315 times occurs, which is unacceptable. Dividing both columns by 3 and then rounding both to n (rather than n-1) digits theoretically gives <0.1% error in matching, as well as giving 3.3 times finer separation resolution.

It’s a little numerical trick to keep in mind.

The Rev Dodgson said:

The simplest way that comes to mind is:

1. In each table create a new column with the combined contents of columns 1,2,3 or 4,5,6 using =A1&B1&C1 and copy down.
2. Use the Match function, with exact match option, to find the matching row number in Table 2 for each row in Table 1. =Match(J1, Table2MatchCol, 0) where J is the new column in Table 1 and Table2MatchCol is a range name for the new column in Table2.
3. Use the Index function to extract the required data from Table 2 in the new combined table.

Thanks again to Rev D.

Just when I thought I had final results, I found that nearly half my latitudes and longitudes were wrong.
Found a reduced, out of order version of the bad data before the error, so had to type in one column by hand again.
Then saved time by using Rev D’s method twice. Keeping backups because I managed to corrupt and fix the data once on the way.

So have just now got to the final results. Only to find that a sizable unknown percentage of my latitudes and longitudes are wrong. Sob.
Now to track down the error once again. I suspect I used the Index function on the array in column AU instead of the array in column O.

My data will be perfect!

170 patients down.

mollwollfumble said:

So have just now got to the final results. Only to find that a sizable unknown percentage of my latitudes and longitudes are wrong. Sob.
Now to track down the error once again. I suspect I used the Index function on the array in column AU instead of the array in column O.

I overreacted. The more I check the more I found is correct. It now seems that I had only a single datapoint out of place. Easy to fix. Deleted it.

buffy said:

My data will be perfect!
170 patients down.

170 patients is great. Your paper by “Thorn, OD, PhD, FAAO, Jane” has arrived. :-)

My Antarctic data should be perfect now, give or take 20 metres or so. At least, all the datapoints that are confirmed by three independent datasets should be perfect.

mollwollfumble said:

mollwollfumble said:

So have just now got to the final results. Only to find that a sizable unknown percentage of my latitudes and longitudes are wrong. Sob.
Now to track down the error once again. I suspect I used the Index function on the array in column AU instead of the array in column O.

I overreacted. The more I check the more I found is correct. It now seems that I had only a single datapoint out of place. Easy to fix. Deleted it.

buffy said:

My data will be perfect!
170 patients down.

170 patients is great. Your paper by “Thorn, OD, PhD, FAAO, Jane” has arrived. :-)

My Antarctic data should be perfect now, give or take 20 metres or so. At least, all the datapoints that are confirmed by three independent datasets should be perfect.

Goodo. I don’t know if it is useful, but I guess we do need to know what other people have done with the maths.

transition said:

stumpy_seahorse said:

transition said:

Hyundai Excel 97

losing power, missing, gradually got worse over last month, today at highway speeds engine died for a while, then low power for quite a while, then sorta was alright.

figure do basic maintenance, if doesn’t fix it i’ll think about sensors etc

but reckon’s fuel filter, given it’s injected and has engine management system the symptoms of fuel filter won’t be so much like old days with carby.

we’ll see tomorrow, i’ll get to it just before lunch, do oil change filter too. Way overdue

run it and pull the fuel pump fuse out, then let it die before you change the fuel filter.
(or take the back seat out and pull the electrical plug off the top of the fuel pump)

that’ll drop the pressure in the line, those excels hold a fair bit of pressure and you’ll get a shower of fuel otherwise.

Found out this week that all hyundais are built left hand drive.. random fact

i’ll keep that in mind that might be still pressurized when turned off, thanx stumpy

stumpy_seahorse said:

transition said:

stumpy_seahorse said:

run it and pull the fuel pump fuse out, then let it die before you change the fuel filter.
(or take the back seat out and pull the electrical plug off the top of the fuel pump)

that’ll drop the pressure in the line, those excels hold a fair bit of pressure and you’ll get a shower of fuel otherwise.

Found out this week that all hyundais are built left hand drive.. random fact

i’ll keep that in mind that might be still pressurized when turned off, thanx stumpy

What’s all this?
Excel is some sort of motor vehicle?
Next you’ll be telling me that Lotus is a car as well.

• Next you’ll be telling me that Lotus is a car as well.

Ha! That’s a good point…

mollwollfumble said:

For example suppose the true number was 10.49 which had been previously rounded up to 10.5, then rounding both to zero decimal places will give 10 for the first but 11 for the second – no match.

I had not considered this. To overcome this problem, one can create a user-defined function in VBA that performs a function similar to MATCH but instead of testing for equality, would calculate the absolute difference between the two (not rounded) numbers and determine if this absolute difference is less than some specified value.

The Rev Dodgson said:

1. In each table create a new column with the combined contents of columns 1,2,3 or 4,5,6 using =A1&B1&C1 and copy down.

or
A1&” “&B1&” “&C1

mollwollfumble said:

The Rev Dodgson said:

1. In each table create a new column with the combined contents of columns 1,2,3 or 4,5,6 using =A1&B1&C1 and copy down.

or
A1&” “&B1&” “&C1

This won’t address the problem that you indicated to me whereby two numbers that are regarded as different may be closer than two numbers that are regarded as the same.