Como comparar rango en una matriz
Hola a todos, es la primera vez que hago una pregunta en esta página, aunque en ocasiones me han sido muy útiles las respuestas dadas a otros usuarios.
Pues bien, mi duda es la siguiente dispongo de dos hojas de excel con los siguientes datos:
hoja1 hoja2
A B C A B C
1 1 3 5 1 5 1 0
2 5 0 2 2 1 3 5
3 3 2 1 3 2 4 1
Etc...
Lo que quiero hacer es contar cuantas veces se repite el rango A1:C1 de la hoja 1 en la matriz A1:C2000 de la hoja 2, es decir cuantas veces se encuentra "1 3 5" en la hoja 2. Esta comparación la quiero hacer con toda la hoja 1, pero supongo que sabiendo hacer la primera fórmula ya no habrá mayor problema.
Os agradezco de antemano las repuestas.
Pues bien, mi duda es la siguiente dispongo de dos hojas de excel con los siguientes datos:
hoja1 hoja2
A B C A B C
1 1 3 5 1 5 1 0
2 5 0 2 2 1 3 5
3 3 2 1 3 2 4 1
Etc...
Lo que quiero hacer es contar cuantas veces se repite el rango A1:C1 de la hoja 1 en la matriz A1:C2000 de la hoja 2, es decir cuantas veces se encuentra "1 3 5" en la hoja 2. Esta comparación la quiero hacer con toda la hoja 1, pero supongo que sabiendo hacer la primera fórmula ya no habrá mayor problema.
Os agradezco de antemano las repuestas.
1 Respuesta
Respuesta de Cadipas spain
1
Yo te indico como hacerlo con 4 lineas de programación de visual basic, pero si no sabes nada, indícamelo y te voy dando los pasos para que copies en su sitio y te funcione.
El código en cuestión es: ( y lo tienes que poner en un módulo)
Sub BuscaRangosRepetidos()
Set h1 = Sheets("datos1")
Set h2 = Sheets("datos2")
h1.Range("d:d") = ""
r = 1
Do While h1.Cells(r, 1) <> ""
With h2.Range("a1:a2000")
Set cc = .Find(h1.Cells(r, 1), LookIn:=xlValues)
If Not cc Is Nothing Then
firstAddress = cc.Address
Do
fila = cc.Row
If h2.Cells(fila, 2) = h1.Cells(r, 2) And h2.Cells(fila, 3) = h1.Cells(r, 3) Then
h1.Cells(r, 4) = h1.Cells(r, 4) + 1
End If
Set cc = .FindNext(cc)
Loop While Not cc Is Nothing And cc.Address <> firstAddress
End If
End With
r = r + 1
Loop
End Sub
' ------------------
Ahora en cada hoja (hoja1 y Hoja2 dentro del VBA) tienes que pegar este otro codigo:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then BuscaRangosRepetidos
End Sub
Si todo esto te suena a chino, no te preocupes es fácil... con una pequeñas indicaciones lo podemos solventar (pero no nos vamos a adelantar).
Otra posibilidad es que me pases el fichero (le quitas los datos que creas conveniente si tienes dudas de mi confidencialidad) y te lo monto.
Se me olvido decirte que la columna DE de la hoja1 anoto la cantidad de repeticiones.
El código en cuestión es: ( y lo tienes que poner en un módulo)
Sub BuscaRangosRepetidos()
Set h1 = Sheets("datos1")
Set h2 = Sheets("datos2")
h1.Range("d:d") = ""
r = 1
Do While h1.Cells(r, 1) <> ""
With h2.Range("a1:a2000")
Set cc = .Find(h1.Cells(r, 1), LookIn:=xlValues)
If Not cc Is Nothing Then
firstAddress = cc.Address
Do
fila = cc.Row
If h2.Cells(fila, 2) = h1.Cells(r, 2) And h2.Cells(fila, 3) = h1.Cells(r, 3) Then
h1.Cells(r, 4) = h1.Cells(r, 4) + 1
End If
Set cc = .FindNext(cc)
Loop While Not cc Is Nothing And cc.Address <> firstAddress
End If
End With
r = r + 1
Loop
End Sub
' ------------------
Ahora en cada hoja (hoja1 y Hoja2 dentro del VBA) tienes que pegar este otro codigo:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then BuscaRangosRepetidos
End Sub
Si todo esto te suena a chino, no te preocupes es fácil... con una pequeñas indicaciones lo podemos solventar (pero no nos vamos a adelantar).
Otra posibilidad es que me pases el fichero (le quitas los datos que creas conveniente si tienes dudas de mi confidencialidad) y te lo monto.
Se me olvido decirte que la columna DE de la hoja1 anoto la cantidad de repeticiones.
Primero quiero darte las gracias por tu rapidez de respuesta y tu ayuda desinteresada. Gracias.
Yo he programado hace mucho tiempo en el antigua gwbasic (no se si te suena porque te hablo de hace unos 20 años). No me resulta muy difícil entender el código, ya que también hice alguna cosilla en visual, lo que ocurre es que nunca he insertado código en excel, access o word. Lo máximo que he hecho es modificar algún código de macro hecha con la grabadora. Evidentemente desconozco las instrucciones propias que utiliza el excel.
Pienso que este podría ser un buen momento para ampliar mis conocimientos en este terreno, por eso me gustaría que me echases una mano, y te agradecería que me indicases como debo iniciar el editor de visual, y si debo situarme en alguna celda concreta para identificar esa celda con el código (perdona mi ignorancia).
Con tu ayuda seguro que lo consigo y si al final me rindo (cosa que por orgullo no me gustaría) te envío el fichero ya que no tiene datos de gran importancia.
Saludos,
Yo he programado hace mucho tiempo en el antigua gwbasic (no se si te suena porque te hablo de hace unos 20 años). No me resulta muy difícil entender el código, ya que también hice alguna cosilla en visual, lo que ocurre es que nunca he insertado código en excel, access o word. Lo máximo que he hecho es modificar algún código de macro hecha con la grabadora. Evidentemente desconozco las instrucciones propias que utiliza el excel.
Pienso que este podría ser un buen momento para ampliar mis conocimientos en este terreno, por eso me gustaría que me echases una mano, y te agradecería que me indicases como debo iniciar el editor de visual, y si debo situarme en alguna celda concreta para identificar esa celda con el código (perdona mi ignorancia).
Con tu ayuda seguro que lo consigo y si al final me rindo (cosa que por orgullo no me gustaría) te envío el fichero ya que no tiene datos de gran importancia.
Saludos,
El lunes te doy los pasos a seguir, ya que el fin de semana descanso. He entrado un momento al correo y he visto tu mensaje. Es muy fácil y creo que lo conseguirás... nos vemos el lunes.
Para entrar en editor, con el fichero excel abierto, teclea ALT+F11 o desde el menu de excel
HERRAMIENTAS - MACRO - EDITOR DE VIAUL BASIC.
Ahora ya estamos en el editor, normalmente esta dividida en tres partes, a la izquierda hay dos, una es el directorio del proyecto y debajo las propiedades. A la derecha esta la ventana donde va el código.
Pinchamos en el directorio del proyecto y seleccionamos la hoja en cuestión, es diferente pinchar en la hoja o en ThisWorkbook, ya lo iras entendiendo según vayas avanzando. El código que escribas en ThisWorkbook es para todo y lo de la hoja es para esa hoja, aunque se le puede llamar desde otro lado. Vamos a lo que vamos, tu caso concreto:
Otra forma de entrar al editor es de la pestaña de la hoja de excel pinchando en ratón derecho y VER CÓDIGO, y de esta forma ya estamos en la pantalla del código de esa hoja.
Yo a las hojas les he llamado Datos1 y Datos2, entonces pincho con ratón derecho en la pestaña de la hoja Datos1, y pego este código:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then BuscaRangosRepetidos
End Sub
Sub BuscaRangosRepetidos()
Set h1 = Sheets("datos1")
Set h2 = Sheets("datos2")
h1.Range("d:d") = ""
r = 1
Do While h1.Cells(r, 1) <> ""
With h2.Range("a1:a2000")
Set cc = .Find(h1.Cells(r, 1), LookIn:=xlValues)
If Not cc Is Nothing Then
firstAddress = cc.Address
Do
fila = cc.Row
If h2.Cells(fila, 2) = h1.Cells(r, 2) And h2.Cells(fila, 3) = h1.Cells(r, 3) Then
h1.Cells(r, 4) = h1.Cells(r, 4) + 1
End If
Set cc = .FindNext(cc)
Loop While Not cc Is Nothing And cc.Address <> firstAddress
End If
End With
r = r + 1
Loop
End Sub
' ------------------------------
Ahora me cambio a la ventana del código de la hoja Datos2, ( ya sabes, con el ratón derecho sobre la pestaña de la hoja en excel o desde el editor pinchando en el nombre de la hoja en la parte izquierda), y pego este otro código:
If Target.Column < 4 Then BuscaRangosRepetidos
End Sub
' ----------------------------------
Pequeña explicación: cada vez que cambies los datos en las tres primeras columnas de cada hoja Datos1 o Datos2, se ejecuta este código y hace el recuento, de tal forma que si cambias datos, vuelve a contar.
Si necesitas más aclaraciones, ya sabes, pregunta. Se que es un rollo pero, a mi no se me da muy bien las explicaciones y a veces puedo olvidar algo que pienso que esta claro y no lo esta.
HERRAMIENTAS - MACRO - EDITOR DE VIAUL BASIC.
Ahora ya estamos en el editor, normalmente esta dividida en tres partes, a la izquierda hay dos, una es el directorio del proyecto y debajo las propiedades. A la derecha esta la ventana donde va el código.
Pinchamos en el directorio del proyecto y seleccionamos la hoja en cuestión, es diferente pinchar en la hoja o en ThisWorkbook, ya lo iras entendiendo según vayas avanzando. El código que escribas en ThisWorkbook es para todo y lo de la hoja es para esa hoja, aunque se le puede llamar desde otro lado. Vamos a lo que vamos, tu caso concreto:
Otra forma de entrar al editor es de la pestaña de la hoja de excel pinchando en ratón derecho y VER CÓDIGO, y de esta forma ya estamos en la pantalla del código de esa hoja.
Yo a las hojas les he llamado Datos1 y Datos2, entonces pincho con ratón derecho en la pestaña de la hoja Datos1, y pego este código:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 4 Then BuscaRangosRepetidos
End Sub
Sub BuscaRangosRepetidos()
Set h1 = Sheets("datos1")
Set h2 = Sheets("datos2")
h1.Range("d:d") = ""
r = 1
Do While h1.Cells(r, 1) <> ""
With h2.Range("a1:a2000")
Set cc = .Find(h1.Cells(r, 1), LookIn:=xlValues)
If Not cc Is Nothing Then
firstAddress = cc.Address
Do
fila = cc.Row
If h2.Cells(fila, 2) = h1.Cells(r, 2) And h2.Cells(fila, 3) = h1.Cells(r, 3) Then
h1.Cells(r, 4) = h1.Cells(r, 4) + 1
End If
Set cc = .FindNext(cc)
Loop While Not cc Is Nothing And cc.Address <> firstAddress
End If
End With
r = r + 1
Loop
End Sub
' ------------------------------
Ahora me cambio a la ventana del código de la hoja Datos2, ( ya sabes, con el ratón derecho sobre la pestaña de la hoja en excel o desde el editor pinchando en el nombre de la hoja en la parte izquierda), y pego este otro código:
If Target.Column < 4 Then BuscaRangosRepetidos
End Sub
' ----------------------------------
Pequeña explicación: cada vez que cambies los datos en las tres primeras columnas de cada hoja Datos1 o Datos2, se ejecuta este código y hace el recuento, de tal forma que si cambias datos, vuelve a contar.
Si necesitas más aclaraciones, ya sabes, pregunta. Se que es un rollo pero, a mi no se me da muy bien las explicaciones y a veces puedo olvidar algo que pienso que esta claro y no lo esta.
