Find match and append comments in new column

[Vandhana_Chandran]

Hi All,

I have two files with different column names. I have to compare a column from file A to file B if there are matching values. If found in File B then add a comment (Available) under a new column in File A. And also check for the corresponding value in a different column in File B and add that value as a part of comment. 

File A column names -  ISO2_Country Code, ERP Value, Name & Comments
File B column names - Country, Region, Region Description

1. Compare "ISO2_Country Code" with "Country" column

2. For all the matches found compare the "Name" column from file A to "Region Description" column from file B.
3. If there is a match set the "Comments" column to Available. And check for the corresponding value in column "Region" and update the same in comments after the Available.
4. Else set the "Comments" column to Not Available.

Thanks in advance
Regards,
Vandhana C

[OTrieger]

@Vandhana_Chandran 
I think that by using a Join tool you will solve your problem, at least point 1 and then you can use Formula tool to do the rest.

[TUSHAR050392]

Hey @Vandhana_Chandran You can use a combination of Find Replace and Formula tool to achieve it using below steps -

1. Use Find replace - F input will be File A and R input will be File B. Select ISO2_Country Code as Find Within Field and Country as Find Value. Then select Region Description, Region in Append Field(s) to Record section. If you want other columns, then select them as well.

2. Use a formula tool to update the comments column by selecting it and writing the formula - IF Name = Region Description THEN 'Available - ' + Comments ELSE 'Not Available' ENDIF

Let me know if this works.

[Vandhana_Chandran]

Hello @TUSHAR050392  - I tried your way and other ways as well, not able to get the expected output. I have explained the scenario in detail, please help if possible!

 I have attached the snapshots for how the data looks like in both the FIle A and File B below:

File A:

Key Points :
1. Considering all columns have blanks.
2. Value column is either just numbers like 01, 09 or just alphabets like DAY and NY or alphanumeric like 2B and 3N6.
3. Name Column is just alphabets with underscore and hyphen and some accent characters.
4. Description column as words seperated ny underscore.
5. EValue column could be just numbers or alphabets both ranging from 2-3 length long.

6. The ISO2_CountryCode column having 2 word length alphabets.

7. Observation column is something that has to be created using the formula tool where we compare the "ISO2_CountryCode" column from File A with "Country" column in file B. If there is a match we update the observation column as Available in FIle B. Evalue is "NY" and Region Name is "New York".  Evalue should be picked from "region" column and Region Name should be picked for the corresponding record from "Region Description" column from file B. If there is no match then we update the observation column as Missing in FIle B.

File B:

Key Points :

1. Only the Region and Region Description have blanks.
2. Country can range from 2-3 word length.

3. Region can have combination of alphanumeric or just alphabets or just number values.

4. Region description are just alphabets (string) with special characters such as hyphen, underscore, brackets and accent characters.

Hope this is clear.

Thanks

[TUSHAR050392]

Hey @Vandhana_Chandran What is the issue that you are facing with my solution. If you are just trying to look for data from File A that matches with File B and then do the data manipulation for matched/unmatched value then it should work.

Can you provide some dummy data with input and expected output to explain the issue because right now if I see the files provided by you, none of the ISO2_CountryCode value in File A match with country in File B. 

[Vandhana_Chandran]

Hey @TUSHAR050392 ,

I have now modified the files data for better and clear understanding. I hope now you're able to understand what exactly I am trying to do.

File A:

 

File B:

 
Thanks