Buscar datos asociados y mostrar encadenados en una celda

Tengo una hoja con un listado por: Columna A: número de recibo de cobro, Columna B: monto cobrado, Columna C: número de depósito.


El número del depósito puede repetirse para varios recibos de cobro ya que a veces se hacen depósitos masivos.


En otra hoja se coloca el estado de cuenta del banco para hacer una conciliación y por conteo se encuentra que un número de depósito corresponde por ejemplo a 10 recibos, pudiendo ser menos o más.


En una celda al lado de la del conteo, quisiera mostrar concatenados y separados con comas todos los números de recibos que corresponden a un número de depósito en particular.


Para simplificar la idea, en el siguiente ejemplo asumo que los datos están en la misma hoja, en donde el la columna D esta el conteo de las veces que se repite el número del depósito usando la función Contar.si y el la columna E es donde deben mostrarse todos los números de recibos asociados a cada depósito:


       A                B             C                   D              E


 Recibo       Monto    Depósito     Dp. Rep.   Recibos Asociados


   1201         500,00       12345            5           1201, 1204, 1205, 1206, 1209


   1202         600,00       56792            2           1202, 1210


   1203         505,00       45628            3           1203, 1207, 1208


   1204         125,00       12345


   1205         280,00       12345


   1206        325,00        12345


   1207        432,00        45628


   1208        425,00        45628


   1209        832,00        12345


  1210      1.247,00        56792


  1211         634,00        34567            1          1211

En este caso, tanto la cuenta de los números de depósitos como de los números de recibos asociados, se indicaron solo en la primera ocasión que aparecen en la lista.

Agradezco la ayuda

1 respuesta

Respuesta
1

No entendí muy bien cómo están los datos y el resultado que esperas.

En la hoja 1 tienes tres columnas, ¿correcto?

¿En la hoja 2 quieres el resultado?

Es decir, en la hoja 2 tienes este número de depósito

          A

1    12345

Y quieres el resultado así

          A                   B                            C

1    12345               5            1201, 1204, 1205, 1206, 1209

¿O quieres en la hoja 1 un resumen de todos los números de la columna C con su conteo y sus números de recibo?

Hola Dante,

justamente lo que busco es lo primero que indicas que quiero como resultado el la columna C de la segunda hoja.

Te cuento que este es un trabajo de conciliación entre lo que reporta el departamento de cobranzas y el estado de cuenta del banco. En otras palabras es para corroborar que el dinero cobrado fue depositado y acreditado en la cuenta.

Hablando siempre de la segunda hoja,

1. el valor de la columna A lo pego a partir del estado de cuenta que me da el banco,

2. para el valor de la columna B uso la función CONTAR.SI(Hoja1!$C$2:$C$50,A2). El conteo en cada linea de la columna B se hace sobre la primera hoja ya que es donde el departamento de cobranzas reporta la información de su actividad. 

4. en la columna C, queremos que mostrar todos los números de recibos cuyo cobro corresponde a ese número del depósito, solo si se repite más de una vez.

Con la función BUSCARV encuentro el número del primer recibo. El problema es cuando son más de 1. En este momento los estoy buscando uno por uno por lo que el trabajo se hace tedioso.

Saludos y gracias por tu interés.

Ejecuta la siguiente macro. Cambia en la macro Hoja1 y Hoja2 por el nombre de tus hojas.

Sub concatenar()
'Por.Dante Amor
    Set h1 = Sheets("Hoja1")
    Set h2 = Sheets("Hoja2")
    h2.Range("B:C").ClearContents
    For i = 2 To h2.Range("A" & Rows.Count).End(xlUp).Row
        Set r = h1.Columns("C")
        Set b = r.Find(h2.Cells(i, "A"), lookat:=xlWhole)
        If Not b Is Nothing Then
            ncell = b.Address
            Do
                h2.Cells(i, "B") = h2.Cells(i, "B") + 1
                If h2.Cells(i, "C") = "" Then
                    h2.Cells(i, "C") = h1.Cells(b.Row, "A")
                Else
                    h2.Cells(i, "C") = h2.Cells(i, "C") & ", " & h1.Cells(b.Row, "A")
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> ncell
        End If
    Next
End Sub

Sigue las Instrucciones para un botón y ejecutar la macro

1. Abre tu libro de Excel

2. Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11

3. En el menú elige Insertar / Módulo

4. En el panel del lado derecho copia la macro

5. Ahora para crear un botón, puedes hacer lo siguiente:

6. Inserta una imagen en tu libro, elige del menú Insertar / Imagen / Autoformas

7. Elige una imagen y con el Mouse, dentro de tu hoja, presiona click y arrastra el Mouse para hacer grande la imagen.

8. 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”

9. Vuelve a presionar click derecho dentro de la imagen y ahora selecciona: Asignar macro. Selecciona: pinta

10. Aceptar.

11. Para ejecutarla dale click a la imagen.

Saludos. Dante Amor

Recuerda valorar la respuesta.

¡Gracias! Dante,

la pondré en práctica y te aviso, igual si tengo alguna dificultad de nuevo te pido ayuda.

Podrías valorar la respuesta, si tienes dudas igual me puedes contactar.

¡Gracias!

Gracias Dante, aplique tu rutina tal como la enviaste y funciona muy bien, solo falta ahora aplicarla a mi archivo que es mucho más complejo de como lo expuse ya que en la hoja principal (Hoja1) tengo un montón más de columnas y lo que es la Hoja2 es una para cada estado de cuenta bancaria y son 5 bancos.

Conozco algo de VisualFox pero nada de VB ni de Macros, estoy tratando de aprender la lógica de los comandos.

De nuevo un montón de Gracias y te estaré contactactando si es necesario.

Claro, con gusto puedes contactarme para cualquier duda, sólo recuerda plantear tu pregunta con ejemplos lo más apegados a la realidad, de esta forma la solución estará completa.

Hola Dante,

Apliqué la macro al archivo real, en el que te comente además de la hoja principal (Hoja1) compuesta por un montón de columnas y filas en blanco tengo 5 hojas adicionales, una para cada banco (Hoja2).

En la hoja principal (Hoja1) la columna U corresponde a la A de tu ejemplo, y la columna Y es la que corresponde a la columna C de tu Ejemplo.

En una de las hojas de bancos (Hoja2), la columna B corredponde a la A de tu ejemplo, la columna H corresponde a la columna B de tu ejemplo y la columna I es donde queremos que aparezcan los datos corresponde a la columna C de tu ejemplo.

Como las columnas de los estados de cuenta de los bancos son variables, agregué una HOJA1 y copié los datos tal como el ejemplo, luego agregué una HOJA2 en la que puse los datos de los depósitos y coloqué en ésta el botón de la macro sin cambiar nada de lo que me enviaste e igualmente funciona bien salvo que:

1.- Borra las etiquetas que identifican las columnas B y C de la HOJA2, cosa que puedo copiar en otras columnas que no sean B ni C.

2.- También ejecuta el conteo y la búsqueda en las líneas cuyo valor es cero en la HOJA2, columna A

3.- No salta las líneas cuyo valor en la HOJA2, columna A, esté en blanco.

Te agradezco tu orientación y si puedes recomendarme algún tutorial de VBA para Excel.

Gracias

Podrías crear una nueva pregunta y me plantes todo lo que quieres que haga la macro.

Hola Dante, de nuevo gracias y te cuento que ya resolví todo gracias a tu macro y un poquito de investigación adicional y unas ideas adicionales que apliqué.

Mi archivo original tenía una hoja principal llamada RECIBOS que tiene muchas columnas para diferentes datos. Además tiene cinco hojas, cada una con el nombre de cada banco y con la estructura de cada Estado de Cuenta ya que no son iguales. Lo que hice:

1.- Agregué una HOJA1 en la que importé de la hoja de RECIBOS solamente:

 Columna    Columna     Columna

       A                B               C

# Recibo    Cobrado    # Deposito

2.- Agregué una Hoja para cada Banco a las cuales importé solo los números de Depósito de los correpondientes Estados de Cuenta, colocando un guión (-) en las celdas en blanco o con valor cero y las llame por un nombre corto como banco1, banco2,.. etc.

 Columna        Columna       Columna

        A                  B                    C

# Depósito       Conteo       Lista de Recibos de tu Macro

Conteo = Conteo con tu Macro del número de veces que se repite un # Deposito

3.- Creé una macro para cada Banco idéntica a la que me enviaste, con el nombre de cada uno de ellos y donde cambié solo el nombre de la segunda hoja:

Sub banco1()
'Por.Dante Amor
    Set h1 = Sheets("Hoja1")
    Set h2 = Sheets("banco1")
........

Sub banco2()
'Por.Dante Amor
    Set h1 = Sheets("Hoja1")
    Set h2 = Sheets("banco2")
..........

4.- Coloqué un botón las hojas en cada banco1, banco2,... y las asocié a la macro respectiva, banco1, banco2, .....
5.- Ejecuté las macros hoja por hoja y luego copié y pegué en cada hoja maestra de conciliación bancaria.
Quizás hay caminos más cortos pero esto fué lo que se me ocurrió y me sirve.

De nuevo gracias.

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas