Summarising a list

[bowry17]

Hi everyone,
this will be simple for anyone with half a brain cell, something I seem to be lacking.
I have some data which is basically a list of supplier invoices including supplier name, date of invoice, amount due.

All I am trying to do is:

1) link the data into a summary for each supplier
2) put a formula in so it only picks up a certain date range

For example

Data worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Supplier Name
[/TD]
[TD]Invoice Date
[/TD]
[TD]Invoice Ref
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]1/9/17
[/TD]
[TD]inv1
[/TD]
[TD]200.00
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]<strike></strike>16/9/17
[/TD]
[TD]inv3
[/TD]
[TD]100.00
[/TD]
[/TR]
[TR]
[TD]DEF
[/TD]
[TD]1/9/17
[/TD]
[TD]inv1
[/TD]
[TD]200.00
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]1/9/17
[/TD]
[TD]inv2
[/TD]
[TD]300.00
[/TD]
[/TR]
</tbody>[/TABLE]


I then have a separate tab for supplier remittances (one tab per supplier) where I will pay all invoices due from 1/9/17 to 15/9/17.
I need to show on the remittance advice the same info I show above (apart from supplier name).

I suppose the formula I am looking for is similar to what a pivot table does but I am getting myself in a right mess. Any help would be greatly appreciated.
thanks,
James

 

[RedBeard]

Might I suggest a proper database instead?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if you could make use of something like this.

Book1
	A	B	C	D
1	Supplier Name	Invoice Date	Invoice Ref	Amount
2	ABC	1/09/2017	inv1	200
3	ABC	16/09/2017	inv3	100
4	DEF	1/09/2017	inv1	200
5	ABC	1/09/2017	inv2	300
Data

On each supplier sheet (or you can just change the supplier name on the sheet)
C1 houses the supplier name
C2 & C3 holds your date range of interest
Formula in A6 is copied down as far as you might ever need. Column A could then be hidden if you want.
Formula in B6 is copied across and down as far as the formulas in column A (You may have to fix the date/number formatting in columns B & D after copying the formulas)

Book1
	A	B	C	D
1		Supplier	ABC
2		Start	1/09/2017
3		End	15/09/2017
4
5		Invoice Date	Invoice Ref	Amount
6	1	1/09/2017	inv1	200
7	4	1/09/2017	inv2	300
8
Supplier

Cell Formulas
Range	Formula
A6	=IFERROR(AGGREGATE(15,6,(ROW(Data!A$2:A$10)-ROW(Data!A$2)+1)/((Data!A$2:A$10=C$1)*(Data!B$2:B$10>=C$2)*(Data!B$2:B$10<=C$3)),ROWS(A$6:A6)),"")
B6	=IF(N($A6),INDEX(Data!B$2:B$10,$A6),"")

 

[bowry17]

Thank you so much for taking the time to respond.

This has worked so thanks again.

Can I take this a step further and amalgamate it? Let me try and clarify...

On the data table there will be scenarios where several rows will be part of an invoice, for example:

	A	B	C	D
Supplier Name	Invoice Ref
ABC	inv1
ABC	inv3
DEF	inv1
ABC	inv1

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Invoice Date[/TD]

[TD="align: right"]Amount[/TD]

[TD="align: center"] 2 [/TD]

[TD="align: right"] 1/09/2017 [/TD]

[TD="align: right"] 200 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]16/09/2017[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/09/2017[/TD]

[TD="align: right"]200[/TD]

[TD="align: center"] 5 [/TD]

[TD="align: right"] 1/09/2017 [/TD]

[TD="align: right"] 300 [/TD]

</tbody>

Data​

Therefore on the supplier sheet I would like it to show inv1 being £500 not two rows showing £200 and £300 like it currently does in the example you kindly gave.

Does that make sense?

Thanks again,
James

 

[Peter_SSs]

On the data table there will be scenarios where several rows will be part of an invoice,

Book1
	A	B	C	D
1	Supplier Name	Invoice Date	Invoice Ref	Amount
2	ABC	1/09/2017	inv1	200
3	ABC	16/09/2017	inv3	100
4	DEF	1/09/2017	inv1	200
5	ABC	1/09/2017	inv1	300
6	ABC	2/09/2017	inv2	300
Data

A6 copied down
B6 copied across to C6 and down
D6 copied down

Book1
	A	B	C	D
1		Supplier	ABC
2		Start	1/09/2017
3		End	15/09/2017
4
5		Invoice Date	Invoice Ref	Amount
6	1	1/09/2017	inv1	500
7	2	16/09/2017	inv3	100
8	5	2/09/2017	inv2	300
9
Supplier

Cell Formulas
Range	Formula
A6	=IFERROR(AGGREGATE(15,6,(ROW(Data!A$2:A$10)-ROW(Data!A$2)+1)/((Data!A$2:A$10=C$1)*(MATCH(Data!A$2:A$10&"|"&Data!C$2:C$10,Data!A$2:A$10&"|"&Data!C$2:C$10,0)=(ROW(Data!A$2:A$10)-ROW(Data!A$2)+1))),ROWS(A$6:A6)),"")
B6	=IF(N($A6),INDEX(Data!B$2:B$10,$A6),"")
D6	=IF(N($A6),SUMIFS(Data!D$2:D$10,Data!A$2:A$10,C$1,Data!C$2:C$10,C6),"")

 

[bowry17]

Peter,
apologies for the late reply.

The formula above (for cell A6) has worked in so much I do not receive an error message from excel but it returns nothing so something is going wrong.

Can I ask what "|" means?

if easier I am happy to send you the spread sheet to look at if you are open to looking at it?

thanks as ever.
James

 

[bowry17]

Peter,
I have now got it to work however I think we have lost the date range part of the formula for cell A6.

In your first version you had something in for the date range but its not there on the amended one.

Could you assist in getting it back in?

I think it will be fine then.
thanks,
James

 

[Peter_SSs]

.. I think we have lost the date range part of the formula for cell A6.

Yes, my mistake. For the layout in post #5 , the formula for cell A6 on the Supplier sheet should be

=IFERROR(AGGREGATE(15,6,(ROW(Data!A$2:A$10)-ROW(Data!A$2)+1)/((Data!A$2:A$10=C$1)*(MATCH(Data!A$2:A$10&"|"&Data!C$2:C$10,Data!A$2:A$10&"|"&Data!C$2:C$10,0)=(ROW(Data!A$2:A$10)-ROW(Data!A$2)+1))*(Data!B$2:B$10>=C$2)*(Data!B$2:B$10<=C$3)),ROWS(A$6:A7)),"")