Skip to main content

How to extract Unique Cells

Explanation…
Formula used
=INDEX($C$2:$C$9,MATCH(0,COUNTIF($E$1:E1,$C$2:$C$9),0),1)
First of all, we will try to understand this part of the forumla..
ie.
COUNTIF($E$1:E1,$C$2:$C$9)
COUNTIF(Range,Criteria)
when entered as any array formula ..
Countif will count of each element to Criteria array (ie. AAA, BBB, CCC and so on) in Range array (is {0})
it returns an array of the same size or length as the Criteria Range
ie.
{0;0;0;0;0;0;0;0}
it is returning zeros as it has not found any match for each element of Criteria array in Range array
then Match function matches 0 in the lookup range and returns the row no. of the first match zero ie. 1
MATCH(0,{0;0;0;0;0;0;0;0},0) returns 1
after that Index Formula, come to action…
=INDEX($C$2:$C$9,1,1) returns "AAA"
Now drag it vertically to include other uniques..
when draged .. The sintex of the Countif formula changes a bit..
like this..
COUNTIF($E$1:E2,$C$2:$C$9)
ie.
COUNTIF({0;"AAA"},{"AAA";"BBB";"CCC";"AAA";"AAA";"BBB";"BBB";"CCC"})
Range Criteria
Now it will count each element of criteria array(ie. AAA, BBB, CCC and so on) in the range array(ie.0,AAA)
and returns following array..
{1;0;0;1;1;0;0;0}
Now Match function will take care of first Zero and return 2 as the row no. of first matched row
Index function will do the rest and return the cell at the intersection of 2 row and 1st column
like this..
=INDEX($C$2:$C$9,2,1) will retun "BBB"
In this way .. When draged further down.. Countif will return the counts of matched rows and
Match function will return the row no. of first zero ie. the row no. of next unique value.. And
Index Function will return the unique cell value…
Array formulas has to be entered by using CTRL + SHIFT + ENTER
Vikas Gautam

File can be downloaded here..
Unique values.xlsx

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom...
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more

Targeting Sendkeys to a Particular Window using Excel VBA

Hi Everyone, Sometimes while doing some VBA stuff, we want to target Sendkeys to a particular window which may or may not be active or present. Here is the code which will first check if the Target window is available by trying to activate it by AppActivate and then Sendkeys as desired. If Target window isn't available then you may wait until it shows up or show an error message. So Here are the code:- 1. For waiting until Windows shows up:- Sub Target_Sendkeys() On Error Resume Next 'Resuming next Statement on Error(5) Do Err.Clear 'Reseting the Err AppActivate "Blogger" 'Put the name of title bar of Target window here. DoEvents 'Avoidings hangs Loop While Err.Number = 5 SendKeys "%{ }r", True 'Restoring the window Application.Wait DateAdd("s", 1, Now) '...
1 comment
Read more