I can't find anything on this.
In the address list, some addresses have individual names, some have all Smith or Jones, and some have Smith, Jones, Garcia.
(See attached file)
How can I mark them as "individual", "Family", Household"?
Can this be done?
Try this formula in C4, copied down:
adjust ranges to suit.
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks for your reply!
That seems to be the right direction, but the formula returned some values that were not right, in C14 & C16. (see attached)
Is there a way to correct? Maybe by referring to the address field??
I am really lost on this one.
http://www.excelforum.com/images/smilies/confused.gif
I noticed that too in the original file, but I assumed you didn't have a complete list.Originally Posted by Curalice
Thanks for your reply!
That seems to be the right direction, but the formula returned some values that were not right, in C14 & C16. (see attached)
Is there a way to correct? Maybe by referring to the address field??
I am really lost on this one.
http://www.excelforum.com/images/smilies/confused.gif
What should those be and why?
I think I figured out your scheme.
Try
Does this work for you?Please Login or Register to view this content.
Many thanks.
Yes, this is a case where, at one address, there are several last names. So I want to send a 'household' label. Only AVEIGA = household as that is lined up with the address in the 'unique address' col D.
col b col c col d col e
AVIEGA household(yes!) 246 QUAIL RIDGE DR 246 QUAIL RIDGE DR
BALL family(wrong) 246 QUAIL RIDGE DR
BALL 246 QUAIL RIDGE DR
GONZALE household(wrong) 246 QUAIL RIDGE DR
Got any ideas?
Did you see my last post? I resubmitted another formula.
Is it still wrong?
I had to go to the office where the PC is a boat anchor, so I couldn't reply until now...sorry.
I keep getting different results with the formula
See attached file.
I changed the length of the column in the formula.
I appreciate your thinking on this. I am very puzzeled.
The upload before didn't attache. Here is the file with your formula.
Thanks
I have inserted my formula and adjusted to your ranges. See attached.
If any are incorrect, please highlight them and tell me why they are wrong and what they should be.
Many thanks.
I will examine your file in the morning.
I truly appraciate your help!
Thanks again for the updated formula. It does still return errors though.
See file.
Guess this one is really tricky.
I really apprecate your efforts. I am totally lost.
Merci
I think this should work now.
Please try and let me know:
Paste this formula in C6 and copy down:
see attached:Please Login or Register to view this content.
When I enter your formula and change it to include the whole list of records (6263 records), it doesn't work. Wrong values and skipped values. Also the formula gives different results when I copy it to different sheets. Here is the changed formula
...=IF(AND(COUNTIF($B$6:B6,B6)=1,COUNTIF($E$6:E6,E6)=1),IF(AND(COUNTIF($B$6:$B$6263,B6)=1,COUNTIF($E$6:$E$6263,E6)=1),"individual",IF(COUNTIF($B$6:$B$6263,B6)<>COUNTIF($E$6:$E$6263,E6),"household","family")),"")
I'm about ready to give up.
Please know I appreciate your efforts and patience! If you don't want to mess with it anymore, its ok.
Using your expanded range on the sheet I submitted before, I get the exact same results
I would have to see your actual worksheet to see what's going on. Make sure you don't have extra spaces in any entries and that spelling is consistent, etc... Matches should be exact (except case mixing; that can vary).
I still get errors when I put the formula into the sheet with all 6765 records.
I couldn't send the whole sheet to you because I couldn't zip it down to 100kb the size liimit for attachments.
It is 'show time' tomorrow, I will have to send labels out to all 'household' or random names.
Again, many heartfelt thanks for your efforts.http://www.excelforum.com/images/smilies/smile.gif
What kind of errors are they? Miscategorizing or Excel errors?
If you want you can send me your sheet (I'll PM you my email; check your PM) and look at it.
I don't really like to abandon these threads without resolution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks