Dear all,
I have two different software that generates similar csv.
The first outputs for example 12 Angry Men;1957
The second one only 12 Angry Men (1957)
Is there an easy way so that I can have all the data in two columns in a similar pattern?
Transforming the first in the second or vice versa is fine as long as I can compare them.
Thank you very much in advance.
Regards
Not difficult until ; or ( ) is also in some titles: Import the first csv into sheet1, selecting ; as separator for columns. Then import the second file to sheet2 selecting ( and ) as seperator - there is a box to the right for unusual seperators.
.
If sequence is identical you could now create a simple comparision in sheet3 with for example in A1
=IF(Sheet1.A1=Sheet2.A1;"";Sheet1.A1&" // "&Sheet2.A1)
to show only differing cells. (Drag down as far as necessary and also to column B)
.
If Sequence is different you may use VLOOKUP to find an entry, but remember to set the option for data NOT sorted, even if you sorted the data.
Thank you very much. I did not think of that, and it helped a lot.
I followed your advice but I realized that it creates a minor issue on few rows have some text in parenthesis. If I import those as you suggested, the text is split in several columns. I did not mention it earlier and I apologize.
Right now I am fixing those by hand one by one, but maybe there is a more elegant solution.
Anyway, thank you again for taking the time to reply
Yes that is the trick to let ; and () vanish.
.
As an alternative you could import without separator into one column, then use find&replace to replace ; with ’ (’ and append ) = Search regular expression (.*) and replace with $1) to convert the ;-version to the second form.