matching two text columns in two different tables by a part of the words

[MrLowland]

Hello,

I want to compare two columns in two different tables by only a part of the text. After that I want to count them.

Let me try to explain what I mean.

Table-1: contains the name 7-zip.

Table-2: contains the same name only it also has the version in its name. E.g 7-Zip 15.14 (x64), 7-Zip 16.02 (x64 edition), 7-Zip 16.04, ....

If you use a slicer on table-2 you get al the 7-Zip xx.xx names. How do I compare these two tables so I can count how many 7-Zip files there are in table-2.

[gooranga1 · Accepted solution]

Could you use LOOKUPVALUE? It depends on how clean the data is, so this has to have a space " " between the name and version number. 

LU = LOOKUPVALUE(Table1[Column1],Table1[Column1],LEFT(Table2[Column1],FIND(" ",Table2[Column1])-1))

[MrLowland]

Hi Gooranga1,

Could you use LOOKUPVALUE? It depends on how clean the data is, so this has to have a space " " between the name and version number. 

The formula works great. Can you explain how it works?

Because I add more software where the name contains more spaces. E.g. Microsoft Office 365 ProPlus. Now it will not give me the right software name in LU.

Table1 contains:

Table2 contains:

I do not see Microsoft Office at Microsoft Office 365 ProPlus. Can you explain why?

[gooranga1]

Hi @MrLowland,

Yes it's not going to work well for software names with spaces. The issue you will have is that you a need clear delineation between the software titles/names and the subsequent version numbers.

The LookupValue takes a value from 1 table and then matches that to another table, in the example posted I used the space between the name and the version numbers to strip out the software name. BUT it relies on the space between version and software name. 

If your software name is going to contain spaces as well then working out when the versions start and the software names ends becomes quite challenging and without that matching between 2 tables is also very tricky.

Do you have any control over the format of this data?

[MrLowland]

Hi @gooranga1,

Thanks for your explanation.

No I do not have any control over the format of this data. It is already in a system end I only can export it.

[gooranga1]

Hi,

Not sure you need a lookup but a way of stripping out the software name into a new column? If the data is that inconsistent you may have to clean it manually. How many rows is it in total?

[MrLowland]

Hi,

It's about 2400 rows.

[Greg_Deckler]

Perhaps use the multiple selections of the slicer coupled with the Search functionality of the slicer.

[Anonymous]

I want to compare two columns in two different tables by text. After that I want to count them . For Example Equipment Table Having Status Column with value “Not Submitted” & Same Values with Same Column Name in MASTER LIST Table. So I want total “Not Submiited” from Both table. Attached alos is PBX filehttps://www.dropbox.com/s/aqpwx0ov1t5j6z1/Closeout%20Summary%20-%20Mobility%20Pavilion.pbix?dl=0

[MrLowland]

Hi Smoupre,

Perhaps use the multiple selections of the slicer coupled with the Search functionality of the slicer.

Thanks for answering. It is a solution but it is to muchwork. I would like to have it automated so you can see the total at a glance.