Replace data and join two different files

[arieta]

Hello, I would like to replace some data with specific text, different each time. Please see the example below:

1st file:

Column A (as is now)	Column A (as I need it to be)	Column B (as is now)	Column B (as I need it to be)
1st	First line	Stage III	Stage III
2nd+	Second line	Stage II	Stage II
3rd	Third line	[blank]	All stages
2nd	Second line	Zzz [text]	All stages
Xxx [text]	All lines	Stage I	Stage I
Yyy [text]	All lines	Ddd [text]	All stages

Then, when I’ve created my columns as I need them to be I have to match them with a second file, for example, when column A and B include ‘First line’ and ‘Stage III’, respectively, the data from my second file match them. See example below:

2nd file:

Column X	Column Z	Data that need follow
First line	Stage III	0.005
Third line	All stages	0.2
All lines	Stage I	0.3

Data as I need them to be (join 1st and 2nd files):

Column A	Column B	Column X	Column Z	Data need to follow
Third line	All stages	Third line	All stages	0.2
First line	Stage III	First line	Stage III	0.005
All lines	Stage I	All lines	Stage I	0.3

Thank you in advance for your help!

[fmvizcaino · Accepted solution]

Hi @arieta ,

I'm attaching a solution to your problem.

1. Find/replace + formula tool: Fill the blanks and replace ordinal numbers to text.

2. Join: join 1st and 2nd table

Best,

Fernando Vizcaino