Go Back   TeamTalk > Off Topic > Off Topic Discussion

Reply
 
Thread Tools Display Modes
  #1  
Old 01-26-2016, 03:39 PM
JohnE's Avatar
JohnE JohnE is offline
Moderator
 
Join Date: Jan 2006
Boat: XT22
Location: Boston 'burbs
Posts: 11,525
Excel help

Anyone good at excel out there? (I'm sure there are a lot of you)

I have a list of names sorted alphabetically by last then first and some names are on the list multiple times. Say John Smith is listed 3 times in a row. How can I filter that down to just one John Smith.

To complicate it, how can I sort it down if there are several John Smith's but they are 2 different people and are also identified by address?

I don't necessarily need to delete the multiple rows, jut populate another set of cells to generate a mailing list.

Hope this makes sense.

Thanks in advance.

John
__________________
Prior boats - (3) X14's, (3) Prostars, and a Tristar.
Reply With Quote
  #2  
Old 01-26-2016, 03:44 PM
kscrib's Avatar
kscrib kscrib is offline
MC Fanatic
 
Join Date: Jun 2010
Boat: 2015 X23 w/ 6.2
Location: Midwest
Posts: 704
I live in excel a LOT.

You can use "remove duplicates" on the Data tab. You will need to highlight the columns containing the data you want to analyze.
Reply With Quote
  #3  
Old 01-26-2016, 03:49 PM
kscrib's Avatar
kscrib kscrib is offline
MC Fanatic
 
Join Date: Jun 2010
Boat: 2015 X23 w/ 6.2
Location: Midwest
Posts: 704
Re-read your question. If your data is

Column A = First Name
Column B = Last Name
Column C = Address
Column D-ZZ = other info you need

then Select all columns A - ZZ
click the data tab on the ribon
click on remove duplicates
click on columns A, B and C in the window that pops up.


(If you delete more than you expect, you can use "<ctrl>-Z", to undo the delete)
Reply With Quote
  #4  
Old 01-26-2016, 04:25 PM
slalomjunkie slalomjunkie is offline
MC Devotee
 
Join Date: Apr 2015
Boat: NA
Location: West
Posts: 1,624
I personally do not like the ‘remove duplicates’ function in Excel.

Lets say this:


Cell A1: 1st name
Cell B1: Last name
Cell C1: Street Name

1. In cell D1 enter this: =A1+B1+C1
2. Copy for formula all the way down column D
3. Copy column D and “paste special” “Values” in Column E
4. Now use your “conditional formatting” the Home tab to highlight duplicates (in column E)


Hope that helps
Reply With Quote
  #5  
Old 01-26-2016, 04:30 PM
JohnE's Avatar
JohnE JohnE is offline
Moderator
 
Join Date: Jan 2006
Boat: XT22
Location: Boston 'burbs
Posts: 11,525
Thanks Ken. I follow you. What I have is a voter list for 3 years. I'm trying to identify everyone in 2 ways:

1. Everyone who voted one time in 3 years. Your answer will achieve this.

2. Everyone who voted in all 3 elections. So if a name appears 3 times (sorted alphabetically) Then I want to populate 3 new cells. Right now it is column A= Last name Column B = first name Column C = address.

So I want to do a function that says if A2=A1 and B2=B1 and C2=C1 and A3=A2 and B3=B2 and C3=C1 then D1=A1 E1=B1 F1=C1

I know = is for numbers and forumlas. I don't know if I can use EXACT or what I can use.

Thanks for the help.
__________________
Prior boats - (3) X14's, (3) Prostars, and a Tristar.
Reply With Quote
  #6  
Old 01-26-2016, 04:32 PM
slalomjunkie slalomjunkie is offline
MC Devotee
 
Join Date: Apr 2015
Boat: NA
Location: West
Posts: 1,624
have you tried a pivot table or a v-lookup?
Reply With Quote
  #7  
Old 01-26-2016, 04:42 PM
kscrib's Avatar
kscrib kscrib is offline
MC Fanatic
 
Join Date: Jun 2010
Boat: 2015 X23 w/ 6.2
Location: Midwest
Posts: 704
Sent you a PM.
Reply With Quote
  #8  
Old 01-26-2016, 05:29 PM
kscrib's Avatar
kscrib kscrib is offline
MC Fanatic
 
Join Date: Jun 2010
Boat: 2015 X23 w/ 6.2
Location: Midwest
Posts: 704
Not sure I understand your last post. Here is an if statement that you can put in D2 (assuming row 1 is a header)

=IF(AND(B2=B3, C2=C3, D2=D3), B2, "")

It will stick the last name in D2 if the last/first/address match two times.

But I don't think that is what you want, and that is where my confusion comes in. Do you only want to know who voted in all three elections? Or do you want to know the number of times a person voted (say over a 10 year period). If you want the number of times a person voted, then I agree with the pivot table idea slalomjunkie suggested. I would create a new column "D" that is =(A2 & ":" & B2 & ":" & C2). I used the colon as a divider between the fields (assuming no name or address has a colon), but you could use any divider you want. Then make a pivot table using column D and have that count the occurrences for you.

Last edited by kscrib; 01-26-2016 at 05:36 PM. Reason: fixed errors in formulas and formatting
Reply With Quote
  #9  
Old 01-26-2016, 07:02 PM
Forrest-X45's Avatar
Forrest-X45 Forrest-X45 is offline
MC Fanatic
 
Join Date: May 2009
Boat: X-45
Location: Northwest
Posts: 994
Quote:
Originally Posted by slalomjunkie View Post
have you tried a pivot table or a v-lookup?
Agree with slalomjunkie. Pivot table is going to be your friend.
__________________
2008 X-45 Pro Tour Red and White
Reply With Quote
  #10  
Old 01-26-2016, 08:11 PM
JimN's Avatar
JimN JimN is offline
MC Master Poster
 
Join Date: Jul 2004
Posts: 13,057
Would this help?

http://www.contextures.com/xlSort02.html
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 02:43 PM.