Sort equal queues

Question

Sort equal queues

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have 4 columns in excel: A, B, C, D right? But I need to sort it to make a comparison because it's all the disorganized information I explain:

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 would you like it to look like this:

   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 must be the same as to make comparison of translations easier.

NOTE: The file contains approx. 4000 rows in each queue (A, B, C, D). Is there any way to do this sorting in excel, plugin, etc ... Thank you!

excel csv

asked by anonymous 10.10.2017 / 13:08

2 answers

0

You can accomplish what was requested by Excel formulas.

Input Data

Result

Howtodoit?

NewformulashavebeeninsertedintocolumnsF,G,H,andItocomparethetwocolumnsandreturnonthesamelinewhenwordsareequal

ColumnF

=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 function as described.

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

10.10.2017 / 14:37

Vector by Parameter in C Status Return Javascript or PHP API MercadoPago

score 0 · Accepted Answer

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

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

Something like:

WhereTraduçãoPTandTraduçãoENareobtainedthroughasearchinthesheet/documentwheretheyare.Thereferencesbeingmatchedonbothsheetswouldjustcopyfromoneofthemandpasteonthisnewsheet.

ForTraduçãoPTwouldusetheformula:

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

WhereA2isthereferenceandFolha2!$A$1:$B$4isthesheetwiththetranslationsinPortuguese.VisuallythetranslationsheetinPortuguesewouldlooklikethis:

Notethattheorderofreferencesinthissheetisirrelevantbecausethevaluesareobtainedbysearching.

ForTraduçãoENtheformulawouldbethesamebutonanothersheet:

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

Withthisyoucanquicklyseewhicharemissing.Youcanevenmakeaformulaonthesidetotellyouwheneveratranslationismissinglikethis:

WheretheEmfaltafieldusestheformula:

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

That only compares if both translations are the same and if they say the translation is missing.

With this you can filter through the missing ones by considerably simplifying the handling of the sheet. You can even know how many translations are missing in the total, with a formula such as =CONTAR.SE(D2:D5;"FALTA") specifying as an interval all values for the Em falta column.