• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique list based upon another columns value

M

mbrixius

New Member
I have a mini dashboard I am making to make it easier to check that our inventory is in balance.


I have a worksheet with 4 columns:


Financial Code

General Ledger Balance

Inventory Asset Balance

Difference


How can I make a list of unique Financial codes where the Difference column <> 0? I need this to be automatic so the dashboard summary worksheet can update automatically when the reports are imported. I prefer to avoid VBA when at all possible.


Addl info if needed:

The sheet works by taking 2 .csv files and pasting them into worksheets. Then another sheet finds all the unique values from one of the worksheets and uses those values to Vlookup() values from both. There is then a summary page that pulls data forward and charts it. What I am trying to add is a list on the summary page of out-of-balance financial codes


Thanks


Mike
 
Probably the easiest way is to put a filter on your table and control for Difference. You could select only instances where the difference is 0 to see financial codes in that group. If you are looking for non-0 differences you can filter for everything that isn't zero. Either way, you can select and copy the entire list of financial codes once the filter is in place.
 
Thanks for the input. I am looking for a way to have the summary sheet update after entering the data without employees needing excel knowledge. I there a way to make the filters "Sticky"?


Mike
 
Without using a VBA, I'm thinking that the best way to do this is to use a defined name, though I'm not that familiar with all of the capabilities of defined names. Most likely you will need an array formula within your defined name that only selects Financial Codes where the Difference is (or isn't as the case may be) 0. Then put the name in the cells that you want to display your list. That may work.
 
Here's an array formula (confirm using Ctrl+Shift+Enter) that will do the trick. Assuming you put this in E2:

=IF(MIN(IF(($D$2:$D$100<>0)*(COUNTIF($E$1:E1,$A$2:$A$100)=0),ROW($D$2:$D$100)))=0,"",INDEX(A:A,MIN(IF(($D$2:$D$100<>0)*(COUNTIF($E$1:E1,$A$2:$A$100)=0),ROW($D$2:$D$100)))))


Adjust the range sizes as needed.
 
You must log in or register to reply here.
Back
Top