Excel 2007 - Need help with a formula

[johnfn]

Hello,

I need some kind of formula that can do the following:

If the exact value of a cell in column C is found anywhere in column A, then the word "duplicate" needs to be added in column B next to that column A cell.

A summary of my problem is basically I have 800 cells of content in column C that is also within 2400 cells of column A. I need to remove the 800 cells of duplicates from column A.

Thanks in advance.

John

 

[VoG]

We really just need to see what your data looks like. Your sample does not need to include any duplicates.

 

[johnfn]

Ok, well here is an example of what I'd like to happen:

Excel Workbook
	A	B	C
1	content a	*	content k
2	content b	*	content h
3	content c	*	content m
4	content d	duplicate	content n
5	content e	*	content o
6	content f	*	content p
7	content g	*	content q
8	content h	duplicate	content r
9	content i	*	content d
10	content j	*	content s
Sheet

And here's an example of the data I'm working with:

Excel Workbook
	A	B	C
33	Aardvarks Dog Training Center is made up of both a training facility and day care specialty center. The goal of the facility is to train the dog in areas to promote great psychological health by playing with other dogs, and to reduce problems associated with barking too much, excessive chewing and manners. Your dog must be 16 weeks old or more and in great health for participation. The company is located in Vallejo, California, United States, but is known to serve the whole Solano County California, staff is on duty every day until 6:00pm. The company was voted BEST OF THE BAY AREA by San Francisco Magazine for its Sirius Puppy Training.	*	We offer dog training for your dogs and we are located in minnesota.
34	Aardvarks To Zebras Pet Care, located in Port Angeles, Washington, is a full service pet grooming company. *If you have a pet that you love, you want to keep your pet looking sharp. *Whether your pet just needs a good bath or brushing, nail trimming or flea dipping, or maybe a new "do", the place to call is Aardvarks To Zebras Pet Care!	*	Excellent service.Earn a Great Salary - Become a Pet Groomer, Nat'l Hands-on Trainingwww.ABCGroomingCollege.com
35	In our pet care we are glad to say that great provider of pet grooming service in Washington. our Location in post angles contact us at any time *(360) 452 1099.We are very caring of pets While the owner is out of town. Some pets require constant grooming and others only occasional grooming. You can use our website to locate a dog or cat grooming.We receive thousands of visitors everyday and your opinion can help another pet owner .	*	We offer veterinary services and also dog training for your beloved pets groves,Texas.
36	The owner of our ABC Dog training school is Greg Franklin. He has extensive experience and knowledge of canine behavior and training. He is state certified senior instructor. Our ABC dog training focuses on all aspects of dog behavior and learning, which will result in truly dynamic relationship that you both will enjoy.Our training module consists of following things ::1) Basic Obedience.2) Advanced obedience.3) Housebreaking.4) Agility.5) Protection. * * Our ABC dog training will assist with any specific behavior problems your dog may be experiencing. This training is available for any age and any breed.	*	We offer dog training in many specialized areas like obedience and behaviour etc.
37	Greg Franklin has extensive experience and knowledge of canine behavior and training.Franklin began his career in dog training in 1990. His first client was his own destructive Samoyed who was reformed and later won Best in Breed at a local show.Franklin's training expanded to assisting others with breaking their dogs of unwanted behavior and teaching basic obedience commands.In 1992, Franklin's focus turned to training dogs for law enforcement. He attended the police academy and police instructor school eventually becoming a Captain and State Certified Senior Instructor. He trained police dogs and their handlers at the Public Safety Training Institute / K-9 Training Division.	*	We offer world class dog training for your dogs in a good environment.
38	The philosophy at ABC Obedience is a blend of modern motivational techniques with some tried and true traditional methods. Owners are taught to recognize behavioral cues and reinforce appropriately. Dogs do not have to be handled harshly to earn respect! Emphasis is on using logic and communication to get results, not force. Training should be fun for owner and dog!	*	We fullfill all your needs and requirements with the best what we have.
39	ABC Pet Resort & Spa offers dog boarding, cat boarding, pet grooming, dog day care, and obedience training. *Our enclosures are all climate controlled, have antimicrobial partition panels to keep your pet safe, and have comfy, fleece bedding. Our gift shop sells only the best pet foods as well as breed specific gifts for you and your friends. Pet owners are encouraged to tour our facilities prior to boarding their pets and we are open from 7AM-7PM Monday through Friday for your convenience. We are also considering opening a members-only dog park here at ABC, assuming we get enough interest.	*	Categories (Edit)Dog Breeders, Pet Training, Dog TrainingLanguages SpokenAmerican SignProducts & Services (Edit)AffenpinschersAfghan HoundsAkitas
40	Hello and welcome to ABCK-9 & Equine, a place were our services for animals range from boarding to training, coaching and grooming. Trough our various services animals are exercised, socialized, and our training services are split into three different groups for a balanced experience. We are open from 6:30 AM to 9:00 PM, and we provide the right environment for your pets.	*	We offer dog training, dog daycare and boarding and all dog related services.
41	From dogs and cats, to birds and other more exotic pets, Aberdeen Acres Pet Care Center offers services to fill a variety of pet related needs. *We offer boarding, training and grooming for your pet, and we have a facility in Winchester, Virginia that we would love for you to see. *Our doors are always open for tours, so please bring your pet and see for yourself what we are talking about when we tell you that Aberdeen Acres Pet Care Center is the right place to make your pet a happy healthy one.	*	Address: * Just Dogs Playcare * 4100 Westcor Ct. * Coralville, IA 52241Phone: * * * * * * * 319-545-7111 * * *Email: * Archie@JustDogsPlayCare.com
42	Working and can't take care of your pet dog? Able K-9 Academy is the place to be. We specialize in dog day care as well as dog training. We can also take care of drop offs, daily, monthly or even weekly situations. We add the human touch with supervised exercise and play with their own dog door to go out whenever they choose. So call now!	*	Pinebrook Kennels is a full service pet care facility providing boarding, grooming and training.
Sheet1

 

[VoG]

Thank you. Am I correct in assuming that you want to find phrases in column C that are contained in paragraphs in column A.

If so I'm pretty sure that you will need VBA code rather than a formula.

 

[johnfn]

Well cells in column C will be exactly the same as in column A (so it won't be matching a phrase to a paragraph, it would be matching a paragraph with a paragraph).

I've never use VBA code, so I'm open to any options.

 

[VoG]

If you are looking for exact matches then any of the formulas given earlier should work. It is possible that you have extraneous spaces in column A and not C or vice versa.

Or do you mean that you want to find an exact sentence or paragraph in a cell in column C that also occurs somewhere in a cell in column A?

 

[johnfn]

I'm looking for an exact match from column C in column A, but maybe an exact match formula can't have as much content in each cell?

 

[VoG]

I'm looking for an exact match from column C in column A, but maybe an exact match formula can't have as much content in each cell?

There may be a limit but the formula certainly works with a sentence

Excel Workbook
	A	B	C
1	a		i
2	b		j
3	c		k
4			l
5	d		We offer dog training, dog daycare and boarding and all dog related services.
6	e		m
7	f		n
8	g		o
9	We offer dog training, dog daycare and boarding and all dog related services.	Duplicate	p
10	h		q
Sheet1

 

[jasonb75]

Well cells in column C will be exactly the same as in column A (so it won't be matching a phrase to a paragraph, it would be matching a paragraph with a paragraph).

I've never use VBA code, so I'm open to any options.

Try a couple of simple checks on a pair of known duplicate cells, changing A1 and C1 in each formula to the relevant cells.

=A1=C1

=LEN(A1)=LEN(C1)

If the cells are exact dupicates then both will return true, it may be helpful if you could post both cells as examples with excel jeanie.

 

[T. Valko]

There may be a limit but the formula certainly works with a sentence

Excel Workbook
	A	B	C
1	a		i
2	b		j
3	c		k
4			l
5	d		We offer dog training, dog daycare and boarding and all dog related services.
6	e		m
7	f		n
8	g		o
9	We offer dog training, dog daycare and boarding and all dog related services.	Duplicate	p
10	h		q
Sheet1

There is a limit, it's 255 characters.

Some of the OPs data entries are well over 255 characters long.

For example:

A1=

Aardvarks To Zebras Pet Care, located in Port Angeles, Washington, is a full service pet grooming company. *If you have a pet that you love, you want to keep your pet looking sharp. *Whether your pet just needs a good bath or brushing, nail trimming or flea dipping, or maybe a new "do", the place to call is Aardvarks To Zebras Pet Care!

LEN = 338 characters

A10 = xxx
A11 = zzz
A12 =

Aardvarks To Zebras Pet Care, located in Port Angeles, Washington, is a full service pet grooming company. *If you have a pet that you love, you want to keep your pet looking sharp. *Whether your pet just needs a good bath or brushing, nail trimming or flea dipping, or maybe a new "do", the place to call is Aardvarks To Zebras Pet Care!

=ISNUMBER(MATCH(A1,A10:A12,0)) = FALSE
=COUNTIF(A10:A12,A1) = #VALUE!
=A1=A12 = TRUE

Trim A1 and A12 down to 255 characters and:

=ISNUMBER(MATCH(A1,A10:A12,0)) = TRUE
=COUNTIF(A10:A12,A1) = 1
=A1=A12 = TRUE