How to display pull filtered data from another sheet in Excel by using a formula? - Stack Overflow

I need to pull filtered data from the Original Data Sheet and show the full row information in the Final Sheet.

If an ID has both 'Not Started' and 'Archived' statuses, I should pull 'Not Started' instead of 'Archived'. However, if 'Archived' is the only status for an ID, I need to pull 'Archived'.

Original Table:

| ID | Name   | Status      | Other Column |
|----|--------|-------------|--------------|
| 1  | Task 1 | Archived    | Random Text  |
| 2  | Task 2 | Not Started | Random Text  |
| 2  | Task 2 | Archived    | Random Text  |
| 3  | Task 3 | Not Started | Random Text  |
| 4  | Task 4 | Archived    | Random Text  |
| 5  | Task 5 | Archived    | Random Text  |
| 5  | Task 5 | Not Started | Random Text  |

I need to pull filtered data from the Original Data Sheet and show the full row information in the Final Sheet.

If an ID has both 'Not Started' and 'Archived' statuses, I should pull 'Not Started' instead of 'Archived'. However, if 'Archived' is the only status for an ID, I need to pull 'Archived'.

Original Table:

| ID | Name   | Status      | Other Column |
|----|--------|-------------|--------------|
| 1  | Task 1 | Archived    | Random Text  |
| 2  | Task 2 | Not Started | Random Text  |
| 2  | Task 2 | Archived    | Random Text  |
| 3  | Task 3 | Not Started | Random Text  |
| 4  | Task 4 | Archived    | Random Text  |
| 5  | Task 5 | Archived    | Random Text  |
| 5  | Task 5 | Not Started | Random Text  |

• excel
• excel-formula

Share Improve this question Follow edited Feb 4 at 10:13 Darren Bartrup-Cook 19.9k22 gold badges2727 silver badges4949 bronze badges asked Feb 4 at 9:59 TraceyTracey 1333 bronze badges 1

• How should I update the formula to include 4 statuses? The prioritization steps are Not Yet Started Refresh Current Archived – Tracey Commented Feb 14 at 15:24

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

Try the following formula-

=FILTER(A2:D8,MAP(B2:B8,C2:C8,LAMBDA(x,y,OR(COUNTIFS(B2:B8,x)=1,(COUNTIFS(B2:B8,x)>1)*(y="Not Started")))))

Input:

ID	Name	Status	Other Column
1	Task 1	Archived	Random Text
2	Task 2	Not Started	Random Text
2	Task 2	Archived	Random Text
3	Task 3	Not Started	Random Text
4	Task 4	Archived	Random Text
5	Task 5	Archived	Random Text
5	Task 5	Not Started	Random Text

Output:

ID	Name	Status	Other Column
1	Task 1	Archived	Random Text
2	Task 2	Not Started	Random Text
3	Task 3	Not Started	Random Text
4	Task 4	Archived	Random Text
5	Task 5	Not Started	Random Text

This formula should work:

=LET(DataTable, Table1,
     NotStarted, FILTER(DataTable,CHOOSECOLS(DataTable,3)="Not Started"),
     Archived, FILTER(DataTable, NOT(ISNUMBER(XMATCH(CHOOSECOLS(DataTable,1),CHOOSECOLS(NotStarted,1))))),
     SORT(VSTACK(NotStarted, Archived),1))  

• The first FILTER returns the "Not Started" rows.
• The second FILTER uses XMATCH to return which rows in the original table were returned in the first FILTER (this will return either #NA() or the position in the table of the found data). These rows are excluded.
• Finally the two tables are stacked vertically and sorted.

If you have values in Status other than just "Archived" or "Not Started" you should specify that it's returning "Archived" on the second filter:

=LET(DataTable, Table1,
     NotStarted, FILTER(DataTable,CHOOSECOLS(DataTable,3)="Not Started"),
     Archived, FILTER(DataTable, NOT(ISNUMBER(XMATCH(CHOOSECOLS(DataTable,1),CHOOSECOLS(NotStarted,1))))*(CHOOSECOLS(DataTable,3)="Archived")),
     SORT(VSTACK(NotStarted, Archived),1))

This formula works in case of more than two status values and select the "Archived" row. A2:D8 is the table data range.

=LET(firstcol,CHOOSECOLS(A2:D8,1),
status,CHOOSECOLS(A2:D8,3),
tab,BYROW(A2:D8,LAMBDA(x,TEXTJOIN("|",FALSE,IF((SUM(--(CHOOSECOLS(x,1)=firstcol))>1)*
(CHOOSECOLS(x,3)="Archived"),x,IF(SUM(--(CHOOSECOLS(x,1)=firstcol))=1,x,""))))),
TEXTSPLIT(TEXTJOIN("#",TRUE,tab),"|","#",TRUE))