Por favor ayuda en excel

Occasional Visitor
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
1 Reply

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