Formula to add unique values from range EXCEPT values in another range

kirbyfez

Board Regular
Joined
May 15, 2012
Messages
51
Hi guys,

I have no idea if this is possible but I hope so.

We have a document here at work where we log all our transactions and in the summary field we have lots of formulas based on customers which is all row based.

Issue is that we have certain codes that we do not want to include in the summary sheet and want these not to be added.

For example.

In Sheet called Monday there are customer names in B2:B301. I want the unique values from that range to be listed in B5:B29 in a sheet called summary. but would want it to not add all strings listed in range Z1:Z50 in sheet Admin...

This was very crappy explained but hopefully you get the drift.

At the moment I have a macro that can do it, but it's so slow and wouldnt work in the long run. Also have a formula that takes it over but doesnt exclude the ones that shouldnt be added.

Current formula is: =IF(ROWS(B$5:B5)>$B$1,"",(INDEX(Table1[CUSTOMER/STATUS],MATCH(ROWS(B$5:B5),Table1[Num],0))))
which is then dragged down from B5 to B29

Anyone that can offer some assistance here?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Watch MrExcel Video

Forum statistics

Threads
1,095,623
Messages
5,445,559
Members
405,339
Latest member
dayanand008

This Week's Hot Topics

Top