Match Items from two different matrix

users
#1

I understand now what you are saying

I don't think you do - at least, not entirely. You haven't clarified the answers to various questions. I don't need the answers to these - but you do!

- I need to find which games of the first Matrix appear in the second Matrix finding equal string or equal word into the string. For example:
Column-Row A1 Column-Row A6
0 A.D. 0 A.D. Empires Ascendant
The content of A1 and the content of the A6 are about the same game but they uses different name (One single and the other complete) I need to find A1 for A6 in Column B6 of Second Matrix by the key in this case "0 A.D." that appear in both names. Sometimes, both, the name of the game of the First Matrix and the name of the second Matrix are equals. I try to do this by formula unknowing the exactly key but knowing that there is.

You do not know what exactly the key is, but you are expecting a formula in Calc to guess this for you. Surely that is worse than expecting it to read your mind, since even your mind does not know the answer, you say?

Before you can do anything like this, you do need to have a clear statement of exactly what constitutes a match and what doesn't. If you had "Whatever" in one column and "Whatever 2" in the other, would these be the same game or would one be the successor to the other? If one column included "0 A.D." and the other "0 A. D.", would those be the same? What about "0 A.D." and "0 AD"? You probably cannot rely on names assembled from different sources being entered identically.

It may well be, of course, that it will be more or less impossible to define exactly what constitutes a match. In that case - and especially if the lists are not particularly large and you are doing this process once, not regularly - you may find that manual inspection is your quickest route. You could start by looking for exact matches (as explained previously). How about then sorting the lists alphabetically and picking out obvious matches? You could then transfer these items or rows into a new list. This would bring any further matches closer together and enable you to identify more. Even if you could come up with a clear criterion for a match, you would inevitably have to do a lot of manual checking afterwards to eliminate false positives and to add missed matches.

I trust this helps.

Brian Barker

#2

Hi Brian:

  Thank you for your support, I just order both matrix alphabetically and
I tried to use VlookUp but it gave me a several false-positive as you
toll me. But at this moment is my only way if not I would had to have it
complete manual.

Regards,

Jorge Rodríguez

#3

Thank you for your support, ...

No probs!

I just order both matrix alphabetically and I tried to use VlookUp but it gave me a several false-positive as you told me.

If you get false matches from a simple VLOOKUP(), it is possible that you have not set the fourth parameter correctly. If, as here, there may not be a match, you need to set the SortOrder parameter to FALSE or zero, irrespective of whether the list is sorted.

I trust this helps.

Brian Barker

#4

Good morning Brian and all:

    I did this in my workbook:

Sheet 1 Sheet2
1st Matrix 2nd Matrix VlookUp
Result
Name Name
Formula
0 A.D. 0 A.D. Empires Ascendant =BUSCARV(A2;Hoja1.A3:A6;1;1)
0 A.D.
2HFU 3D Chess
=BUSCARV(A3;Hoja1.A3:A6;1;1) 2HFU
3D chess Abe
=BUSCARV(A4;Hoja1.A3:A6;1;1) 3D chess

In the first case I tried to find "0 A.D. Empires Ascendant" from 2nd
Matrix - sheet 2- in the 1st Matrix -sheet 1- and found "0 A.D." ... it
was good !

But in the second case I tried to find "3D Chess" that is the third
element in the first Matrix-Sheet 1- and the found result is "2HFU" ...
it isn't good and confused me.

In the last case I tried to find "Abe" and found "3D chess" more confuse
to me.

As I can see the VlookUp find always the element that continue in order
in the list of the 1st Matrix not the name that I'm finding.

And if I used "=BUSCARV(An;Hoja1.A3:A6;1;0)" with zero at end, the tree
results are "#N/D" ("An" is for not to repeat tree times the formula)

I don't know if I'm doing something wrong or there is a bug

Some time ago, VlookUp was using only to search exactly element (Only
equal) now it has so strange behaivor (At least to me)

Regards,

Jorge Rodríguez

#5

I'm sorry if it was formated confuselly. I hope this is better:

#6

I did this in my workbook:
Sheet 1 Sheet2
1st Matrix 2nd Matrix VlookUp Result
Name Name Formula
0 A.D. 0 A.D. Empires Ascendant =BUSCARV(A2;Hoja1.A3:A6;1;1) 0 A.D.
2HFU 3D Chess =BUSCARV(A3;Hoja1.A3:A6;1;1) 2HFU
3D chess Abe =BUSCARV(A4;Hoja1.A3:A6;1;1) 3D chess

Hold on! As I made very clear in my last message (and you even copied in yours!), "If, as here, there may not be a match, you need to set the SortOrder parameter to FALSE or zero, irrespective of whether the list is sorted." You have set it to 1 and so are telling the function that there *will* be a match somewhere in the array. I if there is no match, you will get wrong results. That's all explained clearly in the help text.

You must have typed those three formulae separately, which is a recipe for errors. You need the array to be specified not as Hoja1.A3:A6 but as Hoja1.A$3:A$6. That way, you can fill the formula down the column. Surely that idea was explained on page 3 (or thereabouts) of your Beginner's Guide to Spreadsheets?

In the first case I tried to find "0 A.D. Empires Ascendant" from 2nd Matrix - sheet 2- in the 1st Matrix -sheet 1- and found "0 A.D." ... it was good !

Actually, you have been satisfied by a rogue result. The function searches for "0 A.D. Empires Ascendant" and fails to find it. With the correct fourth parameter, you would see that.

But in the second case I tried to find "3D Chess" that is the third element in the first Matrix-Sheet 1- and the found result is "2HFU" ... it isn't good and confused me.

You did not take my advice - and instead told the function that a match was guaranteed. Since it wasn't - "3D Chess" and "3D chess" do *not* match if case is relevant - you got a wrong result. Set the fourth parameter correctly and you will see the correct result.

In the last case I tried to find "Abe" and found "3D chess" more confuse to me.

Same problem.

And if I used "=BUSCARV(An;Hoja1.A3:A6;1;0)" with zero at end, the tree results are "#N/D" ("An" is for not to repeat tree times the formula)

That means that the result is "not available" - in other words that there is no match. That is surely what you want to see in that circumstance? (Note that there is no exact match for any of your three examples.)

I don't know if I'm doing something wrong or there is a bug.

I do!

Some time ago, VlookUp was using only to search exactly element (Only equal) now it has so strange behavior (At least to me)

It is doing exactly what it says on the tin (er, in the help text).

I'm not sure why you keep asking for help, since you have consistently ignored my advice. That is very much your prerogative, of course, but you can hardly expect anyone to continue to help if you do so. (I note that no-one else is offering help.) In addition to the SortOrder parameter, you have clearly not understood the difference between exact matches and the fuzzy matches that you are clearly hoping for. You failed to respond to the seven example questions I asked in my message of 16th or the additional three in my message of 18th, and it is clear that you have not appreciated their significance.

By the way, the exact behaviour of VLOOKUP() is affected by options at Tools | Options... | LibreOffice Calc | Calculate. You will need to understand those.

Oh, and do you remember my suggestion that you might do this job manually? I'm pretty sure I could have done this more quickly that just trying to help you!

I trust this helps.

Brian Barker

#7

Hi Brian and all:

  I'm really sorry by my fail using LO spreadsheet and follow yours
advises. Besides I didn't know the options of tools (LO-Calc-Calculate)

  Thank you again,

Regards,

Jorge Rodríguez