Este fórum está sendo desativado

[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,2‬0.

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

[susev]

Robô - Relatório.xlsx

[osvaldomp]

Olá, @susev .
#

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,2‬0.
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 ?

[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

[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.

[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

[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?

[susev]

Olá, @PHSabater

A coluna de CPF sempre virá sem a informação.
O ESCOB é um número único por cliente.

Muito obrigada!!!

[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"?

[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

[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!

[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.

[susev]

@PHSabater , bom dia!
Muito obrigada!!!

[osvaldomp]

Código: Selecionar todos

Sub 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

[susev]

@osvaldomp, ficou perfeito e era exatamente o que eu precisava. Agradeço muito por tudo!!! Abraços