¿Cómo ordenar códigos numéricos y alfanuméricos en excel?
Tengo una lista de códigos numéricos y alfanuméricos ubicados en una columna de excel que necesito ordenar . Son códigos son de secuencia recursiva, inicia con 1, después 11, 111 hasta llegar a 2 y repetir el proceso. Por ejemplo:
Columna 1-------------------------------------------------- Columna 2
1 1
2a 1a
1b 1b
1a 11
11 11a
2 2
11a 2a
¿Cómo hago para que excel pueda ordenar los datos de la forma presentada en la columna 2?
H o l a:
Te preparé una macro para ordenar los códigos.
Pon tus códigos en la columna "A", a partir de la fila 2.
En la columna "B" te pondrá los códigos ordenados.
Pon la siguiente macro en un módulo para ejecutarla:
Dim codigos As New Collection
Sub OrdenarCodigos()
'Por.Dante Amor
Application.ScreenUpdating = False
ordenar
cortes
conca
Application.ScreenUpdating = True
MsgBox "Códigos ordenados", vbInformation, "ORDENAR CÓDIGOS"
End Sub
'
Sub conca()
'Por.Dante Amor
For i = 2 To Range("J" & Rows.Count).End(xlUp).Row
Cells(i, "B") = Cells(i, "J") & Cells(i, "K")
Next
Columns("D:K").ClearContents
End Sub
'
Sub cortes()
'Por.Dante Amor
ant = Left(Cells(2, "D"), 1)
j = 2
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row + 1
cod = Cells(i, "D")
If ant <> Left(cod, 1) Then
u = Range("G" & Rows.Count).End(xlUp).Row
On Error Resume Next
Range("H2:H" & u).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "0"
On Error GoTo 0
Set h1 = ActiveSheet
With h1.Sort
.SortFields.Clear: .SortFields.Add Key:=h1.Range("G2:G" & u)
.SortFields.Add Key:=h1.Range("H2:H" & u)
.SetRange h1.Range("G2:H" & u): .Header = xlGuess: .Apply
End With
Range("H2:H" & u).Replace What:="0", Replacement:=""
u2 = Range("J" & Rows.Count).End(xlUp).Row + 1
Range("G2:H" & u).Copy Range("J" & u2)
Columns("G:H").ClearContents
j = 2
End If
cad = ""
For k = 1 To Len(cod)
If IsNumeric(Mid(cod, k, 1)) Then
cad = cad & Mid(cod, k, 1)
Else
Cells(j, "H") = Mid(cod, k, 1)
End If
Next
Cells(j, "G") = cad
ant = Left(Cells(i, "D"), 1)
j = j + 1
Next
End Sub
'
Sub ordenar()
'Por.Dante Amor
Columns("B:K").ClearContents
Set codigos = Nothing
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
x = Cells(i, "A").Text
agregar x
Next
j = 2
For i = 1 To codigos.Count
Cells(j, "D") = codigos(i)
j = j + 1
Next
Set codigos = Nothing
End Sub
'
Sub agregar(dato)
'por.DAM agrega los datos únicos y en orden alfabético
For i = 1 To codigos.Count
Select Case StrComp(codigos(i), dato, vbTextCompare)
'Case 0: Exit Sub 'ya existe, no lo agrega
Case 0, 1: codigos.Add dato, Before:=i: Exit Sub 'agrega antes
End Select
Next
codigos.Add dato 'lo agrega al final
End SubSigue las Instrucciones para un botón y ejecutar la macro
- Abre tu libro de Excel
- Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11
- En el menú elige Insertar / Módulo
- En el panel del lado derecho copia la macro
- Ahora para crear un botón, puedes hacer lo siguiente:
- Inserta una imagen en tu libro, elige del menú Insertar / Imagen / Autoformas
- Elige una imagen y con el Mouse, dentro de tu hoja, presiona click y arrastra el Mouse para hacer grande la imagen.
- Una vez que insertaste la imagen en tu hoja, dale click derecho dentro de la imagen y selecciona: Tamaño y Propiedades. En la ventana que se abre selecciona la pestaña: Propiedades. Desmarca la opción “Imprimir Objeto”. Presiona “Cerrar”
- Vuelve a presionar click derecho dentro de la imagen y ahora selecciona: Asignar macro. Selecciona: OrdenarCodigos
- Aceptar.
- Para ejecutarla dale click a la imagen.
Hola Dante!! Primero muchas gracias por tu ayuda. Esperaba unas instrucciones y esto superó mis expectativas.
Solo tengo dos pequeños problemas, y si no es mucha molestia, quisiera que me indicaras como solventarlos.
1) Al ejecutar el macro, aplica perfecto el criterio de ordenación pero empieza a ignorarlo cuando supera los dos dígitos. Por ejemplo: 1; 11,11a,11,b 12...111. Esperaría que el 111 quede seguido del 11 y asi con los otros casos.
El otro problema es: esta ordenacion de códigos tenia como fin ordenar una serie de datos asociados a ese código alfanumérico (una base con 6 columnas). Este macro solo me permite ordenar los códigos de forma aislada. Necesitaria, por ejemplo:
Nombre Código Nombre Código
Maria 1 Maria 1
Juan 2 Marta 11a
Marta 11a Manuela 111
José 3 Juan 2
Manuela 111 José 3
¿Podría indicarme como llegar a eso? De antemano, mil gracias por la ayuda que me has dado.
Saludos.
H o l a:
No te doy unas instrucciones, ya que el orden que deseas obtener es muy particular de tu necesidad. Para excel, los número son primer que las letras y tu quieres que el orden sea diferente:
1a
1b
11
Si los ordenas por excel, el resultado es:
11
1a
1b
Es por eso que te estoy enviando una macro, para que separe las letras de los números, entonces ordene los números y vuelva a juntar los números y las letras. La macro lo realiza para cada dígito, porque si lo haces por número, nuevamente tienes que excel te ordena así:
1
2
11
Y tu quieres esto
1
11
2
En mi macro faltó revisar cuando no hay subordinados.
En tu primer ejemplo tenías:
2
2a
Y ahora en tu nuevo ejemplo solamente tienes:
2
Pero ya le hice las adecuaciones, vuelve a probar con esta macro:
Dim codigos As New Collection
Sub OrdenarCodigos()
'Por.Dante Amor
Application.ScreenUpdating = False
ordenar
cortes
conca
Application.ScreenUpdating = True
MsgBox "Códigos ordenados", vbInformation, "ORDENAR CÓDIGOS"
End Sub
'
Sub conca()
'Por.Dante Amor
For i = 2 To Range("J" & Rows.Count).End(xlUp).Row
Cells(i, "B") = Cells(i, "J") & Cells(i, "K")
Next
Columns("D:K").ClearContents
End Sub
'
Sub cortes()
'Por.Dante Amor
ant = Left(Cells(2, "D"), 1)
j = 2
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row + 1
cod = Cells(i, "D")
If ant <> Left(cod, 1) Then
u = Range("G" & Rows.Count).End(xlUp).Row
If u > 2 Then
On Error Resume Next
Range("H2:H" & u).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "0"
On Error GoTo 0
End If
Set h1 = ActiveSheet
With h1.Sort
.SortFields.Clear: .SortFields.Add Key:=h1.Range("G2:G" & u)
.SortFields.Add Key:=h1.Range("H2:H" & u)
.SetRange h1.Range("G2:H" & u): .Header = xlGuess: .Apply
End With
Range("H2:H" & u).Replace What:="0", Replacement:=""
u2 = Range("J" & Rows.Count).End(xlUp).Row + 1
Range("G2:H" & u).Copy Range("J" & u2)
Columns("G:H").ClearContents
j = 2
End If
cad = ""
For k = 1 To Len(cod)
If IsNumeric(Mid(cod, k, 1)) Then
cad = cad & Mid(cod, k, 1)
Else
Cells(j, "H") = Mid(cod, k, 1)
End If
Next
Cells(j, "G") = cad
ant = Left(Cells(i, "D"), 1)
j = j + 1
Next
End Sub
'
Sub ordenar()
'Por.Dante Amor
Columns("B:K").ClearContents
Set codigos = Nothing
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
x = Cells(i, "A").Text
agregar x
Next
j = 2
For i = 1 To codigos.Count
Cells(j, "D") = codigos(i)
j = j + 1
Next
Set codigos = Nothing
End Sub
'
Sub agregar(dato)
'por.DAM agrega los datos únicos y en orden alfabético
For i = 1 To codigos.Count
Select Case StrComp(codigos(i), dato, vbTextCompare)
'Case 0: Exit Sub 'ya existe, no lo agrega
Case 0, 1: codigos.Add dato, Before:=i: Exit Sub 'agrega antes
End Select
Next
codigos.Add dato 'lo agrega al final
End Sub
¡Muchas Gracias Dante!
Si bien el macro conserva unos problemitas para ordenar (por ejemplo: en la ordenación me salta del 31 al 32 cuando hay un 311), considero que tu última respuesta me ayuda a resolver el problema original.
Gracias por tu ayuda.
Es que no me estás poniendo los ejemplos completos.
En tus ejemplos mencionas solamente un dígito
1
2
3
Y en cada dígito van valores intermedios
1
1a
1b
2
2a
2b
3
3a
3b
Pero en este último ejemplo me estás poniendo 2 dígitos.
Si solamente fuera un dígito el orden de la macro es correcto
3
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
3.10
3.11
El 11 es mayor al 2, por eso el orden es:
3
31
32
311
Con solamente esta inforamción
31
32
311
No sé puede saber si el 311 pertenece al grupo 31 o si el 311 pertenece al grupo 3.
Deberías auxiliarte de algún punto o algún guión en cada grupo y subgrupo, para que puedas ordenarlos:
3.
3.1
3.2
3.3
3.11
4.
5.
20.
30.
31.
31.1
31.2
¿Ves la secuencia?
Si tienes 311, no sé si es 3.11 ó 31.1, incluso si llegas a tener trecientos grupos, podrías tener:
1.
10.
100.
200.
310
311.
¿Entonces el 311 en dónde se ordenaría?.?.?
Si decides poner un punto o un guión, me avisas y realizo una nueva macro.
‘Al final de mi respuesta hay un botón para valorar.
S a l u d o s . D a n t e A m o r. Recuerda valorar la respuesta. G r a c i a s
¿Revisaste lo comenté?
¿Cómo quedaría la numeración?
3.11
31.1
311.
‘Al final de mi respuesta hay un botón para valorar.
Hola Dante!
Disculpa que no conteste a la brevedad.
Para efectos de tu pregunta, la numeración quedaría como 3.11. Si sirve de algo te doy más información: Son aproximadamente 500 datos con códigos que van del 1 al 9. Todo lo demas, son códigos subordinados. En algunas series hay más subordinados que en otras.Por eso la variación en mis ejemplos.
Lo único fijo es que los códigos que comiencen en 1 serán parte de la serie 1:
1
1.a...
1.z;
1.1,
1.1.a...
1.1z;
1.11,
1.11.a....
1.11.z;
1.2,
1.2.a....
1.2.z,
1.2.1,
1.2.1.a...
1.2.1.z
1.2.2...
1.2.9;
1.3;
1.9;
2......y así hasta llegar a 9
Ahora, lo realmente importante es que al ordenar esta columna pueda ordenar el resto de las filas. Como te comenté, es una base de datos con varios campos que me interesa ordenar a partir de esta columna con códigos alfanúmericos. Tu macro, si bien me ayudo a organizar la mayoría de los datos, no me permite organizar la hoja de cálculo donde esta esta columna.
Si hay una posibilidad de hacer esto último, lo agradeceria mucho.
Saludos.
Entiendo tu solicitud.
Pero el caso es que no tienes 1.11
El caso es que tienes 1.1.1
Si tuvieras 1.11 entonces yo estoy en lo cierto.
1.
1.1
1.2
1.11
Pero lo que tu tienes es otro subindice:
1.1
1.1
1.1.1
1.2
En ese caso el 1.1.1 si va antes que el 1.2
El problema no es de la macro, el problema es cómo tienes estructurada la numeración, no podré hacerte la macro hasta que determines cómo serán los subindices
Te pongo un ejemplo de estructura:
A = Grupo
B = subgrupo
C = SubSubgrupo
D = subsubgrupo
a.
a.b
a.b.c
a.b.c.d
No importa cuántos subgrupos tengas, pero me tienes que decir en dónde empieza, como te lo dije antes, este número 311, ¿en dónde va?
3.1.1
3.11
31.1
311.
Solamente tú sabes que los grupos son del 1 al 9. Pero yo se lo tengo que decir a la macro, entonces quedarían 2 posibilidades:
3.1
3.11
Tienes que poner los puntos para saber cuándo empieza un subgrupo.
Como ya viste, necesito que seas muy clara con la información, porque yo no estoy viendo lo que tú tienes.
Ahora mencionas que tienes una base y que quieres ordenarla, pero no lo habías comentado desde el principio.
Vamos a realizar lo siguiente, la macro funciona con las indicaciones iniciales. Valora esta respuesta y crea una nueva y me pones los ejemplos reales de cómo tienes la base, puedes incluso poner una imagen.
Pon la nueva pregunta en el tema de excel, en desarrollo de la nueva pregunta escribe "Para Dante Amor".
Sal u dos
