How match duplicate entry

[Pizzaman22]

Hello, I was looking for help on how to match the following
A. B. C.
Address | House Items | Received item
123john_street | Spoon | Yes
123john_street. | Fork. | Yes
123john_street | Knife | Yes
255john_street. | Apron. | No

So, what I need it’s to do is
Only Exact match “123john” so the first (6 characters) and if already the exact “address” took any items from “house items” previously then provide a “yes” or “no” value in the “received item” Column.

Any help would be great! Thanks!

 

[jasonb75]

Maybe this in D2 and filled down.

=IF(COUNTIF(A$2:A2,LEFT(A2,6)&"*",C$2:C2,"Yes"),"Yes","No")

 

[Pizzaman22]

Hello, thanks for the fast reply.
Unfortunately that didn’t work.

 

[jasonb75]

Oops, typo, it should be

=IF(COUNTIFS(A$2:A2,LEFT(A2,6)&"*",C$2:C2,"Yes"),"Yes","No")

 

[Pizzaman22]

Hello, thanks again, no errors now.
But, it’s giving me a “No” on even though “house items” have been taking. So it’s can be anything in the “house items” list but it has to be the same “address” each time... like for example:
If “123john” took anything previously from the “house items” list it would be a yes” , if then 255john is added and never took anything previously it be a “no” ... I hope that clears things up

 

[Pizzaman22]

You know what, I only have 4 items they can take from the “house items” maybe I should just match the keyword? Is that better ? Like if “John123” took a “fork” before it say “yes”... etc...

 

[jasonb75]

It's based on your example, form which there is no clarity as to why the first row should be "Yes" and the fourth row should be "No".

Without clarity, any answer provided will be based on the interpretation of what we currently have to work with.

 

[Pizzaman22]

sorry for the confusion.

the forth row is No because “255john” never to an item before. I guess my mistake I put “apron” and that confused you. So it “255John” would have took a “fork” instead it will still be a No because his address never took any items from the list before... hope that helps.. sorry ....

 

[jtakw]

Hi,

Do you mean this:

Book3.xlsx
	A	B	C
1	123john_street	Spoon	Yes
2	123john_street.	Fork.	Yes
3	123john_street	Knife	Yes
4	255john_street.	Apron.	No
Sheet916

Cell Formulas
Range	Formula
C1:C4	C1	=IF(COUNTIFS(A$1:A$10,LEFT(A1,6)&"*",B$1:B$10,"<>")>1,"Yes","No")

 

[Pizzaman22]

Hello, thanks you guys very much for your help.
It still doesn’t work and it’s it’s my fault for messing up.. forget about the “apron” part. it would be based on this


A. B. C.
Address | House Items | Received item
123john_street | Spoon | Yes
123john_street. | Fork. | Yes
123john_street | Knife | Yes
255john_street. | Spoon| No
444john_street. | knife| No
255john_street. | Fork | Yes —-> (now a yes because it’s been recorded before they took something from the “house items” list even though it’s not the same item” anything from the “house list” will trigger this prompt to “yes”