Determine if groups in one column meet a criteria in a second column ... I have

Question

Determine if groups in one column meet a criteria in a second column ...

I have a spreadsheet with 3 columns (id,group,driver). I'd like to determine if at least one item in the group column has the number 5 in the driver column.

My data looks like this (hopefully, it shows up correctly - I put the slash mark in, as a line/row delimiter):

id,group,driver
48426,V812,3/
48560,V812,2/
48563,V812,2/
48577,V812,3/
48597,V812,3/
48771,V812,5/
48772,V812,4/
48773,V812,3/
48775,V812,3/
48776,V812,4/
48986,V812,3/
49053,V812,4/
49565,V812,3/
49576,V812,3/
47358,V811,3/

I have approx 1300 rows; there are about 250 unique values in the group column FWIW.

The question I'm trying to answer: Is there at least one entry for group V812 that has a 5? Then an indication the group has at least one 5.

Then do the same for the remaining groups.

When I have the answer, then I want to sort the spreadsheet so that the groups are together w/o 5 and the remaining groups with a 5 are together.

My thoughts, that haven't worked - probably because of my limited knowledge:

  - Pivot tables, which worked to a certain extent, but I couldn't find a way to sort.
  - countifs: I've used this in other situations, but, on a line by line basis in survey data.

Thanks for any thoughts.

Explanation / Answer

1. In column D2, put following formula and drag down

=LEFT(C2,1)="5"

2. It will fill column D with TRUE and FALSE.

3. Select columns A to D, Home tab > Sort and Filter and Custom Sort on Column D (Largest to Smallest)

4. Now, all rows containing 5 will be appearing first in sorting. If you choose Smallest to Largest, first all rows not having 5 will appear.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.