Link an Automated Column to Numerous Sheets

Charlie Fraser

I have a master list that has multiple duplicates and I would like to de-duplicate this data and have it in another sheet. However, my master list is automated through a web forum so the information is updated automatically and I need it to translate to the other sheet if there is a new contact added.

How do I:

1. De-duplicate a list that is constantly changing into another sheet?
2. Get this running de-duplicated list into another spreadsheet that will count another criteria total?

Any help would be greatly appreciated!

Paul Newcome

I would suggest inserting 3 columns.

An auto-number column called "Auto" that doesn't need to have any special formatting done to it.

A text/number column called "Row" with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

And then a checkbox column that uses an IF/COUNTIFS formula to check boxes on duplicate rows. This formula is going to be determined by exactly what criteria needs to be met to have the row considered a duplicate.

From there you can pull a report for rows where the box is not checked to display the unique entries only and then to get your count you would reference the main data sheet and use a COUNTIFS with a range/criteria set included to skip over those rows that are checked.

Charlie Fraser

Hi Paul,

Thank you so much for your help.

If I am counting duplicates in the "Names" column, how do I create a formula that will check the box for me?

=IF(COUNT(DISTINCT(Names$1:Names@row)) <> COUNT(DISTINCT(Names$1:Names3)), COUNT(DISTINCT(Names$1:Names@row)))

This formula works for counting, but I would l like it to do the checkbox function.

Thank you!

Paul Newcome

I would suggest this for checking boxes based on names...

=IF(COUNTIFS(Names:Names, Names@row, Row:Row, @cell <= Row@row) > 1, 1)

Charlie Fraser

That checked the boxes I needed - thank you!

Charlie Fraser

Hi Paul,

I have a report of all the Names I needed thanks to the checkbox.

Can you not use formulas in reports? Trying to calculate the aggregate data for each unique names now that I have them de-duplicated.

Paul Newcome

No. You cannot use formulas in reports. There are a number of ways that you can copy the unique entries into another sheet and then run your formulas there.

How many total unique entries do you anticipate?