Date orders of a reference in the middle of other dates and references

Question

Date orders of a reference in the middle of other dates and references

Navigation

#1 by (1 votes)

0

The situation is simpler to understand with a hypothetical scenario.

Imagine that I bought 3 pieces:

Order A

Order B

Order C.

The supplier, as it did not have everything ready, divided the delivery of the pieces into 3 Tax Notes:

Invoice 1 - issued on 08/10/2018

Invoice 2 - issued on 08/12/2018

Nota Fiscal 3 - issued on 08/14/2018

The result is then seen in the table below:

WhatIwanttodonow(automatically,Iimaginethatthroughacombinationofnformulas)istosorttheorderoftheInvoicesaccordingtotheirdateofissueforeachpiece.Forexample,whatIwantis:

This is because Invoice 1 was the first to be issued, Invoice 2 in the second, and Invoice 3 in the third.

The problem is that the table is not organized, with an ordering of the parts and order of the Invoices.

Any ideas?

Thank you

excel tabela

asked by anonymous 16.08.2018 / 15:29

1 answer

Create slug across the MYSQL database Remove footer row

score 1 · Accepted Answer

Using the formula: =SOMARPRODUTO(($A2=$A$2:$A$7)*($C2>$C$2:$C$7))+1 you can sort the dates by groups in column A.

WheretheformulaisinsertedintocellD2andthenreplicatedtotheothercells with AutoFill .

+------------+-------------+--------------------------------+-------+
| Referência | Nota Fiscal | Data de emissão da Nota Fiscal | ordem |
+------------+-------------+--------------------------------+-------+
| Peça A     |        1001 | 10/08/2018                     |     2 |
| Peça A     |        1002 | 12/08/2018                     |     3 |
| Peça A     |        1003 | 09/08/2018                     |     1 |
| Peça B     |        1004 | 12/08/2018                     |     1 |
| Peça B     |        1005 | 14/08/2018                     |     2 |
| Peça C     |        1006 | 10/08/2018                     |     1 |
+------------+-------------+--------------------------------+-------+

<table><tbody><tr><th>Referência</th><th>Nota Fiscal</th><th>Data de emissão da Nota Fiscal</th><th>ordem</th></tr><tr><td>Peça A</td><td>1001</td><td>10/08/2018</td><td>2</td></tr><tr><td>Peça A</td><td>1002</td><td>12/08/2018</td><td>3</td></tr><tr><td>Peça A</td><td>1003</td><td>09/08/2018</td><td>1</td></tr><tr><td>Peça B</td><td>1004</td><td>12/08/2018</td><td>1</td></tr><tr><td>Peça B</td><td>1005</td><td>14/08/2018</td><td>2</td></tr><tr><td>Peça C</td><td>1006</td><td>10/08/2018</td><td>1</td></tr></tbody></table>