wildturkey
Board Regular
- Joined
- Feb 21, 2006
- Messages
- 189
- Office Version
- 365
- Platform
- Windows
Morning
I'm hoping some magic combination of the above would allow me to take the data set in columns A-F and produce the report I want as shown in I-N - basically, it's listing a debtors report but putting the biggest debtor first with an overall value and listing unpaid invoices...
Well beyond my ability so any help really appreciated...
I'm hoping some magic combination of the above would allow me to take the data set in columns A-F and produce the report I want as shown in I-N - basically, it's listing a debtors report but putting the biggest debtor first with an overall value and listing unpaid invoices...
Well beyond my ability so any help really appreciated...
AJ.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | Customer | Transaction Type | Date | Document Number | Due Date | Open Balance | Customer | Transaction Type | Date | Document Number | Due Date | Open Balance | ||||
2 | Bob | Invoice | 12/05/2022 | INV489391 | 30/06/2022 | £17.00 | Peter | Invoice | 07/06/2022 | INV492110 | 31/07/2022 | £11.00 | ||||
3 | Bob | Invoice | 07/06/2022 | INV492238 | 31/07/2022 | £83.00 | Peter | Invoice | 03/05/2022 | INV488044 | 30/06/2022 | £34.00 | ||||
4 | Bob | Invoice | 05/05/2022 | INV488458 | 30/06/2022 | £29.00 | Peter | Invoice | 03/05/2022 | INV488029 | 30/06/2022 | £23.00 | ||||
5 | Bob | Invoice | 11/05/2022 | INV489209 | 30/06/2022 | £24.00 | Peter | Invoice | 06/05/2022 | INV488649 | 30/06/2022 | £58.00 | ||||
6 | Bob | Invoice | 31/05/2022 | INV491692 | 30/06/2022 | £72.00 | Peter | Invoice | 09/05/2022 | INV488802 | 30/06/2022 | £86.00 | ||||
7 | Peter | Invoice | 07/06/2022 | INV492110 | 31/07/2022 | £11.00 | Peter | Invoice | 24/05/2022 | INV490838 | 30/06/2022 | £42.00 | ||||
8 | Peter | Invoice | 03/05/2022 | INV488044 | 30/06/2022 | £34.00 | Peter | Invoice | 25/05/2022 | INV491011 | 30/06/2022 | £76.00 | ||||
9 | Peter | Invoice | 03/05/2022 | INV488029 | 30/06/2022 | £23.00 | Peter | Invoice | 09/06/2022 | INV492517 | 31/07/2022 | £87.00 | ||||
10 | Peter | Invoice | 06/05/2022 | INV488649 | 30/06/2022 | £58.00 | Peter | Invoice | 04/04/2022 | INV485106 | 31/05/2022 | £78.00 | ||||
11 | Peter | Invoice | 09/05/2022 | INV488802 | 30/06/2022 | £86.00 | Peter | Invoice | 12/04/2022 | INV486131 | 31/05/2022 | £22.00 | ||||
12 | Peter | Invoice | 24/05/2022 | INV490838 | 30/06/2022 | £42.00 | Peter | Invoice | 25/04/2022 | INV487370 | 31/05/2022 | £56.00 | ||||
13 | Peter | Invoice | 25/05/2022 | INV491011 | 30/06/2022 | £76.00 | Peter | Invoice | 27/04/2022 | INV487701 | 31/05/2022 | £22.00 | ||||
14 | Peter | Invoice | 09/06/2022 | INV492517 | 31/07/2022 | £87.00 | Peter | Invoice | 03/05/2022 | INV487996 | 30/06/2022 | £98.00 | ||||
15 | Peter | Invoice | 04/04/2022 | INV485106 | 31/05/2022 | £78.00 | Peter | Invoice | 05/05/2022 | INV488423 | 30/06/2022 | £67.00 | ||||
16 | Peter | Invoice | 12/04/2022 | INV486131 | 31/05/2022 | £22.00 | Peter | Invoice | 11/05/2022 | INV489281 | 30/06/2022 | £21.00 | ||||
17 | Peter | Invoice | 25/04/2022 | INV487370 | 31/05/2022 | £56.00 | Peter | Invoice | 11/05/2022 | INV489284 | 30/06/2022 | £58.00 | ||||
18 | Peter | Invoice | 27/04/2022 | INV487701 | 31/05/2022 | £22.00 | £839.00 | |||||||||
19 | Peter | Invoice | 03/05/2022 | INV487996 | 30/06/2022 | £98.00 | Tim | Invoice | 11/05/2022 | INV489191 | 30/06/2022 | £96.00 | ||||
20 | Peter | Invoice | 05/05/2022 | INV488423 | 30/06/2022 | £67.00 | Tim | Invoice | 08/06/2022 | INV492405 | 31/07/2022 | £40.00 | ||||
21 | Peter | Invoice | 11/05/2022 | INV489281 | 30/06/2022 | £21.00 | Tim | Invoice | 06/06/2022 | INV491954 | 31/07/2022 | £97.00 | ||||
22 | Peter | Invoice | 11/05/2022 | INV489284 | 30/06/2022 | £58.00 | Tim | Invoice | 16/05/2022 | INV489811 | 30/06/2022 | £56.00 | ||||
23 | Sarah | Invoice | 23/05/2022 | INV490537 | 30/06/2022 | £95.00 | Tim | Invoice | 23/05/2022 | INV490644 | 30/06/2022 | £88.00 | ||||
24 | Jane | Invoice | 09/06/2022 | INV492577 | 31/07/2022 | £86.00 | Tim | Invoice | 27/05/2022 | INV491302 | 30/06/2022 | £60.00 | ||||
25 | Jane | Invoice | 11/05/2022 | INV489232 | 30/06/2022 | £28.00 | Tim | Invoice | 31/05/2022 | INV491610 | 30/06/2022 | £66.00 | ||||
26 | Jane | Invoice | 25/05/2022 | INV490938 | 30/06/2022 | £11.00 | Tim | Invoice | 24/01/2022 | INV476932 | 28/02/2022 | £17.00 | ||||
27 | Jane | Invoice | 31/05/2022 | INV491586 | 30/06/2022 | £62.00 | Tim | Invoice | 06/05/2022 | INV488603 | 30/06/2022 | £90.00 | ||||
28 | Jane | Invoice | 10/05/2022 | INV489084 | 30/06/2022 | £22.00 | Tim | Invoice | 06/05/2022 | INV488607 | 30/06/2022 | £33.00 | ||||
29 | Jane | Invoice | 09/06/2022 | INV492535 | 31/07/2022 | £71.00 | Tim | Invoice | 06/05/2022 | INV488767 | 30/06/2022 | £10.00 | ||||
30 | Tim | Invoice | 11/05/2022 | INV489191 | 30/06/2022 | £96.00 | £653.00 | |||||||||
31 | Tim | Invoice | 08/06/2022 | INV492405 | 31/07/2022 | £40.00 | Jane | Invoice | 09/06/2022 | INV492577 | 31/07/2022 | £86.00 | ||||
32 | Tim | Invoice | 06/06/2022 | INV491954 | 31/07/2022 | £97.00 | Jane | Invoice | 11/05/2022 | INV489232 | 30/06/2022 | £28.00 | ||||
33 | Tim | Invoice | 16/05/2022 | INV489811 | 30/06/2022 | £56.00 | Jane | Invoice | 25/05/2022 | INV490938 | 30/06/2022 | £11.00 | ||||
34 | Tim | Invoice | 23/05/2022 | INV490644 | 30/06/2022 | £88.00 | Jane | Invoice | 31/05/2022 | INV491586 | 30/06/2022 | £62.00 | ||||
35 | Tim | Invoice | 27/05/2022 | INV491302 | 30/06/2022 | £60.00 | Jane | Invoice | 10/05/2022 | INV489084 | 30/06/2022 | £22.00 | ||||
36 | Tim | Invoice | 31/05/2022 | INV491610 | 30/06/2022 | £66.00 | Jane | Invoice | 09/06/2022 | INV492535 | 31/07/2022 | £71.00 | ||||
37 | Tim | Invoice | 24/01/2022 | INV476932 | 28/02/2022 | £17.00 | £280.00 | |||||||||
38 | Tim | Invoice | 06/05/2022 | INV488603 | 30/06/2022 | £90.00 | Bob | Invoice | 12/05/2022 | INV489391 | 30/06/2022 | £17.00 | ||||
39 | Tim | Invoice | 06/05/2022 | INV488607 | 30/06/2022 | £33.00 | Bob | Invoice | 07/06/2022 | INV492238 | 31/07/2022 | £83.00 | ||||
40 | Tim | Invoice | 06/05/2022 | INV488767 | 30/06/2022 | £10.00 | Bob | Invoice | 05/05/2022 | INV488458 | 30/06/2022 | £29.00 | ||||
41 | Bob | Invoice | 11/05/2022 | INV489209 | 30/06/2022 | £24.00 | ||||||||||
42 | Bob | Invoice | 31/05/2022 | INV491692 | 30/06/2022 | £72.00 | ||||||||||
43 | £225.00 | |||||||||||||||
44 | Sarah | Invoice | 23/05/2022 | INV490537 | 30/06/2022 | £95.00 | ||||||||||
45 | £95.00 | |||||||||||||||
CustomARAgingDetail |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N18 | N18 | =SUM(N2:N17) |
N30 | N30 | =SUM(N19:N29) |
N37 | N37 | =SUM(N31:N36) |
N43 | N43 | =SUM(N38:N42) |
N45 | N45 | =SUM(N44) |