Filter and Unique missing data

[KillerOfGiants]

Ok, I cannot share data as it is confidential and changing it to "clean" samples does not recreate the problem. Here's the scenario.
I have a tab (2a) where column C is a Location (row 1 is the title "Location", rows 2 and beyond are pasted from a csv file). We currently have 9 locations with 15 rows of data for each location. So our 2a tab has information from rows 1 through 136. As we strive for flexibility, I am using a formula to extract the locations, but when I add UNIQUE to the filtered formula, it excludes one of the locations. And again, I changed all the locations to sample data, and then it suddenly worked.

The location data is in the format of NNNN NAME L# where NNNN is a 4 digit unique number identifying the location, NAME is the brand name (two brands), and L# is the brand name location number (i.e: 1, 2, etc).

The data that is not being portrayed is from rows 62-76 which is the first of 2 for it's brand. Brand 2 is "2001 NAME 1:" and "2002 NAME 2:" so the difference is in the 4th digit and the last digit. Brand 1 has 7 locations and has similar differences between the each other, and works fine.

Here is the original FILTER formula: (Note, this displays the 15 locations for each brand correctly)

=(FILTER('2a'!$C$1:$C$1000,('2a'!$C$1:$C$1000<>"Location")*('2a'!$C$1:$C$1000<>"")))

And here is the UNIQUE formula: (and if I don't add an extra set of parentheses, I get a typo error?)

=UNIQUE((FILTER('2a'!$C$1:$C$1000,('2a'!$C$1:$C$1000<>"Location")*('2a'!$C$1:$C$1000<>""))))

 

[etaf]

wheres the extra set of parenthesis ?

( FILTER( ..... ) )) - three
UNIQUE(....)
so
UNIQUE( ( FILTER( ... ) )) )
so 4 at end

unique is only looking at columnC - as thats all its filtering

not sure i follow sorry

and changing it to "clean" samples does not recreate the problem.

So there must be something in the dataset you are using that is not working

have you tried to copy column C to a new workbook and applying the formula

FILTER - gives you ALL the entries correctly with duplicates
UNIQUE - does not provide all the unique entries
is that correct

 

[KillerOfGiants]

The filter formula is = ( filter formula ). It has the entire formula in parentheses, so adding Unique outside of it shouldn't require another set of parentheses.

I have copied the data to an empty workbook. When I changed it to "dummy" data, the formulas worked fine even AFTER I changed the dummy data back to proprietary data.

You're correct. FILTER gives me 135 rows of data with 15 rows for each of the 9 locations. Once I had the UNIQUE to it, it loses one of the locations.

 

[etaf]

I have copied the data to an empty workbook. When I changed it to "dummy" data, the formulas worked fine even AFTER I changed the dummy data back to proprietary data.

so its fixed in a new workbook - maybe some corruption to the orginal workbook, I have to say I have seen these strange issues quite a few times in last few days
pivot table not working
cells not displaying the text although in formula bar
and various others

The filter formula is = ( filter formula ). It has the entire formula in parentheses, so adding Unique outside of it shouldn't require another set of parentheses.

Good point , maybe as i say issue with the workbook

 

[KillerOfGiants]

I tried copying and pasting in same workbook works after changing dummy data to regular data. I just don't know.

 

[etaf]

works after changing dummy data to regular data.

As i said before

and changing it to "clean" samples does not recreate the problem.

So there must be something in the dataset you are using that is not working

have you tried to copy column C to a new workbook and applying the formula

I think we will need to see your data - see how below

Here you can see the FILTER working and UNIQUE working - so nothing wrong with formula (i just have not refered to a different sheet
AND AS YOU SAY - works with dummy / clean samples

Book2
	A	B	C	D	E	F
1					FILTER	UNIQUE
2			fred		fred	fred
3			harry		harry	harry
4			john		john	john
5			location		john
6			john		harry
7			harry		fred
8			fred
9			location
10			location
11
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=(FILTER($C$1:$C$1000,($C$1:$C$1000<>"Location")*($C$1:$C$1000<>"")))
F2:F4	F2	=UNIQUE(FILTER($C$1:$C$1000,($C$1:$C$1000<>"Location")*($C$1:$C$1000<>"")))
Dynamic array formulas.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone