•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
FILTER function shows errors when combined with UNIQUE and CHOOSECOLS
Our take
Are you struggling with errors when combining the FILTER function with UNIQUE and CHOOSECOLS in your spreadsheet? You’re not alone. Many users face challenges when trying to extract specific data from large datasets. In your case, you want to filter column E for values greater than zero while utilizing the UNIQUE and CHOOSECOLS functions. Understanding the nuances of these formulas is key to achieving your goal. Read on to uncover practical solutions and tips to resolve the #VALUE!
I have a big data set on Sheet 1. On Sheet 2, I want to extract column E (contains value numbers) from Sheet 1. I use the following formula UNIQUE(CHOOSECOLS(‘Sheet 1’!A4:AD1048576),5)). The formula works. However, I want to show values greater than 0 only on Sheet 2. When I include FILTER function in like this UNIQUE(CHOOSECOLS(FILTER(‘Sheet 1’!A4:AD1048576), (‘Sheet 1’!E4:E1048576>0)),5)), it shows error #VALUE! for some reason. Does anyone know how to fix it? Any feedback is greatly appreciated. Thank. you!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Filter Function to Filter large data set to Multiple UsersI am trying to use the “FILTER” function to filter a large dataset that changes each day down to a specific group of users. I am getting stuck when trying to add a second “User” to pull from the data. My Setup: one sheet named “Dataset” is the data. It is many columns and I need the whole row for that user to be brought into the results. The column that houses the User ID is column R and has a couple hundred different users. Covers cells A1:AK11932 Second sheet named “users” is the list of user IDs Covers cells A1:A12 Third sheet named “results” is where I want the rows of filtered data to pull into. I have gotten a lot of different errors and can’t seem to nail down how to write the formula so it pulls every row with a user ID from my user list. The formula helper excel offers is not really helping. submitted by /u/JoeExoticHadAFarm [link] [comments]
- Criteria within FILTER for UNIQUE results based on a certain range.Here is my current formula: =FILTER(Grades!A:A,(Grades!D:D>=80)*(Grades!B:B=6)*(LEFT(Grades!F:F)="S")*(Grades!G:G<>"08320005")) This works wonderfully to pull out the information that I need with one potential issue. It will duplicate a result from the same course in different terms. The course numbers are in Grades!G:G, and the term ID's are in Grades!F:F. What criteria can I add to this FILTER that would yield results only one time if a course number in Grades!G:G is repeatedly matching the other criteria? I have used UNIQUE for this when I need the result of the FILTER to be UNIQUE, but I am not having luck with making a criteria look for uniques. submitted by /u/Subject_Jaguar_2724 [link] [comments]
- FILTER not working with a column that's a formula?Say I have a table with 4 columns, and I add column E that's a formula. I want to create a shadow table using the FILTER function based on this Column E. Is that not possible? I am able to get the formula to work when I just build it off Columns A-D. But when I expand it to E, it results in an #N/A error. submitted by /u/Trust_Issues2278 [link] [comments]
- Need filter function to return blank if blank and 0 if 0I have a formula to retrieve the value in a **unique** cell via filter. `=filter(range, condition*condition*condition)`. However, sometimes I am filtering to older data from before this particular point was collected and the records are blanks. This is currently showing as 0 when retrieved and the preferred behavior is showing NA(). Changing all retrieved 0s will not work, as some of the real data has actual zeroes. It needs to remain numbers, so adding &"" after the range will not work. I have also tried `=if(isblank(filter()),na(),filter())`, and it still returned 0. Manually changing the source data is not an option, as the input data is about 25k rows that change daily, copied in from a SQL query. submitted by /u/sauron3579 [link] [comments]
Tagged with
#Excel alternatives for data analysis#AI formula generation techniques#big data management in spreadsheets#generative AI for data analysis#rows.com#natural language processing for spreadsheets#conversational data analysis#Excel compatibility#real-time data collaboration#intelligent data visualization#financial modeling with spreadsheets#FILTER#UNIQUE#CHOOSECOLS#Sheet 1#Sheet 2#data set#column E#values greater than 0#error #VALUE!