Unique, Filter, Index, Sequence...

wildturkey

Board Regular
Joined
Feb 21, 2006
Messages
189
Office Version
  1. 365
Platform
  1. 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...

AJ.xlsx
ABCDEFGHIJKLMN
1CustomerTransaction TypeDateDocument NumberDue DateOpen BalanceCustomerTransaction TypeDateDocument NumberDue DateOpen Balance
2BobInvoice12/05/2022INV48939130/06/2022£17.00PeterInvoice07/06/2022INV49211031/07/2022£11.00
3BobInvoice07/06/2022INV49223831/07/2022£83.00PeterInvoice03/05/2022INV48804430/06/2022£34.00
4BobInvoice05/05/2022INV48845830/06/2022£29.00PeterInvoice03/05/2022INV48802930/06/2022£23.00
5BobInvoice11/05/2022INV48920930/06/2022£24.00PeterInvoice06/05/2022INV48864930/06/2022£58.00
6BobInvoice31/05/2022INV49169230/06/2022£72.00PeterInvoice09/05/2022INV48880230/06/2022£86.00
7PeterInvoice07/06/2022INV49211031/07/2022£11.00PeterInvoice24/05/2022INV49083830/06/2022£42.00
8PeterInvoice03/05/2022INV48804430/06/2022£34.00PeterInvoice25/05/2022INV49101130/06/2022£76.00
9PeterInvoice03/05/2022INV48802930/06/2022£23.00PeterInvoice09/06/2022INV49251731/07/2022£87.00
10PeterInvoice06/05/2022INV48864930/06/2022£58.00PeterInvoice04/04/2022INV48510631/05/2022£78.00
11PeterInvoice09/05/2022INV48880230/06/2022£86.00PeterInvoice12/04/2022INV48613131/05/2022£22.00
12PeterInvoice24/05/2022INV49083830/06/2022£42.00PeterInvoice25/04/2022INV48737031/05/2022£56.00
13PeterInvoice25/05/2022INV49101130/06/2022£76.00PeterInvoice27/04/2022INV48770131/05/2022£22.00
14PeterInvoice09/06/2022INV49251731/07/2022£87.00PeterInvoice03/05/2022INV48799630/06/2022£98.00
15PeterInvoice04/04/2022INV48510631/05/2022£78.00PeterInvoice05/05/2022INV48842330/06/2022£67.00
16PeterInvoice12/04/2022INV48613131/05/2022£22.00PeterInvoice11/05/2022INV48928130/06/2022£21.00
17PeterInvoice25/04/2022INV48737031/05/2022£56.00PeterInvoice11/05/2022INV48928430/06/2022£58.00
18PeterInvoice27/04/2022INV48770131/05/2022£22.00£839.00
19PeterInvoice03/05/2022INV48799630/06/2022£98.00TimInvoice11/05/2022INV48919130/06/2022£96.00
20PeterInvoice05/05/2022INV48842330/06/2022£67.00TimInvoice08/06/2022INV49240531/07/2022£40.00
21PeterInvoice11/05/2022INV48928130/06/2022£21.00TimInvoice06/06/2022INV49195431/07/2022£97.00
22PeterInvoice11/05/2022INV48928430/06/2022£58.00TimInvoice16/05/2022INV48981130/06/2022£56.00
23SarahInvoice23/05/2022INV49053730/06/2022£95.00TimInvoice23/05/2022INV49064430/06/2022£88.00
24JaneInvoice09/06/2022INV49257731/07/2022£86.00TimInvoice27/05/2022INV49130230/06/2022£60.00
25JaneInvoice11/05/2022INV48923230/06/2022£28.00TimInvoice31/05/2022INV49161030/06/2022£66.00
26JaneInvoice25/05/2022INV49093830/06/2022£11.00TimInvoice24/01/2022INV47693228/02/2022£17.00
27JaneInvoice31/05/2022INV49158630/06/2022£62.00TimInvoice06/05/2022INV48860330/06/2022£90.00
28JaneInvoice10/05/2022INV48908430/06/2022£22.00TimInvoice06/05/2022INV48860730/06/2022£33.00
29JaneInvoice09/06/2022INV49253531/07/2022£71.00TimInvoice06/05/2022INV48876730/06/2022£10.00
30TimInvoice11/05/2022INV48919130/06/2022£96.00£653.00
31TimInvoice08/06/2022INV49240531/07/2022£40.00JaneInvoice09/06/2022INV49257731/07/2022£86.00
32TimInvoice06/06/2022INV49195431/07/2022£97.00JaneInvoice11/05/2022INV48923230/06/2022£28.00
33TimInvoice16/05/2022INV48981130/06/2022£56.00JaneInvoice25/05/2022INV49093830/06/2022£11.00
34TimInvoice23/05/2022INV49064430/06/2022£88.00JaneInvoice31/05/2022INV49158630/06/2022£62.00
35TimInvoice27/05/2022INV49130230/06/2022£60.00JaneInvoice10/05/2022INV48908430/06/2022£22.00
36TimInvoice31/05/2022INV49161030/06/2022£66.00JaneInvoice09/06/2022INV49253531/07/2022£71.00
37TimInvoice24/01/2022INV47693228/02/2022£17.00£280.00
38TimInvoice06/05/2022INV48860330/06/2022£90.00BobInvoice12/05/2022INV48939130/06/2022£17.00
39TimInvoice06/05/2022INV48860730/06/2022£33.00BobInvoice07/06/2022INV49223831/07/2022£83.00
40TimInvoice06/05/2022INV48876730/06/2022£10.00BobInvoice05/05/2022INV48845830/06/2022£29.00
41BobInvoice11/05/2022INV48920930/06/2022£24.00
42BobInvoice31/05/2022INV49169230/06/2022£72.00
43£225.00
44SarahInvoice23/05/2022INV49053730/06/2022£95.00
45£95.00
CustomARAgingDetail
Cell Formulas
RangeFormula
N18N18=SUM(N2:N17)
N30N30=SUM(N19:N29)
N37N37=SUM(N31:N36)
N43N43=SUM(N38:N42)
N45N45=SUM(N44)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Couldn't you use a pivot table?
 
Upvote 0
Struggling to get anything that looks clean and tidy for an end user with little Excel knowledge to use and also get groupoing issues with the dates and totals really mess me up :(

AJ.xlsx
PQRST
1CustomerDocument NumberMonthsDateSum of Open Balance
2PeterINV485106Apr78
3PeterINV486131Apr22
4PeterINV487370Apr56
5PeterINV487701Apr22
6PeterINV487996May98
7PeterINV488029May23
8PeterINV488044May34
9PeterINV488423May67
10PeterINV488649May58
11PeterINV488802May86
12PeterINV489281May21
13PeterINV489284May58
14PeterINV490838May42
15PeterINV491011May76
16PeterINV492110Jun11
17PeterINV492517Jun87
18TimINV476932Jan17
19TimINV488603May90
20TimINV488607May33
21TimINV488767May10
22TimINV489191May96
23TimINV489811May56
24TimINV490644May88
25TimINV491302May60
26TimINV491610May66
27TimINV491954Jun97
28TimINV492405Jun40
29JaneINV489084May22
30JaneINV489232May28
31JaneINV490938May11
32JaneINV491586May62
33JaneINV492535Jun71
34JaneINV492577Jun86
35BobINV488458May29
36BobINV489209May24
37BobINV489391May17
38BobINV491692May72
39BobINV492238Jun83
40SarahINV490537May95
41Grand Total2092
CustomARAgingDetail
 
Upvote 0
Right click the date field and select Ungroup. Then just add subtotals to the Name column.
 
Upvote 0
Struggling with the sub total sorry

AJ.xlsx
PQRS
1CustomerDocument NumberDateSum of Open Balance
2BobINV48845805/05/202229
3BobINV488458 Total29
4BobINV48920911/05/202224
5BobINV489209 Total24
6BobINV48939112/05/202217
7BobINV489391 Total17
8BobINV49169231/05/202272
9BobINV491692 Total72
10BobINV49223807/06/202283
11BobINV492238 Total83
12Bob Total225
13JaneINV48908410/05/202222
14JaneINV489084 Total22
15JaneINV48923211/05/202228
16JaneINV489232 Total28
17JaneINV49093825/05/202211
18JaneINV490938 Total11
19JaneINV49158631/05/202262
20JaneINV491586 Total62
21JaneINV49253509/06/202271
22JaneINV492535 Total71
23JaneINV49257709/06/202286
24JaneINV492577 Total86
25Jane Total280
26PeterINV48510604/04/202278
27PeterINV485106 Total78
28PeterINV48613112/04/202222
29PeterINV486131 Total22
30PeterINV48737025/04/202256
31PeterINV487370 Total56
32PeterINV48770127/04/202222
33PeterINV487701 Total22
34PeterINV48799603/05/202298
35PeterINV487996 Total98
36PeterINV48802903/05/202223
37PeterINV488029 Total23
38PeterINV48804403/05/202234
39PeterINV488044 Total34
40PeterINV48842305/05/202267
41PeterINV488423 Total67
42PeterINV48864906/05/202258
43PeterINV488649 Total58
44PeterINV48880209/05/202286
45PeterINV488802 Total86
46PeterINV48928111/05/202221
47PeterINV489281 Total21
48PeterINV48928411/05/202258
49PeterINV489284 Total58
50PeterINV49083824/05/202242
51PeterINV490838 Total42
52PeterINV49101125/05/202276
53PeterINV491011 Total76
54PeterINV49211007/06/202211
55PeterINV492110 Total11
56PeterINV49251709/06/202287
57PeterINV492517 Total87
58Peter Total839
59SarahINV49053723/05/202295
60SarahINV490537 Total95
61Sarah Total95
62TimINV47693224/01/202217
63TimINV476932 Total17
64TimINV48860306/05/202290
65TimINV488603 Total90
66TimINV48860706/05/202233
67TimINV488607 Total33
68TimINV48876706/05/202210
69TimINV488767 Total10
70TimINV48919111/05/202296
71TimINV489191 Total96
72TimINV48981116/05/202256
73TimINV489811 Total56
74TimINV49064423/05/202288
75TimINV490644 Total88
76TimINV49130227/05/202260
77TimINV491302 Total60
78TimINV49161031/05/202266
79TimINV491610 Total66
80TimINV49195406/06/202297
81TimINV491954 Total97
82TimINV49240508/06/202240
83TimINV492405 Total40
84Tim Total653
85Grand Total2092
CustomARAgingDetail
 
Upvote 0
Right click the Invoice field and uncheck the subtotal option.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top