Orders of the date of a reference in the middle of other dates and references

Asked

Viewed 36 times

0

The situation becomes simpler to understand with a hypothetical scenario.

Imagine I bought 3 pieces:

Piece A

Piece B

Piece C.

The supplier, as he did not have everything ready for delivery, divided the delivery of the parts into 3 Fiscal Notes:

Fiscal Note 1 - issued on 08/10/2018

Fiscal Note 2 - issued on 12/08/2018

Fiscal Note 3 - issued on 08/14/2018

The result is then seen in the table below:

inserir a descrição da imagem aqui

What I want to do now (automatically, I imagine that through a combination of n formulas) is to sort the order of the Fiscal Notes according to their date of issue for each piece. For example, what I want is:

inserir a descrição da imagem aqui

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

The problem is that the table is not organized, there is an intercalation of the order of the pieces and order of the Fiscal Notes.

Any idea?

Thank you

excel table

  • Do not filter the desired Reference for the part, for example Peça A and then sort the dates from Older to Newer? Manually in the table filters.

  • is that I need to take out EVERY manual intervention in the database...without filtering, classifying etc

1 answer

1


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

Resultado

Where the formula is inserted into cell D2 and then replicated to the other cells with Self-prefilling.

+------------+-------------+--------------------------------+-------+
| 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>

Browser other questions tagged excel table

You are not signed in. Login or sign up in order to post.