Identifying cells with same values

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
Name1Apple
Name2Apple
Name2Apple
Name2Peach
Name3Pear
Name3Orange
Name4Pear

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

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?

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.


<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>

<colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody>
PersonFruitMatch PersonMatch FruitIdentify
Name1Apple57570
Name1Apple57570
Name1Apple57570
Name1Pear57600
Name2Peach58611
Name2Peach58611
Name3Peach58611
Name3Apple59
57
1
Name3Apple5957
1

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>
</tbody>
</tbody>
 
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 -
PersonFruitMatch PersonMatch FruitIdentify
Name1
Apple
57
57
0
Name1Apple57570
Name1Apple57570
Name1Pear57600
Name2Peach58611
Name2Peach58611
Name3Peach58611
Name3Apple59601
Name3Apple59601

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>
 
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

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).



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

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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