MS Excel Formula: Remove Duplicates, Blanks, Sort Alphabetically based on a Criteria
I would like a formula that would Extract Unique Values Column: (C2:C30), based on criteria (A2="Car") that matches Column (B2:B30), and Sort in Ascending Order (A-Z), while removing blanks and produce "Output Results" in Column: (D2:D30).
EXAMPLE:
Column A2 Car
Column B2:B30 (Group) Column C2:C30 (Type)
Car Chevrolet
Car Ford
Car Honda
Truck GMC
Car Honda
Car Lexus
SUV Cadillac
Car Kia
Car Toyota
Car Mercedes
Car BMW
SUV GMC
Truck Ford
Car Hyundai
Truck Toyota
Car Cadillac
Car Pontiac
====================================
Column D2:D30 based on "Car" criteria (Output Results)
BMW
Cadillac
Chevrolet
Ford
Honda
Hyundai
Kia
Lexus
Mercedes
Pontiac
Toyota
With Office 365 one can use the following formula to display the list:
=SORT(UNIQUE(FILTER(C2:C30,B2:B30=A2)))
As of the date of this answer these formulas are coming, and only available to those who are part of the office insiders group. But will hopefully soon be sent to all Office 365 users.
I put this here for future reference.
- Excel number format to only show decimals when necessary
- Private Sub for hiding worksheets based on cell value "View" / "Hidden"
- Power Query Filtered Rows based on value
- EDI translator to excel
- If formula (the sum is less than 30 then roundup tp 30)
- Unable to use a dynamic named range as a data series - what am I doing wrong?
- How to get the path of current worksheet in VBA?
- vba + copy sheet from one wb to another wb
- Is it still not possible to open Excel from a Word Add-In?
- Excel-365 functions don't work as expected
- Sum values based on three criteria and transpose result from tabular to visual layout
- How to use fraction string in Excel/Calc/Sheets as formula
- VBA Code Save in Local and Upload to Sharepoint
- List Sheet Names Without Macro or Excel 4 Functions
- Delete a specific value from one column and Rename another Value in Excel using office script
- Highlight cell in first column if row contains a partial text match
- Excel - Aggregate dynamically values in a Table using array formulas (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) in a single Cell
- How to convert a FILETIME to an excel date
- VBA CountIf() loop / Countifs() loop
- How to construct Dataframe from a Excel (xls,xlsx) file in Scala Spark?
- excel filter data and remove units of measurement
- How to reference the current User\Desktop location in VBA
- Apache POI autoSizeColumn Resizes Incorrectly
- Copy cell values to another worksheet based on cell value
- Import Excel to Pandas Dataframe, but preserve order of Day/Month in dates?
- M Code(Power Query) to Remove Empty columns that runs fast
- Excel Count if first character is 0 in string
- How can I send Excel range including header, in table, and email to list?
- Dropdown Lists Emptying When Filling Data in Worksheet
- Excel: How to take a weighted average where blank values cause proportional weighting shifts