Offload unique values from source sheet - Data Shuttle

firestorm

hello all,

I would like to use Data Shuttle to offload the list of "submitted by" users and then upload it to another sheet to count the number of submissions per user.

Source sheet:

Metrics sheet:

How could I prevent duplicate names from being uploaded to the metrics sheet? I tried to use a helper column in the source sheet with this formula to set the unique name to 1 and the repeated name to 0, so that I could add a filter in Data Shuttle to skip rows with 0.

=IF(COUNTIF([Submitted By]$1:[Submitted By]@row, [Submitted By]@row) = 1, 1, IF(COUNTIF([Submitted By]$1:[Submitted By]@row, [Submitted By]@row) = 2, 0, 0))

However, I am unable to convert the helper column to column formula because of the "$" in the formula. In this case, I am uncertain if a new request (added in the top row) can take the formula since it is not a column formula.

Any suggestion?

Amit Wadhwani

I am curious why would you use Data Shuttle? Is it because the "Submitted By" list keeps growing? If you have a limited set of names in "Submitted By", you can try adding those names to a column in the metrics sheet and create cross sheet references. You can then use a simple COUNTIF formula to count the number of submissions against that person.

firestorm

Hello @Amit Wadhwani

Yes, I will not know who will submit a request, it could be anyone so there is no way for me to pre-populate a list, else things will be simpler.

Amit Wadhwani

Okay. And do you also use Pivot app or DataMesh?

firestorm

not yet explore the apps, how does it help?

Amit Wadhwani

Pivot app will let you do it quickly and easily. Datamesh requires some more configuration, but can be done there as well. Immediate solution could be a little indirect. In Sheet 1 of your Excel where the data shuttle offloads the data, all the rows will be there. You need Sheet 2 in Excel that will use UNIQUE formula to show only unique values form Sheet 1. Data Shuttle then uses Sheet 2 to import back to master sheet.

firestorm

hello @Amit Wadhwani

don't quite understand your solution, can you elaborate or provide examples?

Amit Wadhwani

Hi @firestorm

1. Create a Data Shuttle configuration which offloads the data from Source sheet to an Excel file (let's say Sheet1 of the Excel file).
2. In Sheet2 of the same Excel file, add a formula =UNIQUE(Sheet2!A:A) (assuming that the "submitted by" names are in column A of Sheet1. This will show only unique values form the submitted by column.
3. Create another Data Shuttle configuration which uploads the data from the Sheet 2 into the metrics sheet. This way it will only bring in unique values.

You just need to ensure that the Excel stays updated. Ideally it should upload in the background as when the Data Shuttle does its job.