I have voter data in an Excel spreadsheet with names, birthdates, addresses, etc. There are many addresses with two, three or more voters living in the same household.

Is there any way to pull those addresses into another list while eliminating the duplicates?

Make a copy/backup of your current file and try the link below. It worked for me in a quick sample test. It might depend on your version of excel though.

https://support.microsoft.com/...9b-bbe1-8daaec1e83c2

quote:

Originally posted by xantom:
Make a copy/backup of your current file and try the link below. It worked for me in a quick sample test. It might depend on your version of excel though.

https://support.microsoft.com/...9b-bbe1-8daaec1e83c2

This is what I was thinking, but as xantom said, make a copy of the sheet first, sometimes it acts weird. Sounds like you want a 'condensed copy' anyway.

I have a VBA macro that does something similar - checks R2C2 & if it is the same as R1C2, copies R2C3 into sheet2 R1C4, where sheet2 R1C3 = sheet1 R1C3 - Basically combining the 2 rows of data into 1.
This would get you a list with Voter1, Voter2, Voter3, address, etc. in the same row if they have the same address and leave blanks where Voter2+ doesn't exist.
if you email me an example of your spreadsheet, I think I can quickly modify the VBA to meet your needs. Scrub the data, leave the column names & let me know the maximum number of voters at the same address.

Some ding-dong decided that different 'events' in a database needed separate entries. So I have 2-12 rows for each entry in the excel download. each row has the exact same data, except for 2 columns that are much handier to use in formulas if they are in the same damn row. It also makes 1-years worth of data in the 10s of thousands of rows, which can easily be reduced by 10x. Ever run a for loop on a 40k-row spreadsheet? I think my laptop cried the first time, but it's gotten used to it.

I'm an Excel expert. There are many ways to skin a cat. What drives how you do it is what you intend to do with the results.

Here's the easy way I would do it assuming it's a straight database pull meaning the data is "normalized" or in a valid form, i.e. column headings.

First, I would make Excel transform the data into a table to make formulas easy.

Second, I would add a column to count how many instances of the street address appear in the table. "= COUNTIFS ( TableVoter[Street Address], [@Street Address])"

Third, I would add another column to flag the street addresses with multiple occupants. = IF ( COUNTIFS ( TableVoter[Street Address], [@Street Address]) > 1 , "Yes", "No")" I know you could have gone straight to this but I like to work in steps. I would actually reference the last column to see if it was greater than 1, i.e. "= If (previous column > 1, "Yes", "No")

Then you can just filter the table where the last column is "Yes." Or to make it quicker to manipulate: Make Excel make a pivot table, filter for the "Yes" then pull in the street address. If you want to know who are the people in that address, you can pull in the names next to the address. Then you can copy and paste the pivot tables to another list as needed.

You could have gone straight to the pivot point to get a list of unique addresses but you wouldn't know which households have multiple occupants unless you do create the columns I stated and pull that into the pivot table filter or pivot table view.

If you want, you can send me the file and it's no more than 15 minutes work for me assuming I make a French press brew. If you want to take me up on my offer, reply to this post and send me the file to my email. I ask that you reply to this post because that will flag me to look for your email. I check Sigforum more often than I check that email address.

2 words... Power Query.

Takes some knowledge, but it is the most powerful tool in all of Excel.

Rey... This may be easier and will allow this task to be performed over and over as new data comes in.

quote:

Originally posted by Rey HRH:
I'm an Excel expert. There are many ways to skin a cat. What drives how you do it is what you intend to do with the results.

I tend to think I'm fairly handy with Excel & am probably the top knowledgeable user in my company [outside of macros & VBA stuff]
Then these threads come along & the real experts give answers that tend to go over my head.

Rey's is the closest to what I'd be likely to do.

quote:

Originally posted by Rey HRH:
I'm an Excel expert.

I think I've opened an Excel file about 5 times in my entire life.

I did what Xantom said, and that appears to have worked. Took me from roughly 1,200 entries down to about 800.

I bet there are some other things that can be done that are so far over my head that a chinese spy balloon would block my view of it. Should we come up with more that we would like to do with this data, I would be more than happy to compensate you for your assistance.

quote:

Originally posted by a1abdj:

quote:

Originally posted by Rey HRH:
I'm an Excel expert.

I think I've opened an Excel file about 5 times in my entire life.

I did what Xantom said, and that appears to have worked. Took me from roughly 1,200 entries down to about 800.

I bet there are some other things that can be done that are so far over my head that a chinese spy balloon would block my view of it. Should we come up with more that we would like to do with this data, I would be more than happy to compensate you for your assistance.

If you want to compensate me, then feel free to pay for my next 12 months membership subscription to SigForum. Other than that, no, it will be my pleasure.

quote:

Originally posted by bozman:
2 words... Power Query.

Takes some knowledge, but it is the most powerful tool in all of Excel.

Rey... This may be easier and will allow this task to be performed over and over as new data comes in.

Power Query is a separate program that would need to be bought. Then you would need someone versed with database concepts and Power Query to create the process and maintain it. Besides, it sounds like the data pull he has is correct except it's just a flat file so no separate tables for addresses and people who live in those addresses.

If it was a task that was going to be repeated regularly, I would just create a native Excel VBA program to parse the data, most likely, reverse engineer the tables so that at a minimum, you would have a table for addresses, a table for occupants and their attributes such as age, sex, party affiliation, and lastly, a table connecting occupants to their addresses.