Link an Automated Column to Numerous Sheets
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:
- De-duplicate a list that is constantly changing into another sheet?
- Get this running de-duplicated list into another spreadsheet that will count another criteria total?
Any help would be greatly appreciated!
Answers
-
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.
-
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!
-
I would suggest this for checking boxes based on names...
=IF(COUNTIFS(Names:Names, Names@row, Row:Row, @cell <= Row@row) > 1, 1)
-
That checked the boxes I needed - thank you!
-
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.
-
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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!