Macro or Formula for Unique Value based on multiple criteria

[Neilesh Kumar]

Dear Experts,

Please find here attached workbook, where i am looking for the macro or formula for unique account description under Income and Expenses heading from data worksheet based on the selected date (Cell C2 in Details worksheet). Worksheet and workbook will be protected after the completion of the assignment.

I hope i have explained everything from my end. If you do require further clarification, Please do let me know.

Many thanks for your kind help.

Regards,

Neilesh

[Glenn Kennedy]

One way, using variants of this array formula:

=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(0,INDEX(IF(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=Details!$B$5,IF(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=Details!$C$2,COUNTIF(Details!$B$7:B7,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))),0),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Neilesh Kumar]

One way, using variants of this array formula:

=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(0,INDEX(IF(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=Details!$B$5,IF(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=Details!$C$2,COUNTIF(Details!$B$7:B7,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))),0),0)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Thank you so much Dear Expert. Thanks a lot once again for your precious help.

Many thanks.

Regards,


Neilesh

[Glenn Kennedy]

You're welcome!!

[Indi_Ra]

Glen your formula for 28.02.2017 in Expenses, don't work

For Expenses you have to change your formula:

=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(0,INDEX(IF(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=Details!$B$5,IF(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=Details!$C$2,COUNTIF(Details!$B$7:B7,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))),0),0)),"")

to

=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(0,INDEX(IF(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=Details!$B$5,IF(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=Details!$C$2,COUNTIF(Details!$B$26:B26,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))),0),0)),"")

[Glenn Kennedy]

Thnks for the correction!! You're right.

[Neilesh Kumar]

Glen your formula for 28.02.2017 in Expenses, don't work

For Expenses you have to change your formula:

=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(0,INDEX(IF(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=Details!$B$5,IF(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=Details!$C$2,COUNTIF(Details!$B$7:B7,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))),0),0)),"")

to

=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(0,INDEX(IF(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=Details!$B$5,IF(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=Details!$C$2,COUNTIF(Details!$B$26:B26,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))),0),0)),"")

Thank you so much dear Expert. Many thanks and regards to your valuable support and precious feedback.

Regards,

Neilesh

[Indi_Ra]

As an alternative:
Array formula
For Income:
=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(1,(1+COUNTIF(Details!$B$7:B7,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))*(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=$B$5)*(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=$C$2),0),1),"")

For Expenses:
=IFERROR(INDEX(Table_Acct_VW_BudgetVsActualDateWise_1[Account],MATCH(1,(1+COUNTIF(Details!$B$26:B26,Table_Acct_VW_BudgetVsActualDateWise_1[Account]))*(Table_Acct_VW_BudgetVsActualDateWise_1[GroupTypeName]=$B$24)*(Table_Acct_VW_BudgetVsActualDateWise_1[Apldate]=$C$2),0),1),"")

[Neilesh Kumar]

Dear Experts,

Thank you so much for your precious support and valuable contribution. Due to modified requirement now i have two dates (Current Year and Previous Year) for the same solution provided based on one criteria as current year date requirement. Request to you kindly do provide the solution for the same.

Please find here attached workbook for your valuable support and precious contribution. Thank you so much once again for your precious help.

Regards,

Neilesh

[Glenn Kennedy]

Confused. You have one date column in your raw data and 2 date criteria???? Which date is to be matched where? More explanation, please.

[Neilesh Kumar]

Confused. You have one date column in your raw data and 2 date criteria???? Which date is to be matched where? More explanation, please.

Dear Expert,

I have to match with both the dates as Current Year and for the same date as previous year Sir. Suppose if the one data for the current year is matching with both the dates then only one unique item will be appeared but if it is matching with one and not the another date then only one unique item will be appeared under Income and Expenses.

I hope i have explained Sir. Please do let me know if you do require some more details on the same.

Thank you once again for your precious support Sir.

Regards,

Neilesh

[Indi_Ra]

I don't understand.

[Glenn Kennedy]

Thank you Indi Ra!! I have read this 3 or 4 times and still don't have a clue about what is wanted!!! I feel better, knowing that you don't get it either!!

[Neilesh Kumar]

Thank you Indi Ra!! I have read this 3 or 4 times and still don't have a clue about what is wanted!!! I feel better, knowing that you don't get it either!!

Dear Experts,

In the Details tab there are two dates, one is for the current year and another is for the previous year same date and i am looking for the unique account description based on these two dates as well Income and Expense criteria.

If the current year based unique account description is available then that will be appeared based on the current year date, in Details tab and if the previous year account description is available based on the previous year date then that will be appeared but if there is common account description is available for both the dates then a common account description will be appeared for Income and Expenses.

I hope now it is cleared Dear Experts. Please do let me know for further clarification.

Many thanks and kind regards,

Neilesh

[Glenn Kennedy]

You explanation does not help. sorry!!!

This sentence was too long!! "If the current year based unique account description is available then that will be appeared based on the current year date, in Details tab and if the previous year account description is available based on the previous year date then that will be appeared but if there is common account description is available for both the dates then a common account description will be appeared for Income and Expenses."


Open the attached sheet. for 18/1/17 and 18/1/16 coloured yellow (inc) and Gold (exp), what result do you expect to see???

[Neilesh Kumar]

You explanation does not help. sorry!!!

This sentence was too long!! "If the current year based unique account description is available then that will be appeared based on the current year date, in Details tab and if the previous year account description is available based on the previous year date then that will be appeared but if there is common account description is available for both the dates then a common account description will be appeared for Income and Expenses."


Open the attached sheet. for 18/1/17 and 18/1/16 coloured yellow (inc) and Gold (exp), what result do you expect to see???

Dear Expert,

Thank you so much for your precious efforts. But what if the current year date is not available in the raw data and only the previous year data is available then the unique account description will appear as blank. That is what i am looking for Sir and if the unique account description is available for both the year dates then a single account description will be appeared which will be common for both the dates.

I hope i have cleared now Sir.

Many thanks and kind regards,

Neilesh

[Neilesh Kumar]

Dear Expert,

I am looking unique account description based on either previous year date or the current year date given in the details tab or common for the both the dates. Request to you please help me out Sir.

Regards,

Neilesh

[Neilesh Kumar]

You explanation does not help. sorry!!!

This sentence was too long!! "If the current year based unique account description is available then that will be appeared based on the current year date, in Details tab and if the previous year account description is available based on the previous year date then that will be appeared but if there is common account description is available for both the dates then a common account description will be appeared for Income and Expenses."


Open the attached sheet. for 18/1/17 and 18/1/16 coloured yellow (inc) and Gold (exp), what result do you expect to see???

Dear Experts,

Please find here attached revised workbook, i have highlighted where few dates are common but few are unique for the current year as well as for the previous year and based on the same i am looking for the formula that if the in the current year account description is not available and if the same is available for the previous year then that will appear dear Experts.

Thank you for your precious support and valuable cooperation dear Experts.

Regards,

Neilesh

[Glenn Kennedy]

I still don't know what your expected answers look like. So, this is a guess. Both are array formulae and can only be followed by looking at the attached sheet.

[Neilesh Kumar]

Istill don't know what your expected answers look like. So, this is a guess. Both are arry formulae and can only be followed by looking at the attached sheet.

Dear Sir,

Thank you very much Sir. Exactly the same i was looking for Sir. Thanks you so much once again Sir.

Many Thanks and Kind Regards,

Neilesh

[Glenn Kennedy]

That was lucky!! You're welcome. "See" you again sometime. A word of advice. If you can, always include some manually calculated expected results.

[Neilesh Kumar]

That was lucky!! You're welcome. "See" you again sometime. A word of advice. If you can, always include some manually calculated expected results.

Sure Sir I will. Thank you so much Sir once again for your precious and valuable support.

Regards,

Neilesh