Hi,
I am trying to find the way to obtain a list of values in same cell, based on 2 conditions.
I seem to be struggling with the right way based on below formula:
=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(ColumnB,(ColumnA=A2)*(ColumnC="Yes"),"")))
for some reason i obtain only 1 result instead of...
XUNIQUE is a powerful and straightforward alternative to the built-in UNIQUE with full control over data type(s) inclusion/exclusion
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description; and download...
I have a table extract and i'm using a combination of transpose and unique formula to bring in values onto Q2. I'm trying to filter this data so it only brings in column D, E, I L, and O. I keep trying to use the FILTER function but I get errors. I only need the yellow items in my example...
Hi,
I have a list of codes (column A), and amounts of money (Column B).
I would like to sum all of the codes but where the codes are the same, only have the sum for the first instance of that code from the list.
eg see the example below
TIA
CODE
AMOUNT
RESULT
CAT
5
11
DOG
6
16...
Hi,
would like to ask for help on this. I need to sort the table (new column with both new and theoretically repetitive tags added weekly) by match and frequency. Any idea how to do that?
Example:
first line - tag in row matches, most frequent amongst all columns
second line - tag in row...
Hi
I have a reference cell B2 which contains the year.
I would like to create a formula that contains dates (not referenced in cells) and counts the duplicate dates.
Within the formula I have the fixed dates DATE(YEAR(B2),1,1),DATE(YEAR(B2),3,2),DATE(YEAR(B2),5,1),DATE(YEAR(B2),5,9) and I would...
PART 1) I have a workbook that we keep track of all help hired along with a bunch of other information. I wanted to create a tab that will use the following criteria (State, Date Range, Used Multiple Times, Rehire Status) to retrieve the names of anyone who fit the criteria that is filled in. I...
I am running a college football pool and I want to get a list of all of the teams that are playing in the games this year. I want to return the names of the teams in column K & M, in one column (separate schedule) if Column C is "2022". Is there a way to use the filter to pull from both...
Hello, I have a button that creates 12 month sheets for a specific year that is entered into an inputbox. The code below enters values (coming from a master sheet list) randomly every workday per month sheet created.
I would like to have only unique random values per week, and a minimum of 2...
Hello,
I have an absence tracker which records absence in a current 6-month rolling period and a previous 6-month rolling period.
Each occasion of absence is recorded as a separate row and absence is manually added to the "current 6 month" sheet daily (as it occurs). A piece of VBA code then...
Hi
I need a formula which will count how many distinct values I have in a particular field in my dataset – I envisage something similar to COUNTIFS / SUMIFS. Below is a screenshot of my data:
The formula needs to count the number of distinct Customer IDs in a country but have the ability...
Hello,
I have 2 columns. Column A is 4 months (January - April), Column B is Years (2015 - 2020). Each Row has a different month and year, but some month and year combinations might repeat. I want to count the number of unique occurrences of repeating month and year combinations.
For example...
Hello all,
I need to produce a template for a table which shows me the number of distinct customers I have in each country each month.
I have a list of global sales transactions with Country, Customer ID and Sales ID:
• Each customer has a distinct Customer ID
• Each customer may have...
I have been successfully using this formula: =UNIQUE(FILTER(pp_tasks,(wbs_Hire=1))) with great success to apply an auto filter to a set of unique values. In a certain use case, this data set is still too large and I only want it to return the first 200 values. This formula: = INDEX(...
Hello!
I need to count unique values based on two criteria and this is the formula I've come up with. It successfully gets what I want, EXCEPT it returns a 1 when it should be returning a 0. I tried adding in an IFERROR function so that it doesn't count any #N/A's that the FILTER function...
I am measuring "population" for states/cities. I would like to get a list of top 3 states/cities in critical status based on population. The dataset I am using has metrics for years 1910-2017. Short example with made up data:
A
B
C
D
E
1
State
City
Year
Status
Population
2
Pennsylvania...
Hello everyone!
I have the following formula that extracts columns 1,2,3,6,7 from sheet1 into sheet2.
=FILTER(INDEX(Sheet1!A:G,SEQUENCE(ROWS(Sheet1!A:G)),{1,2,3,6,7}),ISNUMBER(SEARCH(Sheet2!B2,Sheet1!F:F)))
How can the formula be modified so that only unique records in sheet1:column2 are...
Hi all you wonderful people,
I am trying to return a list of distinct items using the sort formula below.
=SORT(UNIQUE(FILTER(sheet2!$P$2:$P$300,sheet2!$N$2:$N$300="Test1")))
This works perfectly, except I only want to return items where sheet 2 column N = “Test1” or “test2”.
I am guessing...
I am trying to make a Data Validation list from one of my tables, but I can't seem to get the UNIQUE function to work in the Name Manager. If I create a new Name called "Sports" and use the formula =tblGear[Sport], it works just fine. However, I want the unique values from there. I don't want it...
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.