Macro para borrar los valores que sean iguales al de arriba

En ocasiones tenemos listados en que los elementos importantes son los primeros que tienen el primer valor diferente en una columna. Por ejemplo, en un listado de localidades, las localidades corresponden a una ciudad, cada vez que aparece el cambio de ciudad es el valor que hay que mantener, el ejemplo se ve en seguida.

En el ejemplo se aprecia un listado de localidades que conforman ciudades. Cada que hay cambio de ciudad es el renglón que hay que mantener.

La macro es la siguiente:


Sub EliminaIgualAbajo()

Anterior = 9999999

For N = 1 To 300

 Valor = ActiveCell.Value

 If Valor = Anterior Then
     Selection.EntireRow.Delete
 Else
    Anterior = Valor
    ActiveCell.Offset(1, 0).Range("A1").Select
End If

Next

End Sub

Y el resultado queda así:

Como sustituir asteriscos en Excel por otro valor, y el nombre de Dios

Hay un problema común, por ejemplo, en algunas bases de datos se les ocurre sustituir los valores confidenciales, pero cuando quiero en Excel sustituir * con otro valor, por ejemplo 9999 o A. En la imagen se muestra un ejemplo:

 Si yo -o tú, claro está- utilizas el buscador de Excel, y le dices sustituir "*" por otro valor, entenderá que lo que quieres hacer es sustituir cualquier valor (pues así se dice en idioma Excel cualquier cosa, "*") por A, y pondrá puras Aes, como se ve en la siguiente imagen:

Realmente "*" es como el nombre de Dios que no se puede pronunciar y su uso tiene efectos desconocidos. Bien. 

 Para ello está esta macro:


Sub SustituyeAsterisco()
   
    For N = 1 To 16
   
    ValorAObservar = ActiveCell.Value
   
     If ValorAObservar = "*" Then
      ActiveCell.Value = "A"
     End If
   
   
    ActiveCell.Offset(1, 0).Range("A1").Select
   
    Next
   
End Sub

Para integrarla simplemente se graba cualquier macro, se detiene, se va al menú de macros se dice modificar y ahi se pega, entonces quedará grabada. Cuando se haga eso el resultado será el siguiente:

En otra ocasión veremos como prescindir del valor límite en For Next, para que la macro actúe independientemente del número de registros.

Tu vida en un metro cuadrado. ¿Haz visto pasar tu vida?

Un artista plástico (Ignacio Uriarte) hizo una exposición en la Casa del Lago  de la UNAM. No es exactamente el tipo de arte que me gusta mucho, pero si me provoca algunas sensaciones, en este caso, agradables. Supongo que con eso basta. Uno de sus trabajos se llama Todos mis días y es simplemente una hoja de Excel, donde en cada celda están todos los días de su vida desde que nació hasta que creo esa obra. La sensación que me produjo fue extraña, todas las fechas en formato DD/MM/AA son visibles a un metro, y caben en una hoja de ploter. Que chica es la vida. Parece ordenada y realmente todos esos días debieron transcurrir diferente, sin embargo en el conteo son lo mismo.

Me decidí hacer mi propia hoja de todos mis días. El diseño será un poco diferente, cada columna será un año y tendremos tantas columnas como años. Evidentemente esto lo haremos con una macro, luego cada quien puede modificar los parámetros, incluso podemos, por ejemplo, diseñar una hoja donde el usuario anote a un recuadro con ayuda de un combo, su fecha de nacimiento y le genere automáticamente su hoja tipo "Todos mis días".

El procedimiento será el siguiente:
Lo primero que hay que hacer es cambiarle el formato a todas las celdas a tipo fecha, si no sabes como pregúntale a quién más confianza le tengas. Luego anota la fecha de tu nacimiento en la columna A, renlgón 1. Osea, así:

No es buena costumbre, pero a veces ante la prisa, es bueno ahorrar escritura de programación y en vez de teclear código grabar movimientos con las macros y luego injertarlos en el código. Haremos eso por esta vez para mostrar la técnica. Lo que queremos ahora es tener la columna A con todos los días de un año biológico, y como el primer año biológico como todos los demás tienen 365 días tenemos que copiar ese dato pero con un día aumentado en la columna de abajo, de forma que A2 es el ayer de A1, y así suscesivamente. Obviamente que es muy fácil hacer eso con un copy paste. Pero no nos serviría (salvo haciendo otras cosas) para la segunda columna, así que haremos el copy paste pero con una macro. Es decir esta macro copiara lo que hay en A1 en A2, primero copia luego baja y pone el valor. Primero hay que dar click en referencias relativas, decir grabar macro y proceder. Cuando aparezca la caja de diálogo de macro le damos un nombre, "Baja".

Cuando demos aceptar bajaremos una celda para quedar en A2. Al dar detener, quedará dada de alta una poderosisima macro que lo que hace es bajar una casilla. Ese será el código bajar que utilizaremos.
Repetimos el proceso para copiar pero con un uno aumentado, que en el contexto del formato de fecha será un día.

Podemos pedir el menu de macros y nos aparecerán los dos códigos que tenemos.

Cuando damos en el botón modificar, podemos acceder al código que se ve así:

Ahora si, tenemos este código:

Sub Bajar()
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Sub AumentaUnDia()
    ActiveCell.FormulaR1C1 = "=+R[-1]C+1"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Y con ese creamos este:

Sub CalculaColumna()

ActiveCell.Offset(1, 0).Range("A1").Select 'primera macro

For DiasDeUnAnio = 1 To 364
ActiveCell.FormulaR1C1 = "=+R[-1]C+1"      'segunda macro
ActiveCell.Offset(1, 0).Range("A1").Select
Next


End Sub

Esta macro lo que hará será llenar la primera columna con la secuencia de días que forman el primer año biológico. Así se verá cuando acabe de correr:

El siguiente paso es regresar a la B1, donde iniciará la columna con el segundo año de vida. Lo haremos del modo fácil.

En este momento, la macro te ha dejado en la casilla A366. Así que grabas una macro que se llama "CambiaSiguienteColumna" y te haces uno a la derecha y todo hasta arriba. Es decir, das grabar nueva macro, la llamas "CambiaSiguienteColumna", te mueves a la B366, luego das tecla fin, tecla arriba y detienes la macro. El resultado es que ahora tienes una macro con este código:


Sub CambiaSiquienteColumna()
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlUp).Select
End Sub


Cuando se ejecute este código desde cualquier casilla en el renglon 366 si irá al primer renglón de la siguiente columna. Este proceso se podría hacer también de otros modos, pero este modo intuitivo de manejo de las macros es mejor para el principiante.

Ahora repites una operación similar a aquella en la que sumaste un día, pero un poco diferente, como resultado de la macro anterior, quedas en la columna B1, así que ahora tienes que sumarle a la casilla de la izquierda 1 año, osea 365 días. Para hacer la macro: activas macro (cuidando que esté en referencias relativas), presionas signo de más (con esto estás en modo de espera de cálculo), tecleas 365, pones otro más, mueves la flecha izquierda, copias y enter. Apagas macro.
El proceso se ve así:

Y el código queda así, si le llamaste a tu macro "SumaUnAnio"


Sub SumaUnAnio()
' SumaUnAnio Macro
    ActiveCell.FormulaR1C1 = "=+RC[-1]+365"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Solo que como aprecias en la figura te deja en la celda B2, siendo que quieres estar en la B1 para aplicar tu primer macro, así que la regresas grabando una macro que suba una casilla, que es esta:



Sub subircelda()
    ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub

Y ahora vienen los suspiros, pues llegó la hora de generar tantas columnas como años. Se puede hacer haciendo referencia a las macros pues también son subrutinas, o bien directamente que es como procederemos. Yo tengo prácticamente 45, así que va así la cosa:

Sub TodosMisDias()

For Edad = 1 To 45
    'dias del primer anio, primer columna
    ActiveCell.Offset(1, 0).Range("A1").Select 'primera macro

   For DiasDeUnAnio = 1 To 364
 
    ActiveCell.FormulaR1C1 = "=+R[-1]C+1"      'segunda macro
    ActiveCell.Offset(1, 0).Range("A1").Select

  Next

'cambiar de columna
      ActiveCell.Offset(0, 1).Range("A1").Select
      Selection.End(xlUp).Select
'sumar un anio
      ActiveCell.FormulaR1C1 = "=+RC[-1]+365"
      ActiveCell.Offset(1, 0).Range("A1").Select
'subir una celda
      ActiveCell.Offset(-1, 0).Range("A1").Select

Next



End Sub




Ahora corre la macro poniendo tus parámetros de edad y fecha de nacimiento y tendrás esto.

Si cabe tu vida y puedes ver los días eres un joven si no como yo un anciano. Obtendrás una sensación muy agradable cuando veas correr la macro, lo que en verdad estarás viendo, es pasar tu vida, a la velocidad que hoy crees que pasó.

Palindromas y Excel

Ahora vayámonos de la oficina y hagamos algo divertido... bueno si no quieres, puedes regresar a la oficina.
Veremos cuantas palabras son palíndromas en español. Una palabra palindrómica es aquella que se lee igual de derecha a izquierda que de izquierda a derecha. Ejemplos

Oso -> osO
Ojo -> ojO

También se pueden armar frases, una vez saque unos muy buenos:
Ana Freud Huerfana
Claro el anterior no es perfecto por la H, pero en cuanto a sonido sí. ¿Te acuerdas cuando decían que el rock al revés le hablaba al diablo? Imaginate a un grupo de rock con una canción que hable de Ana Freud y al revés te diga que la chica era huérfana y por eso mató a su papá. Claro, simbólicamente.

O este otro:
Ay ojalá Bush suba la joya
Ala moro le derrota la torre del oro mala

Bueno, la idea es esa, como diría el decrépito chavo del ocho.
Ahora, veamos como podemos saber cuantas palabras solas forman un palíndroma, un diccionario de palíndromas, de hecho le puede servir a un palindromista para hacer palíndromas complejos, como los últimos que vimos. Por ejemplo, ala, oro son palíndromas, adentró de otro palíndroma.

Lo primero que tenemos que conseguir es un diccionario. Hay muchos, uno muy bueno está aquí:

http://snowball.tartarus.org/algorithms/spanish/voc.txt

En las páginas de Hackers hay muchos, pues estas malas personas se dedican a romper códigos para lo que necesitan múltiples palabras. Hacen combinaciones inteligentes a partir de esas palabras y entonces rompen passwords.

Lo siguiente que tienes que hacer es darle una formateada y quitarle los absurdos acentos, pasar todo a mayúsculas y quitar los resabios del origen de la vida que son las eñes y las us con diéresis y todos esos adornos del idioma español. Claro que no debes decir que hiciste eso a nadie de sociedad, te pueden decir que eres un salvaje que cometes atentados contra la lengua.

Luego lo debes pasar a cualquier procesador de texto y de ahi como texto llano a otro archivo. Lo importarás en Excel y convertirás todo a mayúscula con la función para ello. Se que sueno imperativo y que ese tono no te gusta, pero si no lo hago, no harás tu tarea. Adelante.

Ahora, si copias la función de mayúsculas en todo el listado y luego lo copias como valor y borras la antigua columna tienes lo que queremos.

Deberás ahora borrar los espacios vacíos y tienes 28377 palabras, lo puedes salvar ya como una hoja Excel. Ahora nos sorprende que haya personas que se comuniquen con trescientas palabras.

Bien, ahora si podemos trabajar.

Nuestra macro lo que hará será ir bajando desde la celda B2, hasta la 28377 volteando las palabras. Para ello deberá hacerse a la izquierda la celda activa, tomar el valor, procesarlo, regresar a la derecha, vaciar el valor procesado, bajar, y así tantas veces como larga la lista es.

Para no escribir tanto código hay un buen truco que consiste en hacer una pequeña macro para cada acción de esas, es decir una para hacerse a la izquierda, otra para la derecha, otra para subir, otra para bajar, las untamos y ya solo llenamos el código faltante.

Tenemos varios códigos:


Se hace a la izquierda en el mismo renglón:
    ActiveCell.Offset(0, -1).Range("A1").Select
Se hace a la derecha:
    ActiveCell.Offset(0, 1).Range("A1").Select
Se hace hacia abajo:



    ActiveCell.Offset(1, 0).Range("A1").Select


El código comentado queda del siguiente modo:

-------------------------------------------------------

Sub Macro5()
Dim Palindroma, NuevaLetra, Palabra As String   'abre variables que usaremos
Dim N, M, LargoDePalabra As Integer


'
' Macro5 Macro
'
Palindroma = ""  'inicia dichas variables
NuevaLetra = ""

For M = 1 To 28377    'inicia el ciclo para todas las palabras que se tienen

'
ActiveCell.Offset(0, -1).Range("A1").Select   'se hace a la izquierda
Palabra = ActiveCell.Value                            'toma la palabra
ActiveCell.Offset(0, 1).Range("A1").Select    'regresa a la derecha

LargoDePalabra = Len(Palabra)                    'mide la palabra, cuantos caracteres tiene

For N = 1 To LargoDePalabra                      ' Examina la palabra del primer al último caracter
NuevaLetra = Mid$(Palabra, LargoDePalabra - N + 1, 1)   'extrael la letra numero n

Palindroma = Palindroma + NuevaLetra                             ' dicha letra la anota al final del palindroma

Next                                                                                   ' repite el proceso

ActiveCell.Value = Palindroma                                           'anota el palindroma
Palindroma = ""                                                                  'inicia
NuevaLetra = ""

ActiveCell.Offset(1, 0).Range("A1").Select                        'baja uno

Next
End Sub

-----------------------------------------------

El resultado es este:

Posterioremente, para saber cuales son palindromas, incertamos en la columna c1 para copiar en todos los renlgones el condicional:
=if(b1=a1,"PALINDROMA","")

Que significa, si la palabra en la columna de palabras es igual a la palabra en la columna de palindromas, anota palindromas, si no no anotes nada. Del siquiente modo:

Si posterior a eso, juntamos mediante un filtro todos los que resultaron palíndromas, obtenermos los 64 palíndromas de ese diccionario:

Resultan estos:
     A ABBA ABEBA ACA AEREA AFA ALA ALLA ALLA AMA ANA ANNA ASA B BAB BOB C CIVIC D DAD E EFE EJE ESE ESE EYE F FMF G H I ICI II III J K L M MENEM N NARRAN O O OJO ORO OTTO OXXO OYO P POP Q R RADAR RAPAR RECONOCER S SALAS SERES SOLOS SOMOS SOROS SUS T U V W X XIX XX Y

Realmente yo pensé que los resultados serían más sorprendentes, palabras nunca antes vistas, pero no, son las mismas de siempre, hasta por ahi aparece Oxxo. Las únicas que valdría la pena destacar son rapar, reconocer, y ya. Quizá examinando un diccionario de 200 mil palabras resulte algo, ya habrá ocasión de repetir esto. Como siguiente reto, para otra ocasión, haremos una relación de palabras que en al revés de español, son una palabra existente en inglés.

Nada más amiguitos, recuerden que lso palindromas evocan al diablo, así que este ejercicio no lo hagan más que en la presencia de un adulto.

Excel: como formar claves

Ahora el asunto, del ejemplo anterior, es que queremos que el elemento; Estado, 01; Municipio, 001; Localidad, 0001; por ejemplo, tenga la clave. 010010001. Eso se llama concatenar y aquí no recomendaremos macros sino una función muy simple:

En la imagen queremos que se concatenen D, E y F, en G.

Así que insertamos en G1, la función:
=concatenar(D1,E1,F1) y nos resulta esto:

Estaría bien, aunque podemos hacer que quede mejor si evitamos aquellos que no tienen clave en E, con un if quedando entonces así:

=si(e1<>"", concatenar(D1,E1,F1),"")

Lo cual significa que si el valor en e1, no es vacío, que concatene, y si no, no haga nada. Luego eso se copia y queda el final.

Que tiene un look así:

En Excel, el extraño caso de cero que se convierte en valor

Problema:

A veces, Excel insiste en convertir valores del tipo "0000" en un espantoso "0", que no nos sirve pues nos referimos a un código no a un valor. Eso también aplica en Excel que debe estar convencido de eso del cero a la izquierda, pues a veces, es imposible sustituir por ejemplo "0000", por "0001" pues ya sea por remplazo o por macros el "0000" lo convertirá en 1, ante un reemplazo.


Solución:
Regresamos a nuestra conocida base.

En la columna F, queremos sustituir los "0000" por "0001" y ocurre todo lo que hemos dicho. Por ejemplo, aquí se presenta el efecto de cambiar el dato con la opción de reemplazo:

Eso no es lo que queremos pues como dijimos "0000" y "0001" son códigos. Así que una macro sentará perfecto. Aquí está la macro, sólo que ahora le hemos puesto un bonito nombre:


Sub ComandanteMacro()

Dim Valor, NuevoValor As String
    Selection.Copy
For N = 1 To 780
Valor = ActiveCell.Value
Select Case Valor
Case "0000"
    ActiveSheet.Paste
Case Else
End Select
    ActiveCell.Offset(1, 0).Range("A1").Select
Next
End Sub



Nuestra Sub Macro, ahora se llama sub comandanteMacro. Le hemos dimensionado las variables que no significa otra cosa que hemos reservado espacio nominado en memoria para nuestras variables. Visual Basic, que es el lenguaje que viene en Excel, aguanta no definir variables, pero esta vez nos hemos revelado.

Si bien Excel no nos permite reemplazar, si nos permite copiar un valor como el que queremos en otra celda y guardarlo sin transformarlo a valor. Por ello, lo primero que haremos será copiar en la celda F2, desde donde empezaremos la macro. Una valor como el que queremos, y queda así la cosa:

Ese valor lo hemos puesto copiando de la celda F5 el valor. Ahora si simularemos un copiado manual. regresemos a nuestra macro que es una la subrutina Sub ComandanteMacro(). Esta, para correrse, debe iniciarse en la celda donde comienza el problema, en la F1.

---------------------------------------------------------

Sub ComandanteMacro()

Dim Valor, NuevoValor As String   'se definen variables
    Selection.Copy  'se copia la celda donde arranca el programa, que en este caso debe ser la F1

For N = 1 To 780  'inicia un largo proceso que durara 780 vueltas
Valor = ActiveCell.Value ' se extrae el valor de la primera celda
Select Case Valor ' se inicia una inspección case
Case "0000"   'si el caso es que la celda tiene valor "0000" que es el que queremos sustituir, se hace lo que sigue
    ActiveSheet.Paste 'se pega el valor acumulado primero que es "0001" porque nosotros pusimos ese valor
Case Else
End Select
    ActiveCell.Offset(1, 0).Range("A1").Select  'baja uno
Next 'repite
End Sub 'yastubo

-----------------------------------------------------

El resultado es este:

Que es lo que queríamos. Bueno ya saben amiguitos, cuando el Excel ande con eso del cero a la izquierda, a hacer macros.

Sustitución en Excel de claves extrañas por su concepto original

Problema.
Es que deveras. La gente piensa que las máquinas eran como antes. Antes el asunto era la memoria, entonces las máquinas tenían muy poco que dar. Hoy las máquinas tienen tanta memoria que es absurdo estar ahorrando espacio. Por ejemplo, antes era necesario algo asi:

Nombre Grado  Sueldo
Juan        L         1

QUe significaba, Juan de grado licenciado gana mil.

Ahora las máquinas aguantan hasta abusos, por ejemplo:

NombreDelEmpleado                               UltimoNivelDeEstudios                                    SueldoDevengado
Juan de Rivera de los Santos y Maza       Licenciatura inconclusa con ganas de superarse  1,234.83

Pues regresando al asunto del último post, la misma base tiene un ahorro de memoria que no nos favorece:

En la columna B vemos el título Pob y luego clasificaciones como L, C, ZM, todas estas significan lo siguiente:

ZM Zona metropolitana

M    MUnicipal

L      Localidad

C      Conurbación

CI     Conurbación interestatal

No tiene sentido estar ahorrando espacio, sobre todo si lo que ganamos en espacio lo perdemos en estructura, porque la gente piensa que una hoja de cálculo no incluye una base de datos, entonces abajo le pone comentarios y toda la cosa, por ejemplo en este caso ,desafortunadamente real:

Las anotaciones del renglón 781 para abajo rompen la estructura de base, ¿no sería mejor que tuvieramos el dato completo?

Solución:

La solución es simple, como siempre que usamos macros, sólo que tardada si se trata de mucha información. 

Queremos que la lista del tipo:

1 Z

1 M

1 M

2 L

3L

Etc. Aparezca como:

1 Zona Metropolitana

1 MUnicipal

1 Municipal

2 Localidad

3 Localidad

Etcétera. 

Bien, lo que hay que hacer es repetir el proceso del post anterior en cuanto a la grabación de macros y bajar una casilla para posterirmente deterner la grabación. Luego meteremos el código y quedará así (recuerda que comentaremos adelante de una comilla, y la comilla significa que se ignora el comentario, por lo que puedes meter el código también con comentarios:

------------------------------------

Sub Macro2()  ' aquí no debes confundir la sub macro2() con el subcomandante marcos :)

'

' Macro2 Macro

'

For N = 1 To 780                  'Como hay 780 renglones a corregir, decimos que haremos un ciclo de 1 a 780, todo lo que esté entre For y next, será repetido

Valor = ActiveCell.Value   ' Nuevamente, la celda donde se comienza mete su valor a la variable "Valor"

'

Select Case Valor    'La instrucción case, nos dice que la variable podrá tener muchos valores que se detallan a continuación del comando

Case "ZM"                    'hay un primer caso en que ZM debe ser convertido a "Zona Metropolitana"

NuevoValor = "Zona Metropolitana"   'Se asigna el valor

Case "M"                     'Y así con todas

NuevoValor = "Municipal"

Case "L"

NuevoValor = "Localidad"

Case "C"

NuevoValor = "Conurbada"

Case "CI"

NuevoValor = "Conurbada Interestatal"

End Select

ActiveCell.Value = NuevoValor   ' ahora, el nuevo valor que fue transformado se asigna a una nueva variable

    ActiveCell.Offset(1, 0).Range("A1").Select   'se baja un renglon

Next   'y asi más de 700 veces

End Sub    ' se acabó

--------------------------------------

El resultado queda así:

Podrá decir alguien: "ay eso yo lo hago con un if". Y sí, tiene razón. si en una columna se coloca la instrucción "=SI(B2="ZM","Zona Metropolitana", si(B2="M","Municipal", si(B2="C","Conurbado",si(B2="Conurbación interesatatal", si(B2="L",Localidad",""))))

 Luego se copiaría según valor y ya. Sin embargo, la macro es mucho más versatil. Cuando haya no 5 valores sino por ejemplo, 200, un if no sería posible en algunas versiones restringidas a 250 caracteres. Además cualquier error, ocasionaría serios problemas. 

Otra ventaja de resolver este asunto con macros es que puedo usar las macros para otras tareas relacionadas, por ejemplo, si quiero cambiar ahora la tercer columna que tiene "pes" y "ces" por "Principal" y "Complementaria", simplemente lo sustituyo de tal manera y queda el programa así:


Sub Macro2()
' Macro2 Macro
For N = 1 To 780
Valor = ActiveCell.Value
Select Case Valor
Case "P"
NuevoValor = "Principal"
Case "C"
NuevoValor = "Complementario"
End Select
ActiveCell.Value = NuevoValor
    ActiveCell.Offset(1, 0).Range("A1").Select
Next
End Sub


Y así sin esfuerzo, una vez hecha la macro puedo ir arreglando toto más fácil dejando el asunto así:

Una ventaja adicional es que se siente muy bonito cuando la máquina está trabajando y nosotros aprovechamos para ir a tomarnos un café. O un té, pues.

Otra opción que alguien más elemental dirá, será la de sustituir con el comando replace. Cierto que es más fácil el if que aprender macros, pero es todavía más fácil capturarlo y más aún no hacerle nada. Lo tardado es otro asunto.