Página 1 de 1
[RESOLVIDO] - Ajuda com Fórmulas - Macro
Enviado: 28 Abr 2022 às 16:31
por susev
Pessoal, boa tarde!
Tudo bem?
Preciso muito de ajuda!
O relatório anexo é extraído de um robô e mostra todos os pagamentos de um financiamento que foi realizado com o banco.
Existem pagamentos que são feitos mais de uma vez para uma única parcela. Exemplo: O valor da parcela é de 2 mil reais e o cliente pagou mil em 27/04 e mais mil em 29/04.
Preciso consolidar o valor pago, levando em consideração que tenham sido feitos no mesmo mês. Precisa levar em consideração que seja o mesmo produto "Coluna H", a mesma operação "Coluna N" e o mesmo mês "Coluna M".
Exemplo: O ESCOB número: 1119427 - OPERAÇÃO: 0386000007010860168 fez 2 pagamentos no valor de R$ 933,10, ambos no dia 12/04/2022. Preciso que mostre que ele pagou, na verdade, R$ 1.866,20.
Segundo problema:
ESCOB: 1119427 - OPERAÇÃO: 0941000001880300151
No dia 17/04/2020 mostra que o cliente fez um acordo com o banco. Abaixo disso, mostra um outro acordo, mas ele foi quebrado. Sempre que aparecer mais de uma vez a linha com o CODIGO_EVENTO acordo para um cliente (mesmo ESCOB e mesma OPERAÇÃO), é pq houve quebra, então só o mais recente está ativo. O que ele fez em 22/06/2016, por exemplo, foi quebrado com o banco.
Preciso criar uma "planilha adicional" informando as quebras que cada cliente possuí e quantas parcelas já pagou do acordo que está ativo.
Poderiam me dar uma força com isso, por favor?
Estou aprendendo Excel, mas não faço a menor ideia de como iniciar por aqui.
Agradeço demais!
Forte abraço
Re: Ajuda com Fórmulas - Macro
Enviado: 28 Abr 2022 às 16:34
por susev
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 00:05
por osvaldomp
Olá,
@susev .
#
susev escreveu: ↑28 Abr 2022 às 16:31
Preciso criar uma "planilha adicional" informando as quebras que cada cliente possuí e quantas parcelas já pagou do acordo que está ativo.
Ajudaria bem mais se você preenchesse manualmente a "planilha adicional" com 2 ou 3 exemplos de cada um dos casos (pagamentos duplicados e quebra de acordo), e informasse em cada exemplo de qual linha da planilha você pegou os dados. Isso possivelmente irá responder as dúvidas que coloquei mais abaixo.
Informe também como vamos calcular "quantas parcelas já pagou"
Exemplo: O ESCOB número: 1119427 - OPERAÇÃO: 0386000007010860168 fez 2 pagamentos no valor de R$ 933,10, ambos no dia 12/04/2022. Preciso que mostre que ele pagou, na verdade, R$ 1.866,20.
1. nesses casos de pagamentos duplicados quais colunas você quer que sejam replicadas na "planilha adicional" ?
2. nos casos de pagamentos duplicados em que os dados das colunas A e/ou B sejam diferentes, qual linha será replicada? No seu exemplo acima, em um dos registros B=250094 e no outro B=222018. Qual deles vai ser replicado?
Segundo problema:
ESCOB: 1119427 - OPERAÇÃO: 0941000001880300151
No dia 17/04/2020 mostra que o cliente fez um acordo com o banco. Abaixo disso, mostra um outro acordo, mas ele foi quebrado. Sempre que aparecer mais de uma vez a linha com o CODIGO_EVENTO acordo para um cliente (mesmo ESCOB e mesma OPERAÇÃO), é pq houve quebra, então só o mais recente está ativo. O que ele fez em 22/06/2016, por exemplo, foi quebrado com o banco.
Nestes casos o que você quer fazer ?
Que dados serão replicados na "planilha adicional" ?
Como vamos diferenciar na "planilha adicional" os casos replicados como pagamentos duplicados dos casos replicados como quebra de acordo ?
Ou você quer duas "planilhas adicionais", uma para pagamentos duplicados e outra para quebra de acordo ?
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 10:23
por susev
@osvaldomp, bom dia.
Agradeço muito pela sua ajuda.
Coloquei todas essas informações na planilha. Por favor, veja se ficou claro.
Muito obrigada!
Robô - Relatório - Como Deveria Ficar.xlsx
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 11:45
por osvaldomp
Olá, @susev .
Vi rapidamente o seu arquivo e me pareceu que será uma solução bem trabalhosa.
Esse trabalho você irá executar uma única vez ou é repetitivo ?
Depois vou analisar mais detalhadamente e se ainda restarem dúvidas volto a perguntar.
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 13:19
por susev
Olá, @osvaldomp,
O trabalho vai precisar ser executado diariamente, por isso meu desespero!
Levamos horas e horas para fazer essas conferências de forma manual. Está bem complicado.
Muito obrigada por tudo!
Abraços
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 19:33
por PHSabater
A coluna CPF/CNPJ você limpou ou não tem essa informação?
Para criar uma rotina em VBA para fazer os ajustes necessários seria o principal filtro, ou o ESCOB é único também para cada cliente?
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 19:44
por susev
Olá, @PHSabater
A coluna de CPF sempre virá sem a informação.
O ESCOB é um número único por cliente.
Muito obrigada!!!
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 20:10
por PHSabater
@susev, quando existir um status "Acordo" é a negociação que foi feita? Para fins do relatório você quer somente os pagamentos efetuados após o "Acordo"?
Re: Ajuda com Fórmulas - Macro
Enviado: 29 Abr 2022 às 20:21
por susev
@PHSabater
Isso mesmo. Única coisa que tem que levar em consideração é que pode existir mais de 1 acordo para o mesmo cliente, mas só o que estiver com data mais recente está ativo, os demais foram quebrados. Preciso apenas dos ativos.
Outra questão é que o ESCOB é o número único do cliente, mas ele pode ter operações diferentes, por exemplo, o cliente pode ter mais de 1 acordo ativo, um de financiamento de imóvel e outro de carro. Por isso o nome do produto e o número da operação também são importantes para o cruzamento.
Muito obrigada
Re: Ajuda com Fórmulas - Macro
Enviado: 02 Mai 2022 às 08:23
por susev
@PHSabater e @osvaldomp, bom dia.
Caso possam me ajudar e tenha algo que pedi que dificulte o trabalho e demande mais ainda do tempo de vocês, podem me ajudar só com o que for menos trabalhoso, por favor. O restante que não der para automatizar eu continuo fazendo de forma manual.
Muito obrigada pela ajuda e boa semana!
Re: Ajuda com Fórmulas - Macro
Enviado: 05 Mai 2022 às 07:24
por PHSabater
@susev, Bom dia.
É possível sim automatizar, só estou sem tempo por aqui de te ajudar. Hoje a noite vou tirar um tempo pra ver melhor a situação.
Re: Ajuda com Fórmulas - Macro
Enviado: 05 Mai 2022 às 08:06
por susev
@PHSabater , bom dia!
Muito obrigada!!!
Re: Ajuda com Fórmulas - Macro
Enviado: 09 Mai 2022 às 16:12
por osvaldomp
Código: Selecionar todosSub ConsolidaValores()
Dim ws As Worksheet, LR As Long, p As Range, d As Range, x As Long, S As Double, q As Long, b As Boolean
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Sheets("Relatório Robô Original")
If Not Evaluate("isref(Resultado!A1)") Then Sheets.Add(, Sheets(Sheets.Count)).Name = "Resultado"
With Sheets("Resultado")
.Cells.Clear
ws.[A1:Q1].Copy .[A1]
.[Q1] = "VALOR TOTAL PAGO": .[R1] = "QTDE DE PARCELAS PAGAS": .[S1] = "QTDE DE ACORDOS QUEBRADOS"
.[P:Q].NumberFormat = "#,##0.00"
.[P1].Copy
.[Q1:S1].PasteSpecial xlFormats
End With
With ws
On Error Resume Next
.ShowAllData
On Error GoTo 0
'.Columns(13).TextToColumns , FieldInfo:=Array(1, 4)
.[T:U].Clear: LR = .Cells(Rows.Count, 8).End(3).Row
.Range("H1:H" & LR).Copy .[T1]: .Range("N1:N" & LR).Copy .[U1]
.Range("T1:U" & LR).RemoveDuplicates Array(1, 2), xlYes
For Each p In .Range("T2:T" & .Cells(Rows.Count, 20).End(3).Row)
.[A1:Q1].AutoFilter 8, p.Value
.[A1:Q1].AutoFilter 14, p.Offset(, 1).Value
q = Evaluate("SUMPRODUCT(SUBTOTAL(103,OFFSET('Relatório Robô Original'!O2,ROW(O2:O" & LR & ")-ROW(O2),0)),--ISNUMBER(FIND(""Acordo"",'Relatório Robô Original'!O2:O" & LR & ")))")
For Each d In .Range("M2:M" & LR).SpecialCells(12)
If b = True Then b = False: GoTo próxd
If .Cells(d.Row, 1) = "" Then Exit For
x = x + 1
.Cells(d.Row, 1).Resize(, 15).Copy Sheets("Resultado").Cells(Rows.Count, 1).End(3)(2)
If .Cells(d.Row, 15) Like "*Acordo" Then
Sheets("Resultado").Cells(Rows.Count, 1).End(3)(1, 16) = .Cells(d.Row, 16) * 1
x = x - 1: Exit For
ElseIf .Cells(d.Row + 1, 15) Like "*Acordo" Then
Sheets("Resultado").Cells(Rows.Count, 1).End(3)(1, 16) = .Cells(d.Row, 16) * 1
S = S + .Cells(d.Row, 16) * 1
ElseIf Month(.Cells(d.Row, 13)) = Month(.Cells(d.Row + 1, 13)) Then
Sheets("Resultado").Cells(Rows.Count, 1).End(3)(1, 16) = .Cells(d.Row, 16) * 1 + .Cells(d.Row + 1, 16) * 1
S = S + .Cells(d.Row, 16) * 1 + .Cells(d.Row + 1, 16) * 1: b = True
Else: Sheets("Resultado").Cells(Rows.Count, 1).End(3)(1, 16) = .Cells(d.Row, 16) * 1
S = S + .Cells(d.Row, 16) * 1
End If
próxd:
Next d
Sheets("Resultado").Cells(Rows.Count, 1).End(3)(1, 17) = S
Sheets("Resultado").Cells(Rows.Count, 1).End(3)(1, 18) = x
Sheets("Resultado").Cells(Rows.Count, 1).End(3)(1, 19) = IIf(q < 2, 0, q - 1)
S = 0: x = 0: q = 0
Next p
.ShowAllData: .[T:U] = ""
End With
Sheets("Resultado").Columns(16).Font.Size = 8
Sheets("Resultado").Columns(16).NumberFormat = "$ #,##0.00"
Sheets("Resultado").Columns("P:Q").AutoFit
End Sub
Re: [RESOLVIDO] - Ajuda com Fórmulas - Macro
Enviado: 09 Mai 2022 às 16:16
por susev
@osvaldomp, ficou perfeito e era exatamente o que eu precisava. Agradeço muito por tudo!!! Abraços