In Excel, I'm looking to get a breakdown of the values in one column (IDs) by the values in another (status).

Input

| ID | Status |
|----|----------|
| 1 | Complete |
| 2 | Active |
| 3 | New |
| 4 | New |
| 5 | New |
| 6 | Complete |

Desired Output

| Status | IDs |
|----------|---------|
| Complete | 1, 6 |
| Active | 2 |
| New | 3, 4, 5 |

Google Sheets Workaround

In google sheets, you can do this with JOIN, FILTER, and UNIQUE as follows:

Generate Unique Statuses

Filter and Join Results

Other Workarounds

I can't use a pivot table, because I want a full list of the individual values, not any aggregated metrics.

I'm not sure if this is possible with Advanced Filter Query per Filter one column, using another column as criteria, but I haven't seemed to get it to work.

The original input can be formatted as a "table" in excel if there's a way query it using Power Query or some other tool, but I'd love to get a live list of the available filter values.

1

2 Answers

The Excel solution is (almost) exactly the same. UNIQUE is identical. The difference is the join:

=TEXTJOIN(", ",TRUE,FILTER($B$2:$B$7,$C$2:$C$7=$E2))

1

It seems these same functions in Google Sheets are available in Microsoft Excel as well:

• UNIQUE (Office 365)
• FILTER (Office 365)
• TEXTJOIN (2019)

=TEXTJOIN(",",TRUE,FILTER(A$2:A$7,B$2:B$7=D2))

Get Unique Values

Filter Column By Another Column and Join Results

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy