Reputation: 29

Getting IDs based on multiple string criteria

Following data structure:

ID   CRIT1    CRIT2
1    L        M
2    H        M
3    H        H

I would like to get the IDs with Crit1= H and Crit2=H. In a second sheet I want to build a table with the IDs which satisfy my criteria. So in this case only 3.

I tried with INDEX/MATCH and COUNTIF.

However, so far no useful solution based on strings and ranges.

Upvotes: 0

Answers (5)

Excel Hero

Reputation: 14764

Here is a formula solution That will create your table without any blanks.

This assumes your source data are in Sheet1. Edit as necessary.

=IFERROR(INDEX(Sheet1!A$2:A$9999,SMALL(IF((Sheet1!B$2:B$9999="H")*(Sheet1!C$2:C$9999="H"),ROW(Sheet1!A$2:A$9999),9E+99),ROW(1:1))-1),"")

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Now copy downard as far as needed.

Note: adjust the 9999s to a row number suitable to your situation.

Upvotes: 1

heyhey33

Reputation: 29

Regarding the formula solution I don't think it works.

It will give me all IDs with either crit1=h or crit2=h

Upvotes: 0

heyhey33

Reputation: 29

Ok so the pivot table does work yes, however I want to plot a graph which seems to be impossible with pivot data model

Upvotes: 0

Sergio Internicola

Reputation: 347

Without macros you can only copy the single cells if the criteria are satisfied, and don't copy them if not. Paste this formula in A2, then copy it to right (other columns), and to bottom (other rows):

=IF(AND(Sheet1!$B2="H";Sheet1!$C2="H");Sheet1!A2;"")

Upvotes: 0

pnuts

Reputation: 59450

I suggest a PivotTable without Totals or Subtotals, in Tabular Form, all three columns as ROWS and filtered to select H for each of CRIT1 and CRIT2:

Upvotes: 1

Getting IDs based on multiple string criteria

Answers (5)

Related Questions