Subtraindo as vendas da ordem no excel

Question

Eu passei as últimas horas tentando encontrar algo semelhante ao problema que estou tendo e atualmente tenho fumaça saindo dos meus ouvidos!

Eu tenho duas folhas, uma com ordens a segunda com vendas .

Preciso de ajuda automaticamente subtraindo de pedidos com base em vendas .

Estou tentando desenvolver um script que seja executado em todas as vendas e, se local e item forem exatamente iguais, subtraia a quantidade vendida da quantidade pedida para dar uma "novo devido" quantidade.

Haverá vendas sem pedido, o que não é um problema, mas seria bom se esses resultados pudessem ir para uma guia diferente. Abaixo está um resumo do que é necessário em termos n00b.

Sheet1 é vendido , Sheet2 é pedido .

Se Sheet1 C2 & F2 são iguais a qualquer linha em Sheet2 ( A2 , D2 estão correlacionando campos) subtrair Sheet1 G2 , de Sheet2 F2 .

Eu adicionei um exemplo que você pode ver aqui Subtrair se várias condições forem satisfeitas link

Na guia de resultados, tudo o que é destacado em amarelo é o que teria mudado. Itens não destacados em amarelo não houve venda. A formatação verde e vermelha foi para referência e não é necessária se for difícil. (seria ideal) coluna H, está ilustrando, se isso é possível, uma vez que a quantidade do pedido chega a 0, para o script manter a planilha de busca para o próximo pedido com os mesmos critérios e subtrair quantidade restante. Qualquer coisa vai ajudar, eu estou fazendo isso manualmente no fim de semana e geralmente mais de 150 + vendas por semana e é muito demorado.

Em termos simples, estou tentando fazer com que um script passe por cada linha de venda e subtraia a quantidade da folha de pedido se o centro e o número da peça forem os mesmos. Eu classificarei a folha de pedidos com base em quando eles forem removidos da ordem correta.

Obrigado por qualquer ajuda!

    

Answer 1

Eu não acho que você precise de VBA.

Olhe para SUMIFS ()

Não posso ver suas capturas de tela porque o proxy reverso da minha organização não permite acesso ao app.box, mas estou assumindo:

• A coluna "C" da Folha1 contém localizações.
• A coluna "F" da Folha1 contém itens.
• A coluna "G" da Folha1 contém a quantidade vendida.
• A coluna "A" da Folha2 contém localizações.
• A coluna "D" da Folha2 contém itens.
• A coluna "G" da Folha2 contém quantidade solicitada.
• A coluna "H" da Folha2 contém quantidade devida

Enquanto na planilha2, a quantidade total vendida para o item e o local listados na linha 2 é:

=IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

Portanto, a fórmula usada para Sheet2! H2 seria simplesmente subtrair isso da quantidade solicitada:

=G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))

Exceto se o pedido for preenchido e um pedido subseqüente para o mesmo local e item tiver sido pelo menos parcialmente preenchido, o total vendido poderá ser maior que o pedido na linha 2 e Encomendado - Vendido será menor que zero! Mas isso é uma coisa fácil de se proteger ...

=MAX(0,G2-IF(OR(A2="",D2=""),0,SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

Mas isso não leva em conta nenhum pedido acima da linha atual! Temos MIN (0, ThisOrderQuantity - LocationItemQtySold ) quando deveríamos ter MIN (0, ThisOrderQuantity + PreviouslyOrderedQuantities - LocationItemQtySold ) O total de todos os pedidos acima da linha atual na Planilha2 são

=OFFSET(G2,0,0,ROW()-2, 1)

... bem ... que vai quebrar na linha 2. Queremos algo que funcione em todas as linhas, incluindo a primeira. Então, vamos nos proteger na linha 2.

=IF(ROW()<3,0,OFFSET(G2,0,0,ROW()-2, 1))

mas são todos os pedidos anteriores quando queremos apenas pedidos anteriores para o mesmo local e item. Precisamos de outro SUMIFS (). Para a Folha 2, linha 10, isso seria

=IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A10,OFFSET(D:D,0,0,ROW()-2,1),D10))

Ok, agora podemos combiná-los. Para H2, arrastável até a coluna, a fórmula seria:

=MAX(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2)))

Só que se você tiver três pedidos de localização / item e apenas o primeiro deles for preenchido, a quantidade "devida" desse terceiro será aquela ordem mais a quantidade do pedido do segundo! Precisamos ter certeza de que, se todos os pedidos anteriores ainda não estiverem totalmente preenchidos, isso não estraga nossa quantidade "vencida". Portanto, devemos adicionar uma função MAX para que o que é devido nunca seja maior do que o que foi pedido.

=MIN(G2,MIN(0,G2+IF(OR(A2="",D2=""),0,IF(ROW()<3,0,SUMIFS(OFFSET(G2,0,0,ROW()-2, 1),OFFSET(A:A,0,0,ROW()-2,1),A2,OFFSET(D:D,0,0,ROW()-2,1),D2))-SUMIFS(Sheet1!G:G,Sheet1!C:C,A2,Sheet1:F:F,D2))))

Cole isso no H2, arraste essa fórmula pelo resto de H, depure o que eu fiz (porque, spoiler, eu não), e você está pronto! Não há necessidade de VBA.

(Aviso: você mencionou um "não-problema" de vendas sem pedidos. Considere que, nesse caso, se você tiver um pedido, esse pedido será instantaneamente preenchido !)

Atualização 3 de fevereiro de 2017: Corrigido problema com MIN e MAX; deveria ter sido MAX e MIN.