Generar informe de Portadores Energéticos

[pinoji]

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.

Mis agradecimientos de antemano.

Saludos cordiales

Pino

 

 

 

Disponibilidad v9.1.2 Foro .rar

[JSDJSD]

Buena creo que algo como esto es lo que buscas

[pinoji]

Buenas noches colegas.

Buenas noches profesor JSD, estoy desde mi móvil y no tengo toda la capacidad de analizar su propuesta, pero atendiendo a eventos anteriores usted siempre da en el blanco. Si fueras tan amable de subir el archivo con la modificación lo reviso y te comento, pero a priori creo que es lo que estoy necesitando. 

Un abrazo cordial a usted y su familia.

Saludos a los demás miembros de este Foro 

Mis respetos.

Pino

[JSDJSD]

Aquí te lo dejo

10042023.xlsm

[pinoji]

Buenos días colegas. Mis deseos que se encuentren bien de salud.

Buenos días profesor JSD.  Todo correcto,  es la solución perfecta.

He estado analizando el código y nunca hubiera llegado a eso, ahora intentaré adaptarlo al Proyecto.

Gracias mil.

Mis respetos

Un abrazo

Pino