There is two function that is missing in excel 2013, which is Unique and Filter.
In this picture below here, the Resulting Item utilizes a "Unique" function from google, and Resulting Label utilizes a join and filter function =JOIN(", ",FILTER(C:C,A:A=E2)).
How do i perform this in excel 2013 for this two cells?
delete-duplicate-values-scheme.png
Your profile says 2003 and your post 2013 - which is correct, please?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
From the documentation I have seen, UNIQUE() and FILTER() or currently only available to certain "office insiders" who have the correct O365 subscription. From the current help file for the FILTER() function (https://support.office.com/en-us/art...c-4877ad80c759 ):These functions are never going to be available to Excel 2013. At this time, I believe Google Sheets is the only readily available spreadsheet that makes these two functions generally accessible to all users.Note: September 24, 2018: The FILTER function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.
Originally Posted by shg
So, is there anyway i can make it work? possibly some macro / vba scripting?
That is correct. I have them in my insider 365 subscription. However, that's not why I asked about the OP's Excel version - that was to do with his/her forum profile possibly being out-of-date.
The OP is, I believe, asking for an Excel workaround to do these things.
sorry, yes. I was using 2003, but now i have 2013. Let me update it
This is not the kind of programming I do. What do you need this to do exactly?
If I were to do something like this (it would be a one time or very rare thing for me), I would probably just use either of Excel's built in filtering tools for this. Autofilter or advanced filter would allow me to filter the list to get the desired records, then copy them into my destination. Can you tolerate a manual operation, or must it be automated?
If I were to try to automate something like this, I might start with the sample code in the Range object help file: https://docs.microsoft.com/en-us/off....Range(object) This code uses the Range.AdvancedFilter method to extract the unique values from a list. If you will provide a suitable criteria range, I'm sure the code could be modified to automate filtering by more criteria than just unique records. Would something like that satisfy your need for automation?
This thread seems similar
http://www.excelforum.com/excel-gene...e-entires.html
see Jindon's solution for example (of course here as a normal macro not a function, but that might be better or worse)
Sub test() Dim a, i As Long, x, n As Long With Range("a1").CurrentRegion a = .Value With CreateObject("Scripting.Dictionary") .CompareMode = 1 For i = 1 To UBound(a, 1) If Not .exists(a(i, 1)) Then .Item(a(i, 1)) = a(i, 2) Else .Item(a(i, 1)) = _ Join$(Array(.Item(a(i, 1)), a(i, 2)), ", ") End If Next x = Application.Transpose(Array(.keys, .items)) n = .Count End With With .Offset(, .Columns.Count + 1).Resize(n, 2) .CurrentRegion.Clear .Value = x .Columns.AutoFit .CurrentRegion.Borders.Weight = 2 End With End With End Sub
If you want something done right... find a forum and ask an online expert.
Time flies like an arrow. Fruit flies like a banana.
UDF
Use in cell like
=FilterUniq(Sheet1!$A$1:$C$7,1,ROW(A1),COLUMN(A1),2,3)
then copy right/down
Where;
Sheet1!$A$1:$D$7 is a source range incl headings
1 is a unique key col
2 is a column for Sum
3 is a column to be joined
You can use Array when any of above 3 arguments consists of multiple columns
e.g
=FilterUniq(Sheet1!$A$1:$D$7,1,ROW(A1),COLUMN(A1),{2,4},3)
ROW(A1),COLUMN(A1) MUST not be changed in the first cell, so that it increments as you copy.
Function FilterUniq(ByVal rng As Range, ByVal keyCols, ByVal rowRef, _ ByVal colRef, Optional sumCols, Optional JoinCols) Dim a, e, i As Long, ii As Long, txt Static dic As Object If dic Is Nothing Then Set dic = CreateObject("Scripting.Dictionary") dic.RemoveAll a = rng.Value For i = 1 To UBound(a, 1) If IsArray(keyCols) Then For Each e In keyCols txt = txt & Chr(2) & a(i, e) Next Else txt = a(i, keyCols) End If If Not dic.exists(txt) Then dic(txt) = dic.Count + 1 For ii = 1 To UBound(a, 2) a(dic.Count, ii) = a(i, ii) Next Else If Not IsMissing(sumCols) Then If IsArray(sumCols) Then For Each e In sumCols a(dic(txt), e) = a(dic(txt), e) + a(i, e) Next Else a(dic(txt), sumCols) = a(dic(txt), sumCols) + a(i, sumCols) End If End If If Not IsMissing(JoinCols) Then If IsArray(JoinCols) Then For Each e In JoinCols If a(i, e) <> "" Then a(dic(txt), e) = a(dic(txt), e) & _ IIf(a(dic(txt), e) <> "", ", ", "") & a(i, e) End If Next Else If a(i, JoinCols) <> "" Then a(dic(txt), JoinCols) = a(dic(txt), JoinCols) & _ IIf(a(dic(txt), JoinCols) <> "", ", ", "") & a(i, JoinCols) End If End If End If End If txt = "" Next If rowRef <= dic.Count Then FilterUniq = a(rowRef, colRef) Else FilterUniq = "" End If End Function
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
AmazingTrans 
Register To Reply
Bookmarks