This tutorial describes multiple ways to extract unique or distinct values from a column in Excel.
Suppose you have a list of customer names that contains some duplicate values. You wish to extract the unique values from it. In Excel, data starts from cell B3 and ends with cell B15. Header of the list is in cell B2. See the snapshot of actual data in image below.
Download the Excel file below for reference. It will be used to demonstrate methods to find unique values from a column.
1. Advanced Filter
Follow the steps shown in the animation below- Go to Data tab in the menu
- In Sort and Filter box, Click Advanced button
- Choose "Copy to another location"
- In "List range :" box, select a range from which unique values need to be extracted (including header)
- In "Copy to :" box, select a range in which final output to be put
- Check Unique records only
- Click Ok
2. Remove Duplicates Option
The most easiest way to extract unique values from a range is to use "Remove Duplicates" option. See the snapshot below -
Warning : If you want to keep your original data without replacing unique values, make a copy of it by pasting the data into another location. Otherwise, the original data will be deleted.
- Select the Range of cells from which you want to get unique values.
- Go to the Data Tab in the ribbon at the top of Excel.
- Click on "Remove Duplicates" under the "Data Tools" group.
- Select the column that contains duplicates and Click Ok. If you have multiple columns in a range, make sure only the column that contains duplicates is selected.
Note: If you want to remove duplicates based on all the columns (whole row), make sure all the columns are selected.
3. INDEX-MATCH (Array Formula)
=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")
Copy the above formula and paste it into cell D3. Hit CTRL+ SHIFT + ENTER to confirm this formula as it's an array formula. If done correctly, Excel will automatically place curly braces {...} around the formula. After placing curly braces, the above formula would look like this :
{=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")}
To paste it down till the cell D12, select the range D3:D12 and press Ctrl+D shortcut key to fill down.
Suppose there are missing or blank values in your list from which you want to extract unique values. In this case, you need to tweak your formula. The modified formula is explained below -
=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")
Copy the above formula and paste it into cell D3. You need to press CTRL+ SHIFT + ENTER to submit this formula. To paste it down till the cell D12, you can select the range D3:D12 and use Shortcut key : Ctrl+D.
See the steps below how the function extracts second distinct value.
Step 1: Use COUNTIF($D$2:D3, $B$3:$B$15)
returns 1 against the first distinct value (Jhonson) in range $B$3:$B$15. For example COUNTIF($D$2:D3, $B$3:$B$15)
becomes ={1;1;1;0;0;0;0;0;0;0;0;0;0}.
Step 2: Check the position of 0 using the MATCH function. It returns 4 for the second distinct value.
Step 3: Extracts the fourth name from the original list (Column B) using the INDEX function. This returns Dave.
Tutorial : Excel Array Formula with Examples4. MACRO (Advanced Filter)
It's an excel macro to find distinct values from a column in Excel. In this method, we are using the same logic as we have done in first method i.e. Advanced filter. Here, we are applying advanced filter via excel macro rather than doing it manually.
Option Explicit Sub CreateUniqueList() Dim lastrow As Long lastrow = Cells(Rows.Count, "B").End(xlUp).Row ActiveSheet.Range("B2:B" & lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=ActiveSheet.Range("D2"), _ Unique:=True End Sub
- Go to excel sheet where data exists.
- Press Alt + F11 to open Visual Basic Editor.
- Go to Insert menu >> Module. It will create a module.
- In the module, copy and paste the above VBA code into the window
- Close the VB Editor Window
- Go back to your sheet
- Press Alt + F8. Select CreateUniqueList under Macro name box and Hit Run button.
The following are two most frequently asked questions about above excel macro with solutions. If you have any other question regarding the macro, post your question on comment box below.
Q. How to paste unique values to another existing worksheet?Change ActiveSheet.Range("D2") to Sheets("newsheet").Range("D2")In the above code, change "newsheet" to the name of the existing sheet wherein you want to paste unique values. Q. How to paste unique values in a new worksheet? Use the program below. It will paste distinct values to a new worksheet named "mysheet". You can change it to any name you want -
Option Explicit Sub CreateUniqueList() Dim lastrow As Long Dim ws As String ws = ActiveSheet.Name lastrow = Cells(Rows.Count, "B").End(xlUp).Row Sheets.Add.Name = "mysheet" Sheets(ws).Range("B2:B" & lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Sheets("mysheet").Range("D2"), _ UNIQUE:=True End Sub
Thanks a lot
ReplyDeleteIt works only for 1 column not for the 2d range
ReplyDeleteAny way to extend this over multiple columns?
DeleteConcatenate it and then run the concatenated column.
DeleteYou are a legend. Wish you all the best and keep up the good work.
ReplyDeleteExcellent.
ReplyDeleteNicely laid out; especially liked the multiple options to end results. However what I'm looking for is a formula that will seek out the first instance in a column with duplicate values and keep its row place at the same time. In all of your examples, the query column shows a condensed list that does not match across the rows the very first unique instance of that specific value.
ReplyDeleteA B
1 1
1
2 2
2
1
1
3 3
3
2
1
3
Use this formula...
Delete=IF(COUNTIF(A$2:A2,A2)=1,A2,"")
many many thanks dear...I was looking for this.
DeleteHey - it keeps returning "0" value rather than the text in the cells i want. Not sure what i am doing wrong.
ReplyDelete=IFERROR(INDEX('Pocketbook Data'!$C$12:$C$31,MATCH(0,COUNTIF('Chart of Accounts'!$B$4:B17,'Pocketbook Data'!$C$12:$C$31),0)),"")
Since it's an array formula, press CTRL SHIFT ENTER to confirm it rather than only ENTER.If done correctly, Excel will automatically place curly braces {...} around the formula.
DeleteI have the curly brackets and I pressed ctrl+d, but all I have are blank values.
DeleteUpload your file to cloud drive and share the link with me. Thanks!
DeleteI had the same issue Matt.
DeleteCheck if you have a circular reference on the bottom left status bar.
I needed to adjust the countif formula to reference the cell above the current cell that you are entering the formula into. This threw me originally as my data started on the first row of the sheet and Deepanshu's example starts on the second row.
i.e. My first formula was in cell n2 and the example formula references d2 because the first line is blank.
Further confusing is that when you evaluate the formula using formula auditing it looks to work right up to the last step when it switches from the correct value to zero.
HTH
Michael
Also thanks Deepanshu for taking the time to publish this information.
Hey there,
DeleteStumbled on this question.. I know it's 6 years later but I had the same issue. The problem was that I was referencing the the cell I was pasting the formula in and not the one below it.
Example:
=IFERROR(INDEX(Sheet2!$T$3:$T$29, MATCH(0,IF(ISBLANK(Sheet2!$T$3:$T$29),1,COUNTIF($W$2:W2, Sheet2!$T$3:$T$29)), 0)),"")
This gave me a return of 0 while it was an array when it was pasted in the W2 cell, but once I moved the formula to W3 and set it to array, the issue was gone. Hope this helps someone that runs into this problem in the future.
[TL:DR] You need to place this formula one cell below the point you reference. Notice his list and formula start in D3 but the formula references D2
The =IFERROR(INDEX($F$13:$F$1353, MATCH(0,COUNTIF($X$12:X12, $F$13:$F$1353), 0)),"") perfectly worked for me except that I use a filter for the F column. When I deselect any item from the filtered column I would like to have it removed from the unique items list created.
ReplyDeleteIs that possible somehow?
Thanks in advance!
Dim d As Object, c As Variant, i As Long, lr As Long
ReplyDeleteSet d = CreateObject("Scripting.Dictionary")
Sheets("Plan1").Select
lr = Cells(Rows.Count, 1).End(xlUp).Row
c = Range("A1:A300" & lr)
For i = 1 To UBound(c, 1)
d(c(i, 1)) = 1
Next i
Sheets("Plan2").Select
Range("A1").Resize(d.Count) = Application.Transpose(d.keys)
This formula has helped me tremendously! However, is there a way to make the formula only return a value of a cell if the length of the value is 8 for example?
ReplyDeleteI have two spreadsheets, one has a column of data which contains varying lengths of text. On the other spreadsheet I am trying to populate a column with only the values that are a length of 8.
My goal is to NOT have to manipulate the data on the "source" spreadsheet as there will be others using this workbook and I just want to keep it as a simple copy/paste method for importing the raw data.
Any help would be appreciated.
=IFERROR(INDEX(PNRExport!$F$1:$F$800,MATCH(0,IF(ISBLANK(PNRExport!$F$1:$F$800),1,COUNTIF($F$5:F24,PNRExport!$F$1:$F$800)),0)),"")
I used your advice before and find this website extremely helpful, thank you.
ReplyDeleteNow I have a situation where a list has blanks and duplicates, very similar to your example “Version 2: IF BLANK VALUES IN A LIST” but here’s the catch . . . for this effort, I have a requirement to keep the duplicates for reporting purposes but remove the blanks. I might be over thinking this.
Here is the formula I’ve used but of course it removes duplicates which I need to keep:
{=IFERROR(INDEX(WIP!$A$3:$A$501, MATCH(0,IF(ISBLANK(WIP!$A$3:$A$501),1,COUNTIF($A$2:A55, WIP!$A$3:$A$501)), 0)),"")}
Do you have a simple way of achieving this?
Thank you.
ReplyDeleteHow can I do this from multiple list ranges from different worksheets, to get a list of unique model numbers, ranges being for example Sheet2!A2:A40, Sheet3!D2:D40, Sheet4!C5:C43, Sheet5!B2:B40. Thanks in advance!
ReplyDeleteMeant to clarify, it should always update Sheet1 column A with the unique list
ReplyDeleteHaving issues with this query it keeps returning 0 instead of the text. IF(ISERROR(INDEX(Invoiced!$B$1:$C$3101,SMALL(IF((Invoiced!$B$1:$B$3101=$A$5)*(COUNTIF($D$6:D6,Invoiced!$M$1:$M$3101)=0),ROW(Invoiced!$B$1:$B$3101)),ROW(1:1)),2)),"",INDEX(Invoiced!$B$1:$C$3101,SMALL(IF(Invoiced!$B$1:$B$3101=$A$5,ROW(Invoiced!$B$1:$B$3101)),ROW(1:1))*1,2))
ReplyDeleteThanks for providing these tips!
ReplyDeleteI'm trying do this across 3 columns, but there are no results. If I restrict the range to just one column than it works fine, but nothing for 3 columns.
Does this not work for multiple columns, and if not, is there any other alternative that does?
thanks!
Thank you very much! Your website has been SO helpful in my work! This formula is great, however sometimes I need Excel to extract the unique items in alphabetical order. For example, using your illustration above, is there a formula that would return the unique items in the order:
ReplyDeleteDave
Deepanshu
Jhonson
Neha
Sohan
...and not in the order that they occur in the column. I have a similar need with extracting values in ranked order, but have an existing workaround.
Any advice you could provide would be much appreciated!
Thanks again.
Another most simple way - button Data/Remove Duplicates.
ReplyDeleteAnimation for the first solution display it.
The vba gives me the first and second as duplicate...
ReplyDeleteAnd the first cell in the list has a name now Its called "Extract"
Any one has an idea why and how to fix?
super great
ReplyDeleteFantastic Trick to get a dynamic list. I'm a bit confused on how to extend the list beyond 15 rows. I've unsuccessfully changed all values from 15 to 40 rows, but it failed.
ReplyDeleteIf you are using advanced filter to extract unique values, you need to give reference of your updated range manually in the 'List Range' box
DeleteI'd like to use INDEX+MATCH '=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")
DeleteChanging references from $B$15 to $b$30 does not pick up addl rows beyond row 15
Thank you for your quick response and for pointing out my omission to you CTRL SHIFT ENTER - all is well now
DeleteGlad to know all is well now. Cheers!
DeleteHi there,
ReplyDeleteIt works for me. But honestly I didn't get that formula. I would appreciate if someone helps me to understand.
Here is my understanding:
Index($E$2:$E$1000, Match(0,countif($A$3:A3,$E$2:$E$1000),0))
---To be specific this is the part I didn't understand---
countif($A$3:A3,$E$2:$E$1000) --- This function returns 0 or 1 if the value exist in $E$2:$E$1000 range.
Then comes
Match(0,countif($A$3:A3,$E$2:$E$1000),0) --- which is generating this value Match(0, 0 or 1, 0) How come Match function doesn't have a range in the middle. In fact its taking countif array as range. Could anyone explain this to me?
Trying to generate column C from data in A and B. Need to take first all same type in "A" and list first and last occurrence of same type in "a". Data is sorted by "A" then by "B".
ReplyDeleteA B c
1 Blue 24"x36" 24"x36' to 42"x66"
2 Blue 24"x8' 24"x36' to 42"x66"
3 Blue 42"x66" 24"x36' to 42"x66"
4 Red 24"x8' 24"x8' to 7'6"x9'6"
5 Red 7'6"x9'6" 24"x8' to 7'6"x9'6"
Sorry, removed my spacing...
Delete| Col A| Col B | Col C
1 | Blue | 24"x36" |24"x36" TO 42"x66"
2 | Blue | 24"x8' |24"x36' to 42"x66"
3 | Blue | 42"x66" |24"x36' to 42"x66"
4 | Red |24"x8' |24"x8' to 7'6"x9'6"
5 | Red |7'6"x9'6"|24"x8' to 7'6"x9'6"
Suppose values are placed in range A2:B6. Place the following formula in cell C2 and paste it down till C6.
Delete=INDEX($B$2:$B$6,MATCH(A2,$A$2:$A$6,0))&"to" &INDEX($B$2:$B$6,MATCH(A2,$A$2:$A$6))
thaks it worked
ReplyDeleteHow can I extract unique values to a different sheet altogether?
ReplyDeleteThere's a challenge after applying the above formulas. A window pops up which is titled Update Values :"File Name"
Any help in this regard will be greatly appreciated. Thanking u.
Huzefa.
I was racking my brain to come up with a clean solution for this - and your posting was most helpful. Thank you for saving me several hours of frustration! Really appreciated!
ReplyDeleteIn the duplicates list find out unique values stored in array. using vba program......
ReplyDeleteHi Dipanshu ,
ReplyDeleteThanks a lot for posting this - just one thing option 2 gives circular reference error
The formula works fine. Save your file to cloud drive and share link with me.
DeleteGood tutorial but VBA code doesnt work if you have formulas in selected columns which echo text in cells with IFs. The same is with other methods. This tricks work only if you have simple text in every cell or digits result if you use formulas in cells
ReplyDeleteSorry it works... my mistake... missing (,) in my code ;)
ReplyDeleteThanks for this thorough explanation. For some reason I am getting the same entry (the first) repeated over the entire column. Any help would be much appreciated!
ReplyDeleteThank you for writing to me. Save your file on cloud drive and share link with me.
DeleteI have a quick question perhaps you may help me. I have tried the solution using aggregate function but since my workbook is huge it is taking forever to load, when I use index and match it is super fast but I am failing to find a workaround of ignoring the zero values.
ReplyDeleteSample data is as follows
Date Acc# Join Check
01/Jan/2017 5014 42,277 Correct
1-Jan-2017 1047 - Not this
5-Jan-2017 1047 - Not this
15-Jan-2017 1047 42,308 But this
This is a student dataset in a school, so students have to pay a small registration fee which some pay once like Acc 5014 and some pay in installments as Acc 1047. The thing is the join date starts when the reg fee is fully paid hence the zeros on 1 and 5 jan 2017.
Thanks
Hello Deepanshu,
ReplyDeleteThe INDEX-MATCH example that you created was brilliant. Is CountIf() the only function that would have output that array for the match function? I'm having trouble understanding why the CountIf() function is even outputting an array at all. Sorry if my question is dumb, I've only recently begun using the Index function instead of Vlookup.
Thank you,
John
sad
ReplyDeleteall your answer is related to data in row,
ReplyDeleteif we want unique value from column then? i tried in most of the site , but no luck
pls help
INDEX- MATCH array formula works on extracting unique values that are stored on multiple columns. Try it once!
DeleteIn the formula explanation section, you wrote as:
ReplyDelete" for the second distinct record Dave, ..... CountIf($D$2:D3, $B$3:$B$15). It is searching values D2 and D3 in the range B3:B15. The array becomes ={1;1;1;0;0;0;0;0;0;0;0;0;0}. "
I think the above wording is wrong and isn't what you meant to write. I think, you meant: It is searching B3:B15 in the range D2:D3. Because Countif(range,Criteria) specifies the range as first argument and criteria as 2nd.
And then again the result comes (should come) ={1;0;1;1;0;0.....} and not what you wrote ={1;1;1;0;0;0;0;0;0;0;0;0;0}.
Pls check.
Click on the cell D4 and Go to Formulas tab >> Evaluate Formula. You can see how each part of the formula is executed.
DeleteHi Deepanshu,
ReplyDeleteThank you for the solution. I have a question for the macro part. can i make the list shown in another sheet?
thank you.
Yes, you can can. Change ActiveSheet.Range("D2") to Sheets("Sheet2").Range("D2"). In this case, you are pasting unique values to Sheet2. Hope it helps!
DeleteThank you for your help.
Deletesorry that i didn't notice that you've already stated the answer at your post above.
Hi Deepanshu,
ReplyDeletei encounter some problem when inputing the macro. I want to use it to update two table in different sheet, below is the code i use based on your code.
Sub CreateUniqueList()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Sheet("Data Vendor").Range("A2:A" & lastrow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet("Penyelesaian Pekerjaan").Range("P6") And Sheet("Sheet4").Range("A16"), _
Unique:=True
End Sub
It said that "Sub or Function not defined". Can you help me what is the problem?
Thank you
Can anyone thing of a reason why it would duplicate records even though they are identical?
ReplyDeletehi! i have about 9315 rows of airline market. every month i need to update each market on which airline has been added. i was given a previous file to match with the present file. What I usually do was to create a new tab and copy all present & past datas in one tab then remove the duplicates. so if i have 9310 from past and 9345 in present i will be having a total 18655 rows in all and if remove the duplicates it will give me again about 9230. here's my problem... can you help me find a way to remove all the duplicate values and retain the unique ones? so i would know which market was added and at the same time which was removed? Your great help will be so much appreciated. Thank you very much.
ReplyDeletehello,
ReplyDeletei have another question about this xls part
i have columns like
value 2
value 1
value 2
value 3
value 3
value 1
I want to count the number of values in a row so the 1 must be counted as 1 and the 3 must counted as 2 in upper scenario
because my list is of 1000 of different values text and numbers i want to know how many time the same value comes in a row
This is great and functioned well, but like a user before me (I didn't see an answer), is there a way to return the values in order? I have a set of numbers, and would like it to pull numerically.
ReplyDeleteThanks!
Hi Deepanshu,
ReplyDeleteYou code works wonderfully. Thanks a lot. But is there a way you can filter this by a date range?
There is another way to do it through Functions:
ReplyDeleteGive the first cell "=MinValue(List)" and All the values below "=NextValue(List,)" and the list will come up automatically. Functions are below:
Function MinValue(Text_Range) As Variant
Dim cell As Range
' Sets variable equal to maximum value in the input range.
MinValue = ""
' Loop to check each cell in the input range to see if the value is smaller.
For Each cell In Text_Range
If cell.Value <> "" Then
If MinValue = "" Or cell.Value < MinValue Then
MinValue = cell.Value ' cell.Value
End If
End If
Next cell
End Function
Function NextValue(Text_Range, Previous) As Variant
Dim cell As Range
' Sets variable equal to the highest value possible.
NextValue = "ZZZZZZZZ"
' Loop to check each cell in the input range to see if the value is smaller.
For Each cell In Text_Range
If cell.Value > Previous And cell.Value < NextValue Then
NextValue = cell.Value
End If
Next cell
If NextValue = "ZZZZZZZZ" Then
NextValue = CVErr(xlErrValue)
End If
End Function
Hey,Thanks for your help...
ReplyDeleteI have a query on the same..I have been trying some expansion to this formula but no luck. I need to use this formula with couple of conditions, Such as date range from Jan to Mar or Apr to Jun or Jan to May, something like that..Appreciate your help.. thanks in advance
Thank you for your help.
ReplyDeleteI have a question, under the Scenario section above you stated;
"To make it more clear, unique values are the values that appear in a column only once."
however when I run the Index-match listed above, my list is showing items that are not unique, meaning they appear more than once in the column that I'm trying to sort.
Is there a different function or formula (other than checking the little box, "unique records only") that will sort items that appear only once in the column? I do not want to remove duplicates, that is not the same thing, I just need to sort and list items that appear once in a column, preferably without using macros.
Any help would be much appreciated :)
I need to use a macro, because I'm going to repeat this function everyday.
ReplyDeleteBut i have more columns that I want to remove duplicates from (the same you can do when you click on remove duplicates in the Data). How can I change the macro to be able to do so?
Advanced filter works like charm
ReplyDeleteI have 3 excel columns which have values like this,
ReplyDeleteCol_A Col_B Col_C
----- ----- -----
400 600
500 800
400 300
300 200
700 900
800 700
500 100
I want the values to be copied in Column C from Column B, which are not Mache with Column A values.
I mean just copy the values from Column B, which are not available in Column A.
Like below
Col_C
-----
600
200
100
Is there any excel formula using which I can achieve this?
Suppose colA and colB values are in range A2:B8 (excluding header). In cell C2, you can write =IF(ISERROR(VLOOKUP(B2,$A$2:$A$8,1,0)),B2,"") and paste it down till C8.
DeleteIn cell D2, you can enter =IFERROR(INDEX($C$2:$C$8, MATCH(0,IF(ISBLANK($C$2:$C$8),1,COUNTIF($D$1:D1, $C$2:$C$8)), 0)),"")
and submit the formula with CTRL SHIFT ENTER and paste it down.
Thanks sooooooo much dear Bhalla, it work perfectly you solve my big problem. this is you kindness.
ReplyDeleteYou are welcome. Glad it helped!
DeleteHello,
ReplyDeleteI am trying to apply scenario 2 Index- Match Array Formula but I only get 0 value after I enter the formula as per the demo (my list is composed by letters text only).
Could you kindly help to get this right?
Thanks
Dear Deepanshu Bhalla,
ReplyDeleteif we wand to copy the same value, i mean duplicate value in Excel how can we do it.
for Ex:
I have 3 excel columns which have values like this,
Col_A Col_B Col_C
----- ----- -----
400 600
500 800
400 300
300 200
700 900
800 700
500 100
I want the values to be copied in Column C from Column B, which are Mache with Column A values.
I mean just copy the values from Column B, which are available in Column A.
Like below
Col_C
-----
800
300
700
Is there any excel formula using which I can achieve this?
Hi ! i didn't understand how i can take data from more colums. thank u.
ReplyDeletei have a field name ph, and i want that all this distinct type may update in my combobox, but some field is blank and in number type field. but it shows error, help me... here is my code....
ReplyDeletestrsql = "select distinct[ph] from [data$] order by [ph]"
closeRS
OpenDB
Combodob.Clear
rs.Open strsql, cnn, adOpenKeyset, adLockOptimistic
Do Until rs.EOF
If IsEmpty(rs.Fields("ph").Value) = False Then
Combodob.AddItem rs.Fields(0)
End If
rs.MoveNext
Loop
Exit Sub
How do I figure out if 2 four digit numbers occur more than once in multiple variations. Meaning if i have column A & column B in excel and i put '1234' in column A & '5647' in Column B how do I figure out if they occur more than once, IN ADDITION to if those numbers switch columns how can i find out if any variation of the 2 four digit number string occurs? Thank you.
ReplyDeleteFor some odd reason, on the first value in the range it gives me two allegedly equal entries (D2,D3). I cannot see the difference.
ReplyDeleteHi - So I've been using this formula however I need to adjust it slightly and unsure how.
ReplyDeleteIs it possible to extract multiple unique values.
So for example, I have about 150 columns with unique headers (features i recorded). However, some artefacts had more than one of these features recorded for them and this formula is only showing the first column with data in it and not the potentially subsequent column headers?
this is the current formula I have:
=IFERROR(INDEX($U$1:$JK$1,MATCH(TRUE,INDEX($U2:$JK2>0,0),0)),"")
I am trying get unique count for my excel dashboard report. As above example i tried arrays formula but the excel 2013 got hanging due to data has around 2 lakh rows. Requirements is
ReplyDeletecolumn a has YTD months details
Column b has employees id
Column c has team name
Column d haa training name
Column e has pass/fail details
I need ytd unique count based on team name and employe id for each month qwit different training name.
Currently I am doing manually as taking pivot for each training based on unique criteria.
So please help to get auto capture this number dof excwl daahdash report.
Wonderful , very thanks.
ReplyDeleteThe repeated data with count of repetitions can be found with Excel Vba.The repeated data in "Column A" are listed into "Column G" and "Column H" with the count of repetitions :
Sub repeating_data()
Dim t As Object, sonsat As Long, liste(), j As Long
sonsatir = Cells(Rows.Count, "A").End(xlUp).Row
Range("F2:H" & Rows.Count).ClearContents
liste = Range("A2:A" & sonsatir).Value
Set t = CreateObject("scripting.dictionary")
For j = 1 To UBound(liste)
If Not t.exists(liste(j, 1)) Then
t.Add liste(j, 1), 1
Else
t.Item(liste(j, 1)) = t.Item(liste(j, 1)) + 1
End If
Next j
Application.ScreenUpdating = False
Range("G2").Resize(t.Count, 2) = Application.Transpose(Array(t.keys, t.Items))
Range("G2:H" & Rows.Count).Sort Range("H2"), xlDescending
Call number_of_repetitions
Application.ScreenUpdating = True
End Sub
Example file can be downloaded here : https://www.technologicaltipstools.online/2016/05/find-repeating-data-on-sheet-with.html
Hello, I tried the VBA code but there is always a duplicate value for the first one so it returns
ReplyDelete1
1
2
3
4
5
6
Is there any way to solve it such that it will not be duplicated for the first value?
I have the data as follows:
ReplyDeleteColA , ColB, ColC , ColD
100 100 200
200 250 200
250 300 300
125 100 125
ColD should have the non duplicate values from ColA, Colb, ColC,
result should look like:
ColD
200
250
250
100
Pls help
Hi,
ReplyDeleteIn a column A i have many duplicate or repeated entries i want only the first entry to be counted as 1 in column B and the rest duplicate entries as 0. can u please help me.
Example
Column A Column B
12345 1
12345 0
23456 1
23456 0
12345 0
23456 0
Thanks a lot man!
ReplyDeleteVery clear, precise and thorough.
Why is web page https://www.listendata.com/2013/05/excel-3-ways-to-extract-unique-values.html title "3 Ways to extract unique values from a range in Excel" when web page states "4 Methods to Extract Unique Values"?
ReplyDeleteExcel 365 added the =unique() function which is probably the quickest way now.
ReplyDeleteApologies, I realise this is an old thread but I'm looking for some advice.
ReplyDeleteI have a list of records. I need a way to extract unique records from them but some of the columns are missing information. For example...
Doherty Lorraine 245989 2
Doherty Lorraine 27/12/1982 2
Doherty Marlene 276149 3
Doherty Marlene 15/08/1975 276149 2
Doherty Marlene 15/08/1975 2
Doherty Patricia 15/03/1984 2
Doherty Sarah 02/01/1981 1273861 2
Doherty Sarah 1273861 2
Doherty Sarah 245968 2
Doherty Sarah 02/01/1981 2
Doherty Shannon 23/10/1991 2
Doherty Sheena 232444 2
Doherty Vanessa 27/01/1983 294862 2
Doherty Vanessa 294862 2
Doherty Vanessa 27/01/1983 2
I have a last name, first name, DOB, ID and number of duplicate records.
I need to extract unique records but keep the maximum number of duplicate records. The problem is that some of the columns are missing a DOB or an ID but are the same person and in the list above, Sarah Doherty is listed 4 times but it is two people because there are two unique ID's.
I have tried using unique over the whole table but because of the missing information it is failing to find all the records.
Any ideas would be gratefully received.
Thanks