tag:blogger.com,1999:blog-7958828565254404797.post5226610302629643849..comments2020-02-26T01:48:22.825-08:00Comments on ListenData: 3 Ways to extract unique values from a range in ExcelDeepanshu Bhallahttp://www.blogger.com/profile/09802839558125192674noreply@blogger.comBlogger86125tag:blogger.com,1999:blog-7958828565254404797.post-34559606579997957122019-12-29T13:11:24.265-08:002019-12-29T13:11:24.265-08:00Wonderful , very thanks.
The repeated data with co...Wonderful , very thanks.<br />The 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 :<br /><br />Sub repeating_data()<br /> Dim t As Object, sonsat As Long, liste(), j As Long<br /> sonsatir = Cells(Rows.Count, "A").End(xlUp).Row<br /> Range("F2:H" & Rows.Count).ClearContents<br /> liste = Range("A2:A" & sonsatir).Value<br /> Set t = CreateObject("scripting.dictionary")<br /> For j = 1 To UBound(liste)<br /> If Not t.exists(liste(j, 1)) Then<br /> t.Add liste(j, 1), 1<br /> Else<br /> t.Item(liste(j, 1)) = t.Item(liste(j, 1)) + 1<br /> End If<br /> Next j<br /> Application.ScreenUpdating = False<br /> Range("G2").Resize(t.Count, 2) = Application.Transpose(Array(t.keys, t.Items))<br /> Range("G2:H" & Rows.Count).Sort Range("H2"), xlDescending<br /> Call number_of_repetitions<br /> Application.ScreenUpdating = True<br /> End Sub<br /><br />Example file can be downloaded here : https://www.technologicaltipstools.online/2016/05/find-repeating-data-on-sheet-with.html<br />Kadr Leynhttps://www.blogger.com/profile/12302763575988103568noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-60743613516100725522018-10-04T09:40:19.489-07:002018-10-04T09:40:19.489-07:00I am trying get unique count for my excel dashboar...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 <br />column a has YTD months details<br />Column b has employees id<br />Column c has team name <br />Column d haa training name<br />Column e has pass/fail details<br />I need ytd unique count based on team name and employe id for each month qwit different training name.<br />Currently I am doing manually as taking pivot for each training based on unique criteria.<br />So please help to get auto capture this number dof excwl daahdash report. Unknownhttps://www.blogger.com/profile/01164917966649107618noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-90145851784847937732018-07-25T22:42:44.761-07:002018-07-25T22:42:44.761-07:00Hi - So I've been using this formula however I...Hi - So I've been using this formula however I need to adjust it slightly and unsure how. <br /><br />Is it possible to extract multiple unique values. <br /><br />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? <br /><br />this is the current formula I have: <br /><br />=IFERROR(INDEX($U$1:$JK$1,MATCH(TRUE,INDEX($U2:$JK2>0,0),0)),"")<br /><br /><br /><br />Unknownhttps://www.blogger.com/profile/04236356111251880489noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-75586958966047325272018-07-25T11:55:44.137-07:002018-07-25T11:55:44.137-07:00For some odd reason, on the first value in the ran...For some odd reason, on the first value in the range it gives me two allegedly equal entries (D2,D3). I cannot see the difference.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-67267067937749943272018-07-12T13:24:52.362-07:002018-07-12T13:24:52.362-07:00How do I figure out if 2 four digit numbers occur ...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.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-30246547043725853372018-04-19T09:07:28.816-07:002018-04-19T09:07:28.816-07:00i have a field name ph, and i want that all this d...i 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....<br />strsql = "select distinct[ph] from [data$] order by [ph]"<br /> closeRS<br /> OpenDB<br /> Combodob.Clear<br /> <br /> rs.Open strsql, cnn, adOpenKeyset, adLockOptimistic<br /> Do Until rs.EOF<br /> If IsEmpty(rs.Fields("ph").Value) = False Then<br /> Combodob.AddItem rs.Fields(0)<br /> End If<br /> rs.MoveNext<br /> Loop<br /> Exit SubAnonymoushttps://www.blogger.com/profile/14060808580134245956noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-44434057895449931552018-03-27T12:26:25.689-07:002018-03-27T12:26:25.689-07:00Hi ! i didn't understand how i can take data...Hi ! i didn't understand how i can take data from more colums. thank u.Anonymoushttps://www.blogger.com/profile/05191030186344983367noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-16274438037065367842018-01-22T21:33:09.109-08:002018-01-22T21:33:09.109-08:00Dear Deepanshu Bhalla,
if we wand to copy the same...Dear Deepanshu Bhalla,<br />if we wand to copy the same value, i mean duplicate value in Excel how can we do it.<br />for Ex:<br />I have 3 excel columns which have values like this,<br />Col_A Col_B Col_C<br />----- ----- -----<br />400 600<br />500 800<br />400 300<br />300 200<br />700 900<br />800 700<br />500 100<br />I want the values to be copied in Column C from Column B, which are Mache with Column A values.<br />I mean just copy the values from Column B, which are available in Column A. <br />Like below<br />Col_C<br />-----<br />800<br />300<br />700<br />Is there any excel formula using which I can achieve this?<br />Anonymoushttps://www.blogger.com/profile/12227639751269710682noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-77445683980774840162018-01-21T15:36:02.144-08:002018-01-21T15:36:02.144-08:00Hello,
I am trying to apply scenario 2 Index- Matc...Hello,<br />I 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).<br />Could you kindly help to get this right?<br />ThanksAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-52914145974961716062018-01-18T03:50:00.169-08:002018-01-18T03:50:00.169-08:00You are welcome. Glad it helped!You are welcome. Glad it helped!Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-42522627142329769032018-01-18T02:37:57.356-08:002018-01-18T02:37:57.356-08:00Thanks sooooooo much dear Bhalla, it work perfectl...Thanks sooooooo much dear Bhalla, it work perfectly you solve my big problem. this is you kindness. Anonymoushttps://www.blogger.com/profile/12227639751269710682noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-87180637166190053472018-01-18T01:36:00.644-08:002018-01-18T01:36:00.644-08:00Suppose colA and colB values are in range A2:B8 (e...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. <br /><br />In 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)),"")<br />and submit the formula with CTRL SHIFT ENTER and paste it down.Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-52283916736994377022018-01-17T23:30:32.074-08:002018-01-17T23:30:32.074-08:00I have 3 excel columns which have values like this...I have 3 excel columns which have values like this,<br />Col_A Col_B Col_C<br />----- ----- -----<br />400 600<br />500 800<br />400 300<br />300 200<br />700 900<br />800 700<br />500 100<br />I want the values to be copied in Column C from Column B, which are not Mache with Column A values.<br />I mean just copy the values from Column B, which are not available in Column A. <br />Like below<br />Col_C<br />-----<br />600<br />200<br />100<br />Is there any excel formula using which I can achieve this?<br />Anonymoushttps://www.blogger.com/profile/12227639751269710682noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-2377225068697870252018-01-04T11:13:26.869-08:002018-01-04T11:13:26.869-08:00Advanced filter works like charmAdvanced filter works like charmAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-16635467900136723472017-10-30T04:49:04.701-07:002017-10-30T04:49:04.701-07:00I need to use a macro, because I'm going to re...I need to use a macro, because I'm going to repeat this function everyday. <br />But 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?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-48809717571501251142017-08-14T17:35:03.671-07:002017-08-14T17:35:03.671-07:00Thank you for your help.
I have a question, under ...Thank you for your help.<br />I have a question, under the Scenario section above you stated;<br /><br />"To make it more clear, unique values are the values that appear in a column only once."<br /><br />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.<br /><br />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.<br />Any help would be much appreciated :)Unknownhttps://www.blogger.com/profile/07099308457114972168noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-84947033331393704672017-07-15T07:04:29.944-07:002017-07-15T07:04:29.944-07:00Hey,Thanks for your help...
I have a query on the...Hey,Thanks for your help...<br /> I 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 advanceGulshanhttps://www.blogger.com/profile/13887039736045458145noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-82958448862251782742017-06-01T18:16:50.255-07:002017-06-01T18:16:50.255-07:00There is another way to do it through Functions:
...There is another way to do it through Functions:<br /><br />Give the first cell "=MinValue(List)" and All the values below "=NextValue(List,)" and the list will come up automatically. Functions are below:<br /><br />Function MinValue(Text_Range) As Variant<br />Dim cell As Range<br />' Sets variable equal to maximum value in the input range.<br />MinValue = ""<br />' Loop to check each cell in the input range to see if the value is smaller.<br />For Each cell In Text_Range<br /> If cell.Value <> "" Then<br /> If MinValue = "" Or cell.Value < MinValue Then<br /> MinValue = cell.Value ' cell.Value<br /> End If<br /> End If<br />Next cell<br />End Function<br /><br />Function NextValue(Text_Range, Previous) As Variant<br />Dim cell As Range<br />' Sets variable equal to the highest value possible.<br />NextValue = "ZZZZZZZZ"<br />' Loop to check each cell in the input range to see if the value is smaller.<br />For Each cell In Text_Range<br /> If cell.Value > Previous And cell.Value < NextValue Then<br /> NextValue = cell.Value<br /> End If<br />Next cell<br />If NextValue = "ZZZZZZZZ" Then<br /> NextValue = CVErr(xlErrValue)<br />End If<br />End FunctionAnonymoushttps://www.blogger.com/profile/14558704394764874745noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-49779682437747837962017-05-30T04:45:55.995-07:002017-05-30T04:45:55.995-07:00Hi Deepanshu,
You code works wonderfully. Thanks ...Hi Deepanshu,<br /><br />You code works wonderfully. Thanks a lot. But is there a way you can filter this by a date range?Anonymoushttps://www.blogger.com/profile/17572874052087637402noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-79369615308614740152017-05-11T08:07:48.423-07:002017-05-11T08:07:48.423-07:00This is great and functioned well, but like a user...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. <br /><br />Thanks!smittie684https://www.blogger.com/profile/00794581248016527775noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-61460982430867980862017-04-27T08:02:25.570-07:002017-04-27T08:02:25.570-07:00hello,
i have another question about this xls part...hello,<br />i have another question about this xls part<br /><br />i have columns like<br /><br />value 2<br />value 1<br />value 2<br />value 3<br />value 3<br />value 1<br /><br />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<br />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 rowAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-84629116599772509402017-04-21T02:44:33.718-07:002017-04-21T02:44:33.718-07:00hi! i have about 9315 rows of airline market. ever...hi! 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.freahttps://www.blogger.com/profile/03834896031008913411noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-59984148180261606912017-03-28T10:58:56.714-07:002017-03-28T10:58:56.714-07:00Can anyone thing of a reason why it would duplicat...Can anyone thing of a reason why it would duplicate records even though they are identical?Anonymoushttps://www.blogger.com/profile/05361977816727476120noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-17448556402874454452017-03-06T01:26:58.661-08:002017-03-06T01:26:58.661-08:00Hi Deepanshu,
i encounter some problem when input...Hi Deepanshu,<br /><br />i 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.<br /><br />Sub CreateUniqueList()<br />Dim lastrow As Long<br /><br />lastrow = Cells(Rows.Count, "A").End(xlUp).Row<br /> <br /> Sheet("Data Vendor").Range("A2:A" & lastrow).AdvancedFilter _<br /> Action:=xlFilterCopy, _<br /> CopyToRange:=Sheet("Penyelesaian Pekerjaan").Range("P6") And Sheet("Sheet4").Range("A16"), _<br /> Unique:=True<br /> <br />End Sub<br /><br /><br />It said that "Sub or Function not defined". Can you help me what is the problem?<br /><br />Thank youAdhinoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-70526837548388089502017-03-05T23:54:26.718-08:002017-03-05T23:54:26.718-08:00Thank you for your help.
sorry that i didn't ...Thank you for your help.<br /><br />sorry that i didn't notice that you've already stated the answer at your post above.Adhinoreply@blogger.com