Identifying cells with same values

D

droot

New Member
Joined
Oct 18, 2018
Messages
36
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a large spreadsheet with 2 columns A and B

Name1Apple
Name1Apple
Name2Apple
Name2Apple
Name2Peach
Name3Pear
Name3Orange
Name4Pear

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
[TD="width: 64"]Name1[/TD]
[TD="width: 64"]Apple[/TD]

</tbody>

I need a way to identify which person (Name) only likes one type of fruit, so in the above example i would like Name1 to be highlighted in some way.

I've spent the better half of the day trying to figure this out and have tried various methods like Match, count, ifs without any luck.

Can someone help me out please?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Re: need help with indentifying cells with same values

Welcome to the Board!

One way would be to use Excel's built-in "Remove Duplicates" functionality (considering both columns) to remove all duplicate entries.
Then you can just use COUNTIF to count how many times a name appears, and those that return 1 are the ones that you want.
 
Upvote 0
Re: need help with indentifying cells with same values

Thanks Joe.

I need all the infomation so the remove duplicates function won't work.

is there a way to count only names that do not have a unique entry in column B?
 
Upvote 0
Re: need help with indentifying cells with same values

droot said:
Thanks Joe.

I need all the infomation so the remove duplicates function won't work.

is there a way to count only names that do not have a unique entry in column B?
Click to expand...

Currently i've used a match formula on each column, from there i can manually put and identifier that i can use to filter the results i need. I have over 50k rows on my spreadsheet so i'd rather not do it this way.

[TABLE="width: 128"]
<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>[TABLE="width: 256"]
<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>[TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="width: 64"]Person[/TD]
[TD="width: 64"]Fruit[/TD]
[TD="width: 64"]Match Person[/TD]
[TD="width: 64"]Match Fruit[/TD]
[TD="width: 64"]Identify[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Apple[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Apple[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Apple[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Pear[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Peach[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Peach[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Peach[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Apple[/TD]
[TD="align: right"]59
[/TD]
[TD="align: right"]57
[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Apple[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]57
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
</tbody>[/TABLE]
</tbody>[/TABLE]
 
Upvote 0
Re: need help with indentifying cells with same values

count was probably the wrong word, sorry. I want some kind of label that i can filter to only show the results i want (on the example below those numbered 1)

I've tried to put and example on my previous reply it didnt work -
[TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="width: 64"]Person[/TD]
[TD="width: 64"]Fruit[/TD]
[TD="width: 64"]Match Person[/TD]
[TD="width: 64"]Match Fruit[/TD]
[TD="width: 64"]Identify[/TD]
[/TR]
[TR]
[TD]Name1
[/TD]
[TD]Apple
[/TD]
[TD="align: right"]57
[/TD]
[TD="align: right"]57
[/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Apple[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Apple[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Pear[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Peach[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name2[/TD]
[TD]Peach[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Peach[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Apple[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Apple[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: need help with indentifying cells with same values

No, "count" was correct. I see what you are trying to do.
You want to count how many unique "Fruits" there are for each "Name".
You can then filter that list to only show those records where that "count" is exactly one.

You should be able to use the formulas in the links I provided to get those counts (see the "Count Unique Values Based On Two Criteria" section in the "Extend Office" link I provided above).
 
Last edited:
Upvote 0
Re: need help with indentifying cells with same values

Joe4 said:
No, "count" was correct. I see what you are trying to do.
You want to count how many unique "Fruits" there are for each "Name".
You can then filter that list to only show those records where that "count" is exactly one.

You should be able to use the formulas in the links I provided to get those counts (see the "Count Unique Values Based On Two Criteria" section in the "Extend Office" link I provided above).
Click to expand...



I figured i could get rid of the 2 match formula and use the "count unique values based of one criteria" forumula but something went wrong -

=SUM(IF(C2=$C$2:$C$70774,1/COUNTIFS($C$2:$C$70774,C2,$E$2:$E$70774,$E$2:$E$70774)),0)

C= Name
E= Fruit

Can you see what i've done wrong?
 
Upvote 0
Re: need help with indentifying cells with same values

Are you enter the formula with a CTRL+SHIFT+ENTER, instead of just using enter?
This is an Array formula, and it is critical that the formula be entered in that manner.
 
Upvote 0
Re: need help with indentifying cells with same values

I've used ctrl+shift+enter and the formula shows inside the {} brackets. Also experiencing major bottlenecking and i haven't even copied it down
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Formula to Retrieve Value base on specific text.
Replies
4
Views
675
Fluff
Fluff
Z
  • Question Question
sumproduct or countA, but how :)
2
Replies
10
Views
1K
Sam_D_Ben
S
U
  • Question Question
VBA - Replacing cell values based on specific range
Replies
3
Views
983
pgc01
P
A
  • Question Question
Randomly mixing datas base on same columns..
Replies
4
Views
714
asyamonique
A
I
  • Question Question
Do while loop returns run time error 1004
Replies
3
Views
2K
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top