How match duplicate entry

Pizzaman22

New Member
Joined
Apr 18, 2021
Messages
13
Office Version
  1. 365
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!
 

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
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 18, 2021
Messages
13
Office Version
  1. 365
Hello, thanks for the fast reply.
Unfortunately that didn’t work.
54DAFC18-CEB7-44B3-B36E-5BFCF9E9E75A.jpeg
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
Oops, typo, it should be
Excel Formula:
=IF(COUNTIFS(A$2:A2,LEFT(A2,6)&"*",C$2:C2,"Yes"),"Yes","No")
 

Pizzaman22

New Member
Joined
Apr 18, 2021
Messages
13
Office Version
  1. 365

ADVERTISEMENT

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
Joined
Apr 18, 2021
Messages
13
Office Version
  1. 365
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
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 18, 2021
Messages
13
Office Version
  1. 365
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2021
Messages
13
Office Version
  1. 365
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”
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top