report repeated names

Scott Withington

Is there a function to allow me to put together a report that identifies names that appear on more than one row in a given column. In this case I am trying to bring out information on people who appear more than once in the name column, and then have the report show certain fields for those

Jeff K.

Go to Report Builder (first button at the bottom of the whole page), and Select the "Who" button

Bob Andrews

When you are in the Who section of the report builder, select the Contact list. When you select this option, you will see a list of users assigned to tasks with parenthesis and the number of the times the name exists in that column. Select the user and run the report to see/edit all the rows. 

Scott Withington

I am only seeing modified by, and created by.

 

When I click on either of those, I get the choice of field is blank, and my name as the only options.

 

What I am looking for is a report that would show only the vehicle accident info on drivers who had had more than 1 accident over the past couple years. (the table is vehicle accident info)

Bob Andrews

You will only see contact list columns in the Who section. If you dont see the column in that list, then you are not adding names to a contact list column. 

 

You could change the column in your sheet to a contact list column, then do what I suggested above.

 

Or you could take a different approach. How many drivers do you track? You could just see this information in the sheet. Create a row at the top of the sheet for each driver. Add their name in one column and a COUNTIF formula in another column. The COUNTIF can count the number of accidents each driver has been in. Like this:

 

https://app.smartsheet.com/b/publish?EQBCT=4b190fde94674d869cfde3dc85e12522

Scott Withington

I can see how to select names from the column to include, but is there a way to automatically select names that appear more than once?  manually selecting them is tedious and increases the risk of errors.

Bob Andrews

The report builder needs something to tell it to bring in a row. There are not IF THEN statements in the builder. You could probably build something in your sheet to be identified in the report.

 

For example, in your sheet add a checkbox column with a formula that looks if the corresponding driver name exists more than once in the sheet. If it does, check the box. Then run the report based on that column. 

Jeff K.

Or have the parent row be the Drivers, and there's a column that counts the number of Children within the parent (by using Children forumalas) and have all accidents as an indent under the Driver row

 

 

Scott Withington

is there a way to automate generating of parent child rows?  I.e as new accidents are added, a driver who already had accidents would be put in the existing parent row, and a new driver would be put in a new parent row

Scott Withington

Bob, do you have an example formula?

J. Craig Williams

Scott, 

 

I have not found a solution yet for this.

Zapier (one the third-party automation options) did not work.

I'm testing Azuqua but haven't gotten it to do this test case yet (it is only my list of things to implement)

 

Craig

Bob Andrews

Scott, here's an example of what I was suggesting. The formula is in the Checkbox column and will check if the corresponding Driver name exists more than once in the Driver column. Just run the report based on if the checkbox is checked.

 

https://app.smartsheet.com/b/publish?EQBCT=36a1988c0caf4e71be23577cf365839c

Scott Withington

Thank you

Jeff K.

Scott, while Bob Andrew's solution gives you what you want as the end result,

it will also mean that you will have to manually enter every new driver to the equation of the checkbox and hope that you didn't miss any

 

Probably better if you use the parent/child we suggested before, so all you have to do is add the equation to the parent. The only thing manual is to place the children under the correct parent row (and indent it to make it a child)