Customer Accounting SUMMER

[makinmomb]

This is my raw data on sheet 1

Reference	Account	Date	Value
INV 218736	A285	17/12/2013	14,667.60
INV 218753	B095	17/12/2013	136,840.20
INV 218778	K066	17/12/2013	17,990.00
INV 218803	N249	17/12/2013	48,780.00
INV 218824	P163	17/12/2013	35,040.00
INV 218916	C028	18/12/2013	45,220.00
INV 218917	J037	18/12/2013	45,143.00
INV 218926	J004	18/12/2013	162,000.00
INV 218944	B095	18/12/2013	307,400.00
INV 218997	C038	18/12/2013	800,000.00

<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <tbody> </tbody>

This is my sheet2 where I need the summary account totals expecting B095 to add up, what formula on cell A2 AND B2 that I will copy downwards, please answer considering data will more thus read range should be to infinity

Account	Value

<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

 

[etaf]

you may want to specify the range - using the whole column will slow the update down considerably - hence the range I put

 

[makinmomb]

Yes I am sorted I realised there was only a small typo thus with these two I have now fully workable file, thank you ETAF

=IFERROR(INDEX(Sheet1!B:B,MATCH(0,COUNTIF($A1:A$1, Sheet1!B:B), 0)),"")

=SUMIF(Sheet1!B:B,A:A,Sheet1!D:D)

Its working tested it on huge data, but it takes a longer time to process, I have to leave the excel sheet on minimize , surely I have to look into PIVOT TABLES , though never used before

 

[etaf]

Its working tested it on huge data, but it takes a longer time to process, I have to leave the excel sheet on minimize , surely I have to look into PIVOT TABLES , though never used before

yep

try it

select the data table
then
insert pivot table
now on the right hand side clickin the box for the
account and the Value fields

now they both will likely go into the row labels
drag the values across to the
value box
now click on the arrow in the count of value
value field settings
and change to sum

pivot table done

 

[MARK858]

now they both will likely go into the row labels

Just drag the Account to the row labels and the Values to the value label. Because the values are all numbers with no spaces it will default to Sum.

 

[makinmomb]

yep

try it

select the data table
then
insert pivot table
now on the right hand side clickin the box for the
account and the Value fields

now they both will likely go into the row labels
drag the values across to the
value box
now click on the arrow in the count of value
value field settings
and change to sum

pivot table done

I am glad to see how a PIVOT works , is quicker for this kind of data

 

[Fazza]

yes, pivot tables are great for well structured data

by default, pivot tables don't automatically update - they need a refresh. if you're not OK with that, please google or ask

otherwise what happens is the source data changes but not the pivot table & someone looking at the pivot table may get the wrong answer...

 

[makinmomb]

yes, pivot tables are great for well structured data

by default, pivot tables don't automatically update - they need a refresh. if you're not OK with that, please google or ask

otherwise what happens is the source data changes but not the pivot table & someone looking at the pivot table may get the wrong answer...

I am already using PIVOT TABLS now, where is the refresh button as would need it one of these days

 

[etaf]

if on 2010 version

you should see
pivottable tools on the ribbon
options

refresh is in the middle(ish) of the ribbon

 

[Fazza]

or if you've selected somewhere in the pivot table, via the keyboard use ALT-D-R

 

[Smitty]

And you can also use VBA to automatically refresh the PT when the sheet is activated.