Sort equal rows

Asked

Viewed 139 times

0

I have 4 columns in excel: A,B,C,D right? Only that I need to order to make a comparison because all the information is disorganized I explain myself:

Original Archive in . CSV (Disorganized Information )

 A(Inglês)         B (Tradução)             C(Inglês)                D (Trad.)
    "xxx.yyy.Hello"            "Oi"        "xxx.yyy.Text"         Texto
    "xxx.yyy.Green"            "Verde"     "xxx.yyy.Hello"        Hello

How I’d like it to stay that way:

   A(Inglês)         B (Tradução)             C(Inglês)                D (Trad.)
"xxx.yyy.Hello"            "Oi"             "xxx.yyy.Hello"        "Hello"  
"xxx.yyy.Green"            "Verde"           "xxx.yyy.Text"         "Texto"

In short the values of fields A and C, to make it easier to compare translations.

Note: The file contains approx 4000 lines in each row (A,B,C,D). There is a way to perform this type of sorting in excel, plugin, etc... Thanks in advance!!!

excel csv

  • Wouldn’t it be better to structure the file with only 2 columns ? English and translation ? Or 3 columns if you want two translations

  • I happen to have 2 files. csv understand? Dae I’ll try to put them the way I told you. File 1 would have columns A and B , File 2 columns C and D

  • But you’re trying to put the two files together in one ? Joining the entries that exist in the two ?

  • But how will I do that if the words are all scrambled? doing it manually would take hours.... Remember that there are almost 4000 lines

  • 1

    And why not the tool to remove duplicates? That would already give to put all followed in the same spreadsheet and remove duplicates

  • I explain : fields A and C are computer codes, only I wanted to do more didactically here, but here we go: Field A Field C xxx.yyy.Green xxx.yyy.Green @danieltakeshi

  • @Rodrigohackzexploitz Do you have any experience in Excel VBA? Or this should be done only with formulas and standard tools?

  • I am not a programmer..... I have always acted in support..... my notions of programming are very basic and many years ago I do not compile anything..... rsrsrss

  • I suggest putting the example of the original sheets and what you intend to do, the ultimate goal. This seems to me a XY problem

  • @Isac , I don’t know if it will make a difference anymore I edited exactly how the original fields are. These are codes for a file . json I passed to. csv and later passed in excel. Note: columns ( A and B ) belong to archive1.csv columns ( C and D ) belong to archive2.csv understand?

  • @Rodrigohackzexploitz Do you know if there are duplicates of the same word in each csv file? If there is, I can only think of VBA to solve the problem.

  • there are of course..... because my idea is to compare the translated fields and those that are not yet translated. @danieltakeshi

  • And how do you know which ones are yet to be translated ?

  • By checking fields B and D, I will know if they have been translated or not.

Show 9 more comments

2 answers

0

It is possible to perform what was requested by Excel formulas.

Input Data

Dados Amostra

Upshot

Resultado

How to do?

New formulas were inserted in columns F,G,H and I to compare the two columns and return in the same row when there are equal words

Column F

=SE($H1="";"";A1)

Column G

=SE($H1="";"";B1)

Column H

=SE(É.NÃO.DISP(CORRESP(A1;C:C;0));"";ÍNDICE(C:C;CORRESP(A1;C:C;0)))

Column I

=SE($H1="";"";ÍNDICE(D:D;CORRESP(A1;C:C;0)))

Problem

When there are duplicates in the second file, it will not work as described.

So the suggestion to fix this problem (if any) is to look at this link

  • I really appreciate your help. 1) But would I have to make this shape manually for the 4000 lines? that would be a long time... or did I get it wrong? 2) The idea would be to "not lose" content of the data as it appears... got 1 queue less.

    1. No need to perform manually, only use the .Autofill double-clicking the bottom right corner of the first cell, where there is a black square. 2) Well, this formula only aligns what is equal in file 1 and 2, words that are unique do not appear. Check the link I passed if you want to change your spreadsheet for something more flexible and complete.

  • If someone is still reading..... Would it be very difficult to do this with some programming language like Python, Java, C# ??? ... say that Python is simpler.... Because I believe that this question I will see on other occasions.

  • @Rodrigohackzexploitz I find Excel VBA easy and all other alternatives can also be used. But if your knowledge of programming does not meet the needs, I suggest using ready-made translation software like Poedit and Transifex (these two most common for websites and databases with PHP). Just give a searched to find many others.

0


I suggest using the formula PROCV. You can build a sheet with the following columns:

Referencia | Tradução PT | Tradução EN

Something like:

inserir a descrição da imagem aqui

In which the Tradução PT and Tradução EN are obtained by searching the sheet/document where they are. References being coincident on both sheets would just copy from one of them and paste into this new sheet.

To the Tradução PT would use the formula:

=PROCV(A2;Folha2!$A$1:$B$4;2;FALSO)

In which A2 is the reference and Folha2!$A$1:$B$4 is the sheet with the translations in Portuguese. Visually the Portuguese translation sheet would look like this:

inserir a descrição da imagem aqui

Note that the order of references in this sheet is irrelevant as values are obtained by searching.

To the Tradução EN the formula would be the same but on another sheet:

=PROCV(A2;Folha3!$A$1:$B$4;2;FALSO)

With this you can quickly see which ones are missing. You can even make a formula next to it to always indicate that a translation is missing like this:

inserir a descrição da imagem aqui

On that field Em falta uses the formula:

SE(B2=C2;"FALTA";"")

That only compares if both translations are equal and if they are says the translation is missing.

With this you can filter the missing hairs by considerably simplifying the handling of the sheet. You may even know how many translations are missing altogether, with a formula like =CONTAR.SE(D2:D5;"FALTA") specifying as range the all values for the column Em falta.

  • I appreciate your help... plus the data of field A and C are equal with codes ( xxx.yyy.Word)... another thing that they are shuffled, are not straight as you put in the photo, so first you have to order... to stay all in the same queue, and this is the complex for me, understand?

  • @Rodrigohackzexploitz In the example I gave the order has no relevance, it can be whatever you want, you can even sort by the reference, because the two columns beside get the corresponding value by searching.

  • I really appreciate it! I’ll run some tests and I’ll get back to you :)

Browser other questions tagged excel csv

You are not signed in. Login or sign up in order to post.