How to match two similar datas?

[user3 (Hyeonwook OH)]

Hi, I have a question!

Im trying to match two similar data in same file. Let me show some pics about it.

These data in sheet 1 are standard of description about material.

and these data in sheet 2 are the data that I matched with Standard in sheet 1.
The red colored data not follow standard or are new data.
In this picture, Standard(sheet1) shows “PL 16” and data in sheet 2 shows “PL16”. there are difference about spacing between latters.

I want change PL16 to PL 16, and all data which not follow standard except new data

Is there solution for this?

[ppr (Peter Preuss)]

Let us divide it into two parts:

• detecting items out of standards, by comparing the presence from one value in other sheet
• value corrections (we cannot see an example for SLIP… in Sheet 1)

for the first you can do for getting a list with distinct non standards

Assign Activity:
LHS: arrS1Values | datatype: String - a string Array
RHS: dt1.AsEnumerable.Select(Function (x) x(0).toString.Trim).Distinct().toArray

Similar we can do for sheet2
LHS: arrS2Values | datatype: String - a string Array
RHS: dt2.AsEnumerable.Select(Function (x) x(0).toString.Trim).Distinct().toArray

Then we can find the non standards by

Assign Activity:
LHS: arrNonstandards String()
RHS: arrS2Values.Except(arrS1Values).toArray

For the corrections we can look for following options:

• Correction rules e.g Regex pattern and replacements
• Lookups in combination with a rating on how PL16 is similar to a value in the standard list and using this standard value for correction. For this we can check e.g.:
  Levenshtein Distance Algorithm-String Comparison - RPA Component | UiPath Marketplace