Forum Discussion

asalazartorres's avatar
asalazartorres
Copper Contributor
Apr 05, 2017

Por favor ayuda en excel

Hola buen dia. Por favor alguien que me pueda ayudar con una formula. Nececito transponer de columnas a filas con estas caracteristicas: en la primera columna que llamare a, tengo los nombres de unos clientes y en la segunda columna que llamare b, los numeros de contacto. En la columna a tengo los nombres repetidos ya que en la en la columna b tengo los numeros de contacto unicos. Mi pregunta es como trasponerlos para que en la primera columna solo me quede el nombre de mi cliente como unico y en las demas columnas los numeros de contacto. agradezco a la persona que me pueda ayudar y darme esta oportunidad de aprender gracias
Excel Desktop
Formulas & Functions

1 Reply

  • Deleted's avatar
    Deleted
    Apr 09, 2017

    Hello,

     

    I translated your question with an online translator and I hope I understood your request correctly.

    I attached a file with some sample data where some names are in column A and some numbers are in column B. The column with the names includes duplicates, for example Anna or Mike.

    The output tranponed range starts from column F until column N, assuming you have max 7 duplicates for one name. You can extend this, of course. The sheet also includes a helper column in D and the first row starting from cell G1 is used as index row.

     

    The helper column in D created some unique ID for the names. This is done with the formula =""&$A3&"-"&COUNTIF(A$3:A3;A3) So, you get Mike-1, Anna-1, Anna-2 and so on as result. This makes the ID's searchable for extracting the contact numbers for each name. Example: in cell F3, you have Mike, so by using the formula G3=""&IFERROR(INDEX($B$3:$B$14;MATCH($F3&"-"&G$1;$D$3:$D$14;0);1);"-"), it searches the contact number for the first occurence of Mike. And so on.

     

    Now, for filtering out the duplicates from the names list, I set the first entry to the first name from the source data. In cell F3 the formula is quite simple =""&A3. For the next entries, I used an array formula. Note: array formulas should entered without the brackets { and }, but finished by pressing Ctrl+Shift+Enter. So, in cell F4 you have: {=IFERROR(INDEX($A$3:$A$14;MATCH(1;(COUNTIF(F3:F$3;$A$3:$A$14)=0)*($A$3:$A$14<>"");0));"")} which only considers a name, if it is not already in the preceding list.

     

    Of course, you should adapt the sample to your sheet. Hope this helps.

     

    Best regards,
    Mourad