Seeking Excel (2010) help

delph

I'm hoping someone can provide some Excel (2010) assistance. I maintain a tracking log of files I receive for review, with a couple thousand entries already. Files have unique ID #'s and each time a file is submitted for review, it's added to the list, in the order received, to assist with prioritizing.

Once reviewed, I enter a "Y" or "N" in an "Approved" column for that log entry. Files not approved are to be re-submitted (using the same ID #) to me after corrections are made. A new log entry is made in my list for re-submissions. I would like an easy method to find the unique file ID #'s that have never been Approved.

Please move this topic if it better fits elsewhere, I was debating between this Tech Tavern and the Help/Advice forum. Thank you.

LD50

The easiest way that doesn't involve any coding is probably to use a pivot table.

First you will want to convert all you "Y's" into 1s and your "N's" into 0s, then create a new pivot table. You'll want your 'rows' to be your ID#s, and the values to be the sum of approved.

This should create a new table listing all of your ID#s once, followed by the number of times you have approved that log entry (where zero means you have never approved it). You should be able to sort the table by the number of approvals ascending, which would put all the unapproved entries at the top.

There may be a way to use a custom calculation (using COUNTIF) for the summary function in the pivot table, but I don't have a copy of excel on hand to test that (or a dataset to make sure it works). Actually, with some quick googling, it doesn't look like COUNTIF can be used in a calculated field, so you'll need to do the Y/N to number conversion. If you want to preserve your Ys and Ns you could create a new column that formulaically derives 1 or 0 from the Y or N in the approved column. That column could be hidden but still used in the pivot table.

This resource might help with pivot table creation if you haven't done it before.

EDIT: I can totally get it working in a google sheet using Ys and Ns, but I don't have a way to test if it works in excel.

delph

LD50 wrote: »

The easiest way that doesn't involve any coding is probably to use a pivot table.

First you will want to convert all you "Y's" into 1s and your "N's" into 0s, then create a new pivot table. You'll want your 'rows' to be your ID#s, and the values to be the sum of approved.

This should create a new table listing all of your ID#s once, followed by the number of times you have approved that log entry (where zero means you have never approved it). You should be able to sort the table by the number of approvals ascending, which would put all the unapproved entries at the top.

There may be a way to use a custom calculation (using COUNTIF) for the summary function in the pivot table, but I don't have a copy of excel on hand to test that (or a dataset to make sure it works). Actually, with some quick googling, it doesn't look like COUNTIF can be used in a calculated field, so you'll need to do the Y/N to number conversion. If you want to preserve your Ys and Ns you could create a new column that formulaically derives 1 or 0 from the Y or N in the approved column. That column could be hidden but still used in the pivot table.

This resource might help with pivot table creation if you haven't done it before.

EDIT: I can totally get it working in a google sheet using Ys and Ns, but I don't have a way to test if it works in excel.

Converting to 1's and 0's was just the trick! I went with the hidden column effect since I share the log with my supervisors. I was surprised by the number of results, about 150 of 3600+ haven't been returned to me. :x

Your response came at an excellent time too! I have a meeting tomorrow morning with my dept manager and his boss regarding this program, so we can discuss how to resolve this list along with whatever else they have planned.