I have a master sheet with students and the locations in which they live. Some students live in the same location.
I need to make a sheet that shows the unduplicated list of locations where students live. I need this on a sheet so that I can then add more details about each location. The vision is that I'll have a list of all of the locations and then be able to add more information about each location.
I pulled the locations from the master sheet over to the new sheet using an index formula. The problem is now I have a giant list of duplicated locations, when I just need a clean list of unduplicated locations. My plan was to filter out the duplicates and make a report. I created a helper column to identify duplicates with a check mark (so I could filter out the checked boxes on my report).
The formula I'm using does not work. I need it to check mark the duplicates, but NOT the first instance of the location name. It's not working.
=IF(COUNTIFS([Building Name]:[Building Name], [Building Name]@row) > 1, IF(MATCH([Building Name]@row, [Building Name]:[Building Name], 0) = MATCH([Building Name]@row, [Building Name]:[Building Name]), 0, 1), 0)
I am also very open to other ideas if anyone has a way to simplify this. I want this to be automated because I don't want to have to manually type in new locations on a location sheet when we get new students.
