Creating a summary using SUMIF, dynamically updates from input data & removes duplicates

Hi folks,

Can someone please have a look at my spread sheet please..

I need to achieve the following;


1) Take raw data that is 'paste-able' month by month and report our codes according to DCLG codes. Our Ledger Codes (often similar types) often 'feed' into one DCLG code.

2) I am using the SUMIF function to create the summary sheet. I was thinking of trying to apply a macro to remove duplicates but when I try this via the data tab function (remove duplicates) highlighted in light blue, it

doesn't give me a match to the original input table. Is my approach completely wrong?

3) I am using a data validation method via VLOOKUP that highlights in red if there is a new code.

I am just stuck on creating a summary sheet that will automatically update according to the 4 categories of data that we will paste in month by month : NL_Codes, Ledger_Type, This_Month, Year_to_Date.

4) The sum values need to match (shown on main tab)

Thank you very very much in anticipation.

Regards

Tutsyboy

=IFERROR(INDEX(DCLG_Codes,MATCH(0,COUNTIF($G$1:G1,DCLG_Codes),0)),"")
Confirmed using CONTROL + SHIFT + ENTER into cell G2 and copied down the column will give you a unique list of values.

Dan

Hi Dan,

Thanks for your help in cracking this issue.

I've put in your formula in G2, however, nothing seems to be populating. Am I missing something obvious?

I uploaded the file again with the original data in sheet 1 and some dummy data in the September tab.

from month to month the data may change slightly so the idea is to jus be able to paste the data in.

May I kindly ask you to look at the sheet again please and offer some advice as to why it's not auto populating the summary sheet?

Thanks once again.

Tutsyboy