numbers
From this image you can see I've sorted the table by column G alphabetically, but what I would like to do is isolate the rows that only have one instance of the value in the G column. Most have multiple but as you can see with the pink dots some only have 1.
Is there a way to order by the amount of times a value occurs on a column?
Thanks
You need to use the $ char e.g. like this: =$D$3
Thanks to your sample file, I think I was able to do what you wished.
Using an additional column to bring in the numbers corresponding to the codes (16AA, 1; 16AE, 2...) sorting by that column then hiding it the desired result was accomplished. To refresh the sort after adding data, unhide the column and sort again.
Here are the steps:
Create new column to the left of the "Code" column.
Add a formula to extract the proper number for each code and copy down.
Formula: =INDEX (Group::c, MATCH (B, Group::A))
(Using INDEX(MATCH) is a good way to get column data with out worrying about extra columns being added as is the case with VLOOKUP.)
Result:
Best Answer
To isolate the rows that only have one instance of a value in the G column, use this method to simulate a pivot table. The columns of the table must have one row of headings.
IF(COUNTIF(G$1:G1,G2)=0,COUNTIF(G,G2),"")in the second row of the new column and autocopy down to fill the column.Here's an example. The new column is
Count.Select the
Countcolumn and filter for values equal to 1.