# How match duplicate entry

#### Pizzaman22

##### New Member
Hello, I was looking for help on how to match the following
A. B. C.
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!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### jasonb75

##### Well-known Member
Maybe this in D2 and filled down.
Excel Formula:
``=IF(COUNTIF(A\$2:A2,LEFT(A2,6)&"*",C\$2:C2,"Yes"),"Yes","No")``

#### Pizzaman22

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

#### jasonb75

##### Well-known Member
Oops, typo, it should be
Excel Formula:
``=IF(COUNTIFS(A\$2:A2,LEFT(A2,6)&"*",C\$2:C2,"Yes"),"Yes","No")``

#### Pizzaman22

##### New Member

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

##### New Member
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

##### Well-known Member

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

##### New Member
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

##### Well-known Member
Hi,

Do you mean this:

Book3.xlsx
ABC
1123john_street Spoon Yes
2123john_street. Fork. Yes
3123john_street Knife Yes
4255john_street. Apron. No
Sheet916
Cell Formulas
RangeFormula
C1:C4C1=IF(COUNTIFS(A\$1:A\$10,LEFT(A1,6)&"*",B\$1:B\$10,"<>")>1,"Yes","No")

#### Pizzaman22

##### New Member
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.
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”

Replies
2
Views
239
Replies
2
Views
236
Replies
0
Views
149
Replies
1
Views
96
Replies
0
Views
93

1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back