Hola colegas buenos días, espero se encuentren con excelente salud ustedes y sus familias.
Hoy me traigo un tema al que le he dado muchas vueltas y llevo varios meses tratando de resolverlo, usando disimiles variantes, pero debo confesar que no he podido, aunque he estado cerca de hacerlo, pero sigue faltando detalles que no consigo completar.
He tratado de hacerlo con VBA y mediante fórmulas o funciones, incluso estuve estudiando un poco más profundo el tema de las fórmulas matriciales del Canal Excel Hecho Fácil de Andrés Rojas Moncada porque tuve la opinión que la respuesta estaba dado en el trabajo con matrices.
Voy al tema: Mi Dirección Nacional me pide una información de manera Mensual que consiste en el pago de los Portadores Energéticos (Agua, Combustible, Gas, Electricidad) pero se deben tener en cuanta varios factores a la hora de completar la información.
El Proyecto es el de Conciliación y Disponibilidad Bancaria en el que alguno de ustedes ya me ha prestado alguna ayuda para elaborarlo y el cual me funciona de manera perfecta para lo que lo diseñé.
En la Hoja18 (Portadores) se encuentra el Modelo Oficial en el cual debo plasmar la información.
ØLos valores monetarios cuando se hace más de un pago se suman.
ØSe deben poner cada una de las fechas en la que se realizan los pagos, pero poniendo la primera o una de ellas es perfectamente adecuado.
ØSe deben poner los distintos Estados de Cuentas (EC) en que el banco me notifica el crédito, pero al igual que las fechas con uno es suficiente.
ØLas Referencias Bancarias al igual que las fechas y los EC con una que plasme es suficiente.
(Los pagos de Energía Eléctrica, Gas y Agua se hacen una sola vez al mes, donde varia es en el Combustible (Fincimex) que pueden ser dos, tres o cuatro veces por mes. En el Modelo existen cinco (5) Filas habilitadas para Fincimex, pero lo que tenemos orientado es que en una sola se sumen los diferentes pagos y se ponga el resultado de esa suma en una sola fila, como si se hubiera realizado un solo pago)
En la Hoja23 (Consolidado) yo agrupo las transacciones de los dos Bancos con los que trabajo en la actualidad (BANDEC y BFI) y en esa misma Hoja23 a través de un filtro que está en el FrmConsolidado (Reporte de Portadores Energéticos) yo extraigo el reporte de todas las transacciones o movimientos bancarios de un rango de fechas y de un Banco determinado que por lo general va desde el día 1 al 30 o 31 del mes. El Banco no importa. (Imagen 1).
A partir de ahí es que se extraen los datos para la información. En el Modelo (Hoja18) se colocan los datos de la siguiente manera: En la Columna “C”se pone el Nº del EC en que Salió el pago (del Portador en cuestión), en la Columna “D” se coloca la Fecha correspondiente a ese Nº de EC, en la Columna “E” el importe del pago o la suma (en caso de Combustible) de ese Portador y en la Columna “F” el Nº de Referencia Bancaria correspondiente a ese pago y todos esos datos están en la Extracción de los Datos que se hace. (Imagen 2).
Mediante un Formato Condicional yo le di un COLOR diferente a la celda donde se encuentra la Descripción o Concepto de la transacción y como se pueden fijar se toman los valores correspondientes a los CRÉDITOS (Columna “R”).
Imagen 1.
ID
Banco
Fecha
Referencia
Concepto
EC
Debe
Haber
1
BANDEC
04/01/2023
C300443747539
Pago de Servicio de Banca Remota
1
0.00
100.00
2
BANDEC
05/01/2023
Q2001007539
Extracción del Salario Diciembre 2022
1
0.00
527,876.15
3
BANDEC
05/01/2023
Q2001003539
Pago a Almest CH-2001003-2
1
0.00
500.00
1
BFI
04/01/2023
FT300401331
Pago de Combustible Fincimex
511
0.00
27,980.00
2
BFI
04/01/2023
FT2300428926
Pago de Combustible Fincimex
511
0.00
23,783.00
3
BFI
04/01/2023
FT2300475333
Pago de Combustible Fincimex
511
0.00
10,422.00
4
BANDEC
06/01/2023
YY30000086539
Rescate de Maternidad 60%
2
4,921.05
0.00
4
BANDEC
06/01/2023
YY30000086539
Rescate de Maternidad 60%
2
4,921.05
0.00
5
BANDEC
10/01/2023
98023A0031026
Pago de Retenciones Creditos Bancarios
2
0.00
947.00
6
BANDEC
10/01/2023
98023A0031052
Pago de Retenciones Formación de Fondos
2
0.00
2,200.00
5
BFI
11/01/2023
FT2301208393
Pago de Combustible Fincimex
517
0.00
20,985.00
6
BFI
12/01/2023
7.55501E+13
Cobro a Pilar II
517
782,239.47
0.00
7
BFI
12/01/2023
6.65201E+13
Cobro a Flamenco I
517
1,285,965.34
0.00
8
BFI
12/01/2023
6.65201E+13
Cobro a Memories Caribe
517
593,885.00
0.00
9
BFI
12/01/2023
FT2301287391
Traspaso de Fondos
517
0.00
2,564,929.81
7
BANDEC
13/01/2023
NT30000224538
Rescate de Maternidad 100%
3
7,174.08
0.00
8
BANDEC
13/01/2023
JD30000039539
Depósito de Efectivo
3
6,490.50
0.00
9
BANDEC
13/01/2023
JD3000040539
Reintegro de Salario
3
21,440.15
0.00
10
BFI
17/01/2023
6.65002E+13
Cobro a Hotel Playuela
520
970,315.60
0.00
11
BFI
17/01/2023
7.55502E+13
Cobro a Hotel Playuela
520
140,697.91
0.00
12
BFI
17/01/2023
FT2301748366
Traspaso de Fondos
520
0.00
1,111,013.51
13
BFI
19/01/2023
FT2301944543
Cobro de Motelera Cayo Cruz
522
4,779.23
0.00
14
BFI
19/01/2023
FT2301960249
Pago de Combustible Fincimex
522
0.00
20,985.00
15
BFI
20/01/2023
FT2302034032
Pago de Electricidad UNE
523
0.00
243,810.78
16
BFI
20/01/2023
6.65202E+13
Cobro a Hotel Meliá Jardines del Rey
523
860,432.64
0.00
17
BFI
20/01/2023
FT2302017030
Traspaso de Fondos
523
0.00
600,416.09
18
BFI
23/01/2023
7.55502E+13
Cobro de Hotel Muthu Guillermo
524
827,856.40
0.00
19
BFI
23/01/2023
FT2302340502
Traspaso de Fondos
524
0.00
827,856.40
10
BANDEC
23/01/2023
VB30000322539
Cobro a Hotel Playa Hermosa Paredón
7
162,688.00
0.00
20
BFI
24/01/2023
6.65002E+13
Cobro a Hotel Valentín
525
200,613.00
0.00
21
BFI
24/01/2023
6.65002E+13
Depósito de Cheque Hotel Valentín
525
110,987.00
0.00
22
BFI
24/01/2023
FT2302436359
Traspaso de Fondos
525
0.00
311,600.00
11
BANDEC
24/01/2023
JD30000061539
Depósito de Comedor
8
728.42
0.00
12
BANDEC
24/01/2023
Q2001008539
Reembolso
8
0.00
9,760.00
13
BANDEC
24/01/2023
VB30000376539
Cobro a UCM Cayo Coco
8
7,429.97
0.00
14
BANDEC
24/01/2023
VB30000381539
Pago a Bomberos Voluntarios
8
0.00
3,600.00
15
BANDEC
24/01/2023
VB30000382539
Pago a Meliá Jardines del Rey
8
0.00
13,920.00
16
BANDEC
24/01/2023
VB30000383539
Pago a GET
8
0.00
2,671.76
17
BANDEC
24/01/2023
VB30000384539
Pago a Sasa Ciego
8
0.00
3,234.67
18
BANDEC
24/01/2023
VB30000384539
Comisión Bancaria
8
0.00
10.00
19
BANDEC
24/01/2023
VB30000395539
Pago a Transgaviota
8
0.00
61,488.00
23
BFI
25/01/2023
7.88803E+13
Cobro a Hotel Muthu Imperial
526
190,363.00
0.00
24
BFI
25/01/2023
6.65003E+13
Depósito de Cheque 29366541 Villa Gaviota
526
0.00
12,335.00
25
BFI
25/01/2023
FT2302534844
Pago de Combustible Fincimex
526
0.00
11,192.00
26
BFI
25/01/2023
FT2302599528
Traspaso de Fondos
526
0.00
166,836.00
20
BANDEC
25/01/2023
VB30000481538
Cobro a Marina Gaviota CC
9
163.33
0.00
21
BANDEC
26/01/2023
VB3000454539
Pago a Cupet Combustible
10
0.00
183,648.72
22
BANDEC
26/01/2023
VB30000457539
Pago de Arrendamiento a Almest
10
0.00
370,195.73
23
BANDEC
26/01/2023
VB30000457539
Comisión Bancaria
10
0.00
100.00
27
BFI
26/01/2023
FT2302679446
Deposito de Cheque 29366542 Peaje
527
0.00
21,600.00
28
BFI
30/01/2023
7.55503E+13
Cobro a Hotel Pilar I
529
61,012.22
0.00
29
BFI
30/01/2023
FT2303028877
Traspaso de Fondos
529
0.00
39,412.22
24
BANDEC
30/01/2023
VB30000504539
Cobro a UCM Cayo Coco
11
236.26
0.00
25
BANDEC
31/01/2023
VB30000542539
Cobro a Villa Gaviota
12
15,004.56
0.00
30
BFI
31/01/2023
6.65203E+13
Cobro a Hotel Memories Caribe
530
567.84
0.00
31
BFI
31/01/2023
6.65203E+13
Cobro a Hotel Flamenco I
530
23,968.79
0.00
32
BFI
31/01/2023
6.6502E+13
Cobro a Hotel Playa Coco
530
220,334.00
0.00
33
BFI
31/01/2023
FT2303104790
Traspaso de Fondos
530
0.00
244,870.63
26
BANDEC
31/01/2023
JD300000091539
Depósito de Comedor
12
630.00
0.00
27
BANDEC
31/01/2023
CS30000002539
Comisión Bancaria (Talonario de Cheques)
12
0.00
200.00
28
BANDEC
31/01/2023
Q3000001539
Reembolso
12
0.00
2,100.00
29
BANDEC
31/01/2023
VB30000611539
Pago a EMER - Grupos Electrógenos
12
0.00
170.80
30
BANDEC
31/01/2023
VB30000618539
Pago a ETECSA - Servicios Telefónicos
12
0.00
3,374.43
Imagen 2. (De acuerdo a los Datos arriba filtrados se confecciona el Modelo)
PORTADORES ENERGÉTICOS
UEB AT Lavandería Cayo Coco
Enero
2023
PORTADOR
Docum
No.
FECHA
IMP. CUP
Numero Ref. Banco
PAGADO A:
Combustible
EC
511
04/01/2023
298,995.72
FT300401331
FINCIMEX
Combustible
EC
FINCIMEX
Combustible
EC
FINCIMEX
Combustible
EC
FINCIMEX
Combustible
EC
FINCIMEX
Electricidad
EC
523
20/01/2023
243,810.78
FT2302034032
UNE
Agua
EC
ABACSA
Gas
EC
CUPET
Subtotal
542,806.50
Este es un ejemplo de como debe quedar el resultado en el Modelo. Para este mes en específico (Enero del 2023) no hubo pago de Agua ni pago de Gas, por lo que no tiene que mostrarse.
En mis intentos por resolver este problema yo tomé como referencia la Columna “G” (Pagado A:) y tomaba como Valor a Buscar la palabra de esa Columna, o sea, FINCIMEX, UNE, ABACSA, y CUPET porque yo al momento de registrar la transacción de acuerdo al pago le coloco esa misma descripción en algún lugar. Intenté con un BuscarV con SI, con Indice, con K.Esimo.Menor, Fila, Columna, pero nada, estuve intentando con VBA, pero lógicamente me faltan conocimientos y tampoco pude lograrlo.
Quizás la solución sea más fácil que la explicación, de hecho, he tratado de explicar de la mejor manera posible de dónde sale cada número, cada dato, las operaciones que hay que hacer y dónde debe colocarse.
Les agradecería infinitamente si me pudieran ayudar.
Hola colegas buenos días, espero se encuentren con excelente salud ustedes y sus familias.
Hoy me traigo un tema al que le he dado muchas vueltas y llevo varios meses tratando de resolverlo, usando disimiles variantes, pero debo confesar que no he podido, aunque he estado cerca de hacerlo, pero sigue faltando detalles que no consigo completar.
He tratado de hacerlo con VBA y mediante fórmulas o funciones, incluso estuve estudiando un poco más profundo el tema de las fórmulas matriciales del Canal Excel Hecho Fácil de Andrés Rojas Moncada porque tuve la opinión que la respuesta estaba dado en el trabajo con matrices.
Voy al tema: Mi Dirección Nacional me pide una información de manera Mensual que consiste en el pago de los Portadores Energéticos (Agua, Combustible, Gas, Electricidad) pero se deben tener en cuanta varios factores a la hora de completar la información.
El Proyecto es el de Conciliación y Disponibilidad Bancaria en el que alguno de ustedes ya me ha prestado alguna ayuda para elaborarlo y el cual me funciona de manera perfecta para lo que lo diseñé.
En la Hoja18 (Portadores) se encuentra el Modelo Oficial en el cual debo plasmar la información.
Ø Los valores monetarios cuando se hace más de un pago se suman.
Ø Se deben poner cada una de las fechas en la que se realizan los pagos, pero poniendo la primera o una de ellas es perfectamente adecuado.
Ø Se deben poner los distintos Estados de Cuentas (EC) en que el banco me notifica el crédito, pero al igual que las fechas con uno es suficiente.
Ø Las Referencias Bancarias al igual que las fechas y los EC con una que plasme es suficiente.
(Los pagos de Energía Eléctrica, Gas y Agua se hacen una sola vez al mes, donde varia es en el Combustible (Fincimex) que pueden ser dos, tres o cuatro veces por mes. En el Modelo existen cinco (5) Filas habilitadas para Fincimex, pero lo que tenemos orientado es que en una sola se sumen los diferentes pagos y se ponga el resultado de esa suma en una sola fila, como si se hubiera realizado un solo pago)
En la Hoja23 (Consolidado) yo agrupo las transacciones de los dos Bancos con los que trabajo en la actualidad (BANDEC y BFI) y en esa misma Hoja23 a través de un filtro que está en el FrmConsolidado (Reporte de Portadores Energéticos) yo extraigo el reporte de todas las transacciones o movimientos bancarios de un rango de fechas y de un Banco determinado que por lo general va desde el día 1 al 30 o 31 del mes. El Banco no importa. (Imagen 1).
A partir de ahí es que se extraen los datos para la información. En el Modelo (Hoja18) se colocan los datos de la siguiente manera: En la Columna “C” se pone el Nº del EC en que Salió el pago (del Portador en cuestión), en la Columna “D” se coloca la Fecha correspondiente a ese Nº de EC, en la Columna “E” el importe del pago o la suma (en caso de Combustible) de ese Portador y en la Columna “F” el Nº de Referencia Bancaria correspondiente a ese pago y todos esos datos están en la Extracción de los Datos que se hace. (Imagen 2).
Mediante un Formato Condicional yo le di un COLOR diferente a la celda donde se encuentra la Descripción o Concepto de la transacción y como se pueden fijar se toman los valores correspondientes a los CRÉDITOS (Columna “R”).
ID
Banco
Fecha
Referencia
Concepto
EC
Debe
Haber
1
BANDEC
04/01/2023
C300443747539
Pago de Servicio de Banca Remota
1
0.00
100.00
2
BANDEC
05/01/2023
Q2001007539
Extracción del Salario Diciembre 2022
1
0.00
527,876.15
3
BANDEC
05/01/2023
Q2001003539
Pago a Almest CH-2001003-2
1
0.00
500.00
1
BFI
04/01/2023
FT300401331
Pago de Combustible Fincimex
511
0.00
27,980.00
2
BFI
04/01/2023
FT2300428926
Pago de Combustible Fincimex
511
0.00
23,783.00
3
BFI
04/01/2023
FT2300475333
Pago de Combustible Fincimex
511
0.00
10,422.00
4
BANDEC
06/01/2023
YY30000086539
Rescate de Maternidad 60%
2
4,921.05
0.00
4
BANDEC
06/01/2023
YY30000086539
Rescate de Maternidad 60%
2
4,921.05
0.00
5
BANDEC
10/01/2023
98023A0031026
Pago de Retenciones Creditos Bancarios
2
0.00
947.00
6
BANDEC
10/01/2023
98023A0031052
Pago de Retenciones Formación de Fondos
2
0.00
2,200.00
5
BFI
11/01/2023
FT2301208393
Pago de Combustible Fincimex
517
0.00
20,985.00
6
BFI
12/01/2023
7.55501E+13
Cobro a Pilar II
517
782,239.47
0.00
7
BFI
12/01/2023
6.65201E+13
Cobro a Flamenco I
517
1,285,965.34
0.00
8
BFI
12/01/2023
6.65201E+13
Cobro a Memories Caribe
517
593,885.00
0.00
9
BFI
12/01/2023
FT2301287391
Traspaso de Fondos
517
0.00
2,564,929.81
7
BANDEC
13/01/2023
NT30000224538
Rescate de Maternidad 100%
3
7,174.08
0.00
8
BANDEC
13/01/2023
JD30000039539
Depósito de Efectivo
3
6,490.50
0.00
9
BANDEC
13/01/2023
JD3000040539
Reintegro de Salario
3
21,440.15
0.00
10
BFI
17/01/2023
6.65002E+13
Cobro a Hotel Playuela
520
970,315.60
0.00
11
BFI
17/01/2023
7.55502E+13
Cobro a Hotel Playuela
520
140,697.91
0.00
12
BFI
17/01/2023
FT2301748366
Traspaso de Fondos
520
0.00
1,111,013.51
13
BFI
19/01/2023
FT2301944543
Cobro de Motelera Cayo Cruz
522
4,779.23
0.00
14
BFI
19/01/2023
FT2301960249
Pago de Combustible Fincimex
522
0.00
20,985.00
15
BFI
20/01/2023
FT2302034032
Pago de Electricidad UNE
523
0.00
243,810.78
16
BFI
20/01/2023
6.65202E+13
Cobro a Hotel Meliá Jardines del Rey
523
860,432.64
0.00
17
BFI
20/01/2023
FT2302017030
Traspaso de Fondos
523
0.00
600,416.09
18
BFI
23/01/2023
7.55502E+13
Cobro de Hotel Muthu Guillermo
524
827,856.40
0.00
19
BFI
23/01/2023
FT2302340502
Traspaso de Fondos
524
0.00
827,856.40
10
BANDEC
23/01/2023
VB30000322539
Cobro a Hotel Playa Hermosa Paredón
7
162,688.00
0.00
20
BFI
24/01/2023
6.65002E+13
Cobro a Hotel Valentín
525
200,613.00
0.00
21
BFI
24/01/2023
6.65002E+13
Depósito de Cheque Hotel Valentín
525
110,987.00
0.00
22
BFI
24/01/2023
FT2302436359
Traspaso de Fondos
525
0.00
311,600.00
11
BANDEC
24/01/2023
JD30000061539
Depósito de Comedor
8
728.42
0.00
12
BANDEC
24/01/2023
Q2001008539
Reembolso
8
0.00
9,760.00
13
BANDEC
24/01/2023
VB30000376539
Cobro a UCM Cayo Coco
8
7,429.97
0.00
14
BANDEC
24/01/2023
VB30000381539
Pago a Bomberos Voluntarios
8
0.00
3,600.00
15
BANDEC
24/01/2023
VB30000382539
Pago a Meliá Jardines del Rey
8
0.00
13,920.00
16
BANDEC
24/01/2023
VB30000383539
Pago a GET
8
0.00
2,671.76
17
BANDEC
24/01/2023
VB30000384539
Pago a Sasa Ciego
8
0.00
3,234.67
18
BANDEC
24/01/2023
VB30000384539
Comisión Bancaria
8
0.00
10.00
19
BANDEC
24/01/2023
VB30000395539
Pago a Transgaviota
8
0.00
61,488.00
23
BFI
25/01/2023
7.88803E+13
Cobro a Hotel Muthu Imperial
526
190,363.00
0.00
24
BFI
25/01/2023
6.65003E+13
Depósito de Cheque 29366541 Villa Gaviota
526
0.00
12,335.00
25
BFI
25/01/2023
FT2302534844
Pago de Combustible Fincimex
526
0.00
11,192.00
26
BFI
25/01/2023
FT2302599528
Traspaso de Fondos
526
0.00
166,836.00
20
BANDEC
25/01/2023
VB30000481538
Cobro a Marina Gaviota CC
9
163.33
0.00
21
BANDEC
26/01/2023
VB3000454539
Pago a Cupet Combustible
10
0.00
183,648.72
22
BANDEC
26/01/2023
VB30000457539
Pago de Arrendamiento a Almest
10
0.00
370,195.73
23
BANDEC
26/01/2023
VB30000457539
Comisión Bancaria
10
0.00
100.00
27
BFI
26/01/2023
FT2302679446
Deposito de Cheque 29366542 Peaje
527
0.00
21,600.00
28
BFI
30/01/2023
7.55503E+13
Cobro a Hotel Pilar I
529
61,012.22
0.00
29
BFI
30/01/2023
FT2303028877
Traspaso de Fondos
529
0.00
39,412.22
24
BANDEC
30/01/2023
VB30000504539
Cobro a UCM Cayo Coco
11
236.26
0.00
25
BANDEC
31/01/2023
VB30000542539
Cobro a Villa Gaviota
12
15,004.56
0.00
30
BFI
31/01/2023
6.65203E+13
Cobro a Hotel Memories Caribe
530
567.84
0.00
31
BFI
31/01/2023
6.65203E+13
Cobro a Hotel Flamenco I
530
23,968.79
0.00
32
BFI
31/01/2023
6.6502E+13
Cobro a Hotel Playa Coco
530
220,334.00
0.00
33
BFI
31/01/2023
FT2303104790
Traspaso de Fondos
530
0.00
244,870.63
26
BANDEC
31/01/2023
JD300000091539
Depósito de Comedor
12
630.00
0.00
27
BANDEC
31/01/2023
CS30000002539
Comisión Bancaria (Talonario de Cheques)
12
0.00
200.00
28
BANDEC
31/01/2023
Q3000001539
Reembolso
12
0.00
2,100.00
29
BANDEC
31/01/2023
VB30000611539
Pago a EMER - Grupos Electrógenos
12
0.00
170.80
30
BANDEC
31/01/2023
VB30000618539
Pago a ETECSA - Servicios Telefónicos
12
0.00
3,374.43
PORTADORES ENERGÉTICOS
UEB AT Lavandería Cayo Coco
Enero
2023
PORTADOR
Docum
No.
FECHA
IMP. CUP
Numero Ref. Banco
PAGADO A:
Combustible
EC
511
04/01/2023
298,995.72
FT300401331
FINCIMEX
Combustible
EC
FINCIMEX
Combustible
EC
FINCIMEX
Combustible
EC
FINCIMEX
Combustible
EC
FINCIMEX
Electricidad
EC
523
20/01/2023
243,810.78
FT2302034032
UNE
Agua
EC
ABACSA
Gas
EC
CUPET
Subtotal
542,806.50
Este es un ejemplo de como debe quedar el resultado en el Modelo. Para este mes en específico (Enero del 2023) no hubo pago de Agua ni pago de Gas, por lo que no tiene que mostrarse.
En mis intentos por resolver este problema yo tomé como referencia la Columna “G” (Pagado A:) y tomaba como Valor a Buscar la palabra de esa Columna, o sea, FINCIMEX, UNE, ABACSA, y CUPET porque yo al momento de registrar la transacción de acuerdo al pago le coloco esa misma descripción en algún lugar. Intenté con un BuscarV con SI, con Indice, con K.Esimo.Menor, Fila, Columna, pero nada, estuve intentando con VBA, pero lógicamente me faltan conocimientos y tampoco pude lograrlo.
Quizás la solución sea más fácil que la explicación, de hecho, he tratado de explicar de la mejor manera posible de dónde sale cada número, cada dato, las operaciones que hay que hacer y dónde debe colocarse.
Les agradecería infinitamente si me pudieran ayudar.
Mis agradecimientos de antemano.
Saludos cordiales
Pino
Disponibilidad v9.1.2 Foro .rar