Adding Names from Sheet 1 to Sheet 2 with Counter update and handling duplicates

Halli9

Good afternoon,

I'm trying to achieve the following without success:

1. I have Sheet 1 that as forms are submitted a Names column is filled.
2. I have Sheet 2 with a Names and Counter columns

When a new submission occurs, I would like to get that name from Sheet 1 and add it to the Name column in Sheet 2 if it doesn't exist already and set the counter to 1. If the Name already exists in Sheet 2 I don't want a new row with that name but I would like to update the counter of the existing name.

This would be my Sheet 1:

And as an example, if "Steven" is added again in Sheet 1 the "Counter" in Sheet 2 for "Steven" would be updated to "2". The current Sheet 2 for the names above in Sheet 1 should be:

I tried using the VLOOKUP, IFERROR and MATCH functions and combinations of them, but I'm still struggling to make it work as intended.

Thank you for your help and any insight on this! It is greatly appreciated!

James Keuning

I did this with two sheets called Sheet1 and Sheet2.

Sheet1 has a column called NAMES, and it has your names in it.

In Sheet2:

Create a column called ROWID. This column should be an incrementing number. 1, 2, 3 etc

Create your NAME column and put a column formula with a cross-sheet reference:

=IFERROR(INDEX(DISTINCT({Name}), [Row ID]@row), "")

where {Name} is a cross-sheet reference pointing to the name column in Sheet1

Create a column called COUNT:

=COUNTIF({Name}, Name@row)

where {Name} is a cross-sheet reference pointing to the name column in Sheet1

That will get you want you want. Now, there are two more things to think about, and I can help with this.

First: your ROWID must increment by 1 and not skip any numbers. You can add a helper column to check that and send you an alert if something breaks.

Second: you need your ROWID column to continue into some empty rows or else names will stop being added to Sheet2. You can create a column formula that will alert you when you need to add more rows.

jason.smith2

Hello,

I believe what you would need first is a helper column in sheet 1. This could be a flag with the name of "In List", and formula as:

=IF(COUNTIF({SHEET 2}, [NAME]@ROW) > 0, 1, 0)

The output of this formula would leave the flag unchecked if the name does not appear on Sheet 2.

Then set an automation to trigger when a row is added.

The condition would be if the column "In List" is not checked, then the row would be copied to Sheet 2.

In Sheet 2, for the Counter column, you could use the below column formula

=COUNTIF({SHEET 1}, [NAME]@ROW)

This output would be a continuing count of the number of times a name appears in Sheet 1.

James Keuning

I did this with two sheets called Sheet1 and Sheet2.

Sheet1 has a column called NAMES, and it has your names in it.

In Sheet2:

Create a column called ROWID. This column should be an incrementing number. 1, 2, 3 etc

Create your NAME column and put a column formula with a cross-sheet reference:

=IFERROR(INDEX(DISTINCT({Name}), [Row ID]@row), "")

where {Name} is a cross-sheet reference pointing to the name column in Sheet1

Create a column called COUNT:

=COUNTIF({Name}, Name@row)

where {Name} is a cross-sheet reference pointing to the name column in Sheet1

That will get you want you want. Now, there are two more things to think about, and I can help with this.

First: your ROWID must increment by 1 and not skip any numbers. You can add a helper column to check that and send you an alert if something breaks.

Second: you need your ROWID column to continue into some empty rows or else names will stop being added to Sheet2. You can create a column formula that will alert you when you need to add more rows.

Halli9

Thank you @jason.smith2 and @James Keuning for your help!

I ended using both your suggestions for the counter column and James's suggestion for the Name column!

As I want to see this data in a dashboard, and want to have it dynamically updated as values are added in Sheet 1, I increased the number of rows to a point where I know it wouldn't surpass because of the nature of the data, and incremented the ROW ID numbers as well. I then used James suggestion of using a "Row ID Helper" column that flags when the Name column is filled, preventing the Counter column to getting updated (using an IF statement the current formula). This allows a that the dashboard gets updated dynamically as names are inputed in the first Sheet.

Thank you so much for your help, it was invaluable!