A quick video? Even a short video takes a while to produce. Rank what? text or numbers? Numbers is possible without formula insanity, text or mixed data is insane with a formula... My book, Ctrl Shift Enter, shows all three methods, but it is complex. There just is not an easy way to create a unique sorted list.
I still do do an occasional video in response to a comment, except I get about 100 request s day... I will do one for your request, just because I think there is a good new way to do it with AGGREGATE ... Late nest week. here is what it might look like: If formula in E5: =AGGREGATE(14,6,Values/(FREQUENCY(Values,Values)>0),ROWS(E$5:E6))
Try this video: Excel Magic Trick 496: Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting youtube [dot] com/watch?v=J05oqUcsiRM
Yes, this is way far into the series. I have a great series to get you up to speed (53 videos from zero to advanced): youtube [dot] com/playlist?list=PL706DCCD130378289
Ok, sorry for not being too clear. I meant ranking numbers (i.e. sales). One sales number per row, with tons of rows. The rank & countif combo I use is super heavy on my model and when I heard you mention that countif can slow calculations up by thousands of times I thought "yes" ! And since then I have been trying all sorts of things, in vain... which is why I am asking the expert for a bit of insight... I remember back in the day you used to do short videos in response to a given question.
please dont say sorry, im sorry, i have already learned a lot from your videos, in fact i learn more here than what i learn at my college, i dont know how to thank you! i came to UK to learn, hopefuly one day i go back to help my country, very poor country! you have excellent knowledge of Execl, so you could put all your knowledge and create very useful project. thnak you dear friend.
just amazing Mike what you do here! there is still good people live in this life! just like you! i wrote you 3 days, but you didnt understand what i meant, and that is because English is not my first language. Ok dear, could you create project, for ex, employee attendance, and then we will learn alot from the project, and we can use it in real life. god bless you and your family.
Could you envision making a quick video to show the different, and especially, most efficient ways to rank tens (hundreds) of thousands of rows without duplicates ? I have tried many ways but can't seem to figure out the right way to go. Tons of thanks in advance Mike !
Thanks for your great video. You mention that there is a formula that can sort extract record A to Z so that the dynamic drop down list will automatically sort as well.could you show it? Thanks again for your great video
Hey!! You mentioned not to use the IRERROR and use IF instead to shut the formula after certain rows, is there a specific reason for that? I understand IFERROR is slowe, but never actually understood why?
Mike, where in MrExcel forum is Dominic's discussion about the sorting solution? It seems this issue of Unique Items Only has been around for decades. At the forum, we're really getting into it now! I've rallied for this functionality to be an option in Data Validation Lists. www.mrexcel.com/forum/excel-questions/942043-data-validation-list.html#post4525625
How should I change the formulas in order to extract and count names that are listed in two separate columns (names of workers in the night shift and morning shift) they are duplicates mostly. I tried to change the range but it’s not working…
Hi, your tutorial is very helpful indeed. I would like to ask how to do it it columns. e.g. in your "unique items" table, instead of it is by row, Is it possible in a column/s? (Chin Sioux Gigi etc etc)
I liked your video and found the formula to be helpful, but in cases where we wanted a quick rough sketch of how many names and the list of unique names in a long list of names, you could also create a "dummy" pivot table off your data. In this case have "customer" as the row labels and quickly highlight them all to count number of cells.
Just to be clear, what I was referring to was the most efficient way to replace the classic : =RANK(A2,$A$2:$A$50000,0)+COUNTIF(B2:B$2,B2)-1 On a side note, I tested your aggregate formula out & it seems to me like it is a ranked extraction of the values, which I will also definitely make good use of...as of today, I was using a sumifs based on the aforementioned descending ranking. Thx again Mike !
I tried to do one of these independently at work the other day, by writing a formula that returned the array of unique numbers with no helper cells. The formula worked fine, but you couldn't use it directly in a data validation list either as a formula or assigned to a named range. Data Validation just wouldn't accept it.
Edit: Fixed, _Control_...*Shift*...*_Enter_*...DUH! Hey Mike, you've been my saving grace for a while now! I finally have a question I cannot locate in your tutorials. I want to perform this function based on a series of vlookup values. So here's a sample formula, sans error management, that runs through say A2:A1750, =VLOOKUP(*$A2*,'Some Page'!$A:$F,6:FALSE) so, I drag this formula all the way down, and pull my values over. I have: John, Suzy, John, John, Suzy, Bill, Bill, etc., but the formula says "1" unique value - I only assume it's validating the formula, not the values. I know I have 16 values. I'm using this as a template so when data changes I don't have to constantly adjust the data validation. Any advice here?
6:01 as soon as I hit ctrl+shift+ enter excel became unresponsive. Note that I have 20k records.I think the space complexity is way too high for this formula. Any faster solution?
Excellent, love this dynamic DV list. Have used the countif before and for large data set it does seem to drag. Will replace with freq formula and see the difference. Thanks Mike.
You're awesome Mike ! By the way, just to let you know that you among other factors also inspired me to pursue some statistics studies starting next Oct or so ! I found a good university, but out of curiousity, should you have any nice recommendations for distance learning - I would gladly look into it/them, as I am in switzerland.. Great wkend !
Thanks for this video; I found it extremely informative. Quick question - I am trying to adapt the method to information grouped horizontally rather than vertically. I adjusted the cell references and replaced ROW and ROWS with COLUMN and COLUMNS. The Unique Count formula works fine, but Return Unique Items is not working correctly. What other adjustments are needed to adapt this to columns rather than rows?
Is this not easier to you to do in VBA? I'm coming from a background in a language that has a simple unique() formula, and the necessity of all this is tough to accept. I learn from almost all of your videos, but I don't see how I ever could have created this on my own.
HEY SIR, CAN YOU HELP ME TO SORT LIST OS NAMES USING THE SECOND AND THIRD CHARECTER OF THE WORDS. I'VE USED =MID(A1,3,100) BUT IT DELETES THE FIRST TWO CHARECTERS AND DISPLAYS ONLY THE REST OF THE LETTERS. IS THERE ANY OTHER WAY BY WHICH WE CAN GET COMPLETE LIST
based on this video I created a dynamic drop list conditional to 3 IFs statements. how can I show the first value of my drop down list in a cell conditionally formatted to have the values of my drop down list? Thanks for your troubles . . .
+MrSarky1992 For large data sets, the COUNTIF takes a long time to calculate. I test the different formulas in the book and time to verify that FREQUENCY is much faster than COUNTIF. I hope that helps.
Hi Mike, I have a dynamic data base table (supplier, product code, description & cost), to which I add new records all the time and I use this dynamic table to creat a DV supplier list and again another DV list to extract all the products and their costs which relates to the particular supplier. I would like to be able to see the records sorted either numerically OR by letters. At the moment when you open the DV drop down the list the records come up as entered on the dynamic table BUT not sorted. Is there a solution for sorting the records? (Either on the dynamic table or at the DV side of things by way of Formula or maybe advance filter) and what if BOTH numbers & letters needs sorting? (As the dynamic table records includes both numbers & letters). I'm happy to email the workbook (heavy in size) should you wish to have a look otherwise I'de appreciate a short answer or any answer. I'de like to add that I couldn't find your book in Australia and would appreciate your advise how I can buy it outside the USA. Last but not least I think you have done (still do...) a great job, I've learned "nearly" all I know in excel from your videos and recommend any one reading this post/comment to follow your videos should they wish to extend their knowledge in excel. Keep up the good work and perhaps let us foreigners know where and how can we get your book & DVD in Australia. Thx in advance and apologies for the long comment.
What about a making conditional validation lists which depends on each other and the source is two columns where: Column 1 contain the item types (sorted, but not unique elements) and Column 2 contain the items itself (sorted, but not unique). Here is small example: type 1, item 1 type 1, item 2 type 1, item 3 type 2, item A type 2, item B So each line contain a full record. And there are no spaces (empty rows). For each column the drop down list has to be created dynamically so user can select first the item type and then item itself. Could you please make a video with the solution for this problem (It has to be for Excel 2003)? I tried to follow this video, but I got lost after a few minutes. :( Update: Managed to do this using VB macro (1'st to create unique list, 2'nd to automatically create a names). :) Bust still it is interesting how to handle such thing using only formulas.
Would you be willing to share your solution? I'm currently trying to achieve the same setup you've described but I simply cannot for the life of me figure out how to set it up.
Persona Non Grata I have the separated sheet called "Lists" where I have the two columns with my data (Column 3 = "Task", Column 4 = "Type"). And there are two macros which I have assigned it to the two buttons: "Create names", "Create unique list". The input does not change often so this solution works for me. The macros are following: Sub create_names() ' Specify the sheet name and the location of the input data sSName = "Lists" iStartRow = 2 iCol1 = 3 iCol2 = 4 '------------------------------ iRow = iStartRow bCheck = True iRangeStart = iRow Worksheets(sSName).Activate ' Process the rows Do With Worksheets(sSName) ' get the 1'st and 2'nd column cell data and 1'st from next row (for comparison) sCol1 = .Cells(iRow, iCol1) sCol2 = .Cells(iRow, iCol2) sCol1Next = .Cells(iRow + 1, iCol1) ' Mark exit if there is no more rows to process If IsEmpty(sCol1Next) Then bCheck = False End If ' if the data is changing in the first column, then create a name If sCol1 sCol1Next Then sRange = "R" & iRangeStart & "C" & iCol2 & ":R" & iRow & "C" & iCol2 sString = "=" & sSName & "!" & sRange sName = "__" & normalize_name(.Cells(iRow, iCol1)) Worksheets(sSName).Range(.Cells(iRangeStart, iCol2), .Cells(iRow, iCol2)).Select ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:=sString ' update RangeStart iRangeStart = iRow + 1 End If End With ' increment row iRow = iRow + 1 Loop Until bCheck = False End Sub Private Function normalize_name(ByVal sText As String) As String sText = Replace(sText, " ", "_") sText = Replace(sText, "(", "") sText = Replace(sText, ")", "") sText = Replace(sText, "/", "") sText = Replace(sText, "-", "") sText = Replace(sText, "__", "_") normalize_name = sText End Function And then there is the second macro: Sub create_unique_list() ' the list has to be sorted first ' Specify the sheet name and the location of the input data sSName = "Lists" iRowHeader = 1 iRowStart = 2 iColSrc = 3 iColDst = 5 iRow = iRowStart bCheck = True iRowCnt = iRow Worksheets(sSName).Activate ' Process the rows Do With Worksheets(sSName) sRow1 = .Cells(iRow, iColSrc) sRow2 = .Cells(iRow + 1, iColSrc) ' Mark exit if there is no more rows to process If IsEmpty(sRow2) Then bCheck = False End If If sRow1 sRow2 Then .Cells(iRowCnt, iColDst) = sRow1 iRowCnt = iRowCnt + 1 End If End With ' increment row iRow = iRow + 1 Loop Until bCheck = False ' Make a Work name With Worksheets(sSName) .Range(.Cells(iRowStart, iColDst), .Cells(32, iColDst)).Select iRowEnd = Columns(iColDst).Find("", Cells(Rows.Count, iColDst)).Row - 1 End With sWorkRange = "=" & sSName & "!" & "R" & iRowStart & "C" & iColDst & ":R" & iRowEnd & "C" & iColDst ActiveWorkbook.Names.Add Name:="Work", RefersToR1C1:=sWorkRange End Sub So the idea behind this is to create the names by the script (Column "Task" is used as a source for the name and the content is from the second "Type" column). The second macro will create the Column 5 = "Work" and name is as "Work". So in my target sheets, where I use these list as validation I have the following: (Example for the row nr 2) Column F = Category (Validation "List", Source "=Cat"). Name "Cat" contain a Holidays Sick Leave Work Column G = "Task" (Validation List, Source "=INDIRECT($F2)" ). This will get me the Column 5 ("Work") drop down list. Column H = "Type" (Validation List, Source "=INDIRECT(CONCATENATE("__",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G2," ","_"),"/",""),")",""),"(",""),"-",""),"__","_"))) ). There are lots of substitutes here as the name definition can not contain blanks and also any of the characters specified in the "normalize_name" function. Maybe this is not the best solution, but at least it is easy to see what the macros do, which you can not say in many cases when the functions are created in the cell content. :)
A quick video? Even a short video takes a while to produce. Rank what? text or numbers? Numbers is possible without formula insanity, text or mixed data is insane with a formula... My book, Ctrl Shift Enter, shows all three methods, but it is complex. There just is not an easy way to create a unique sorted list.
I am sorry that I do not understand you about "employee attendance" Try searching the excelisfun channel for the video that you would like.
The book does a great job with explaining this. IFERROR has to run the big array formula in every cell, the IF and ROWS does not.
I still do do an occasional video in response to a comment, except I get about 100 request s day... I will do one for your request, just because I think there is a good new way to do it with AGGREGATE ... Late nest week. here is what it might look like:
If formula in E5: =AGGREGATE(14,6,Values/(FREQUENCY(Values,Values)>0),ROWS(E$5:E6))
Formulas are slow are large data sets. Try Advanced Filterm, Extract Unique Records. i have a few videos,. Search
Try this video:
Excel Magic Trick 496: Attendance Sheet with Freeze Pane, IF & SUM functions, Custom Date Formatting
youtube [dot] com/watch?v=J05oqUcsiRM
Yes, this is way far into the series. I have a great series to get you up to speed (53 videos from zero to advanced):
youtube [dot] com/playlist?list=PL706DCCD130378289
Oh, I thought you want to extract a unique list and sort, not just rank. Yes RANK+COUNTIF rules!
Unless you already notice a difference, probably no need to replace.
I do not have any good advice for distance learning...
I think you have have the formula deliver it to cells, and then use cells in DV.
Youa re welcome! I am glad that the videos are useful!
Convert list to an Excel Table and it will expand automatically.
Thank you for your support in buying the book!
Ok, sorry for not being too clear. I meant ranking numbers (i.e. sales). One sales number per row, with tons of rows. The rank & countif combo I use is super heavy on my model and when I heard you mention that countif can slow calculations up by thousands of times I thought "yes" ! And since then I have been trying all sorts of things, in vain... which is why I am asking the expert for a bit of insight... I remember back in the day you used to do short videos in response to a given question.
please dont say sorry, im sorry, i have already learned a lot from your videos, in fact i learn more here than what i learn at my college, i dont know how to thank you! i came to UK to learn, hopefuly one day i go back to help my country, very poor country! you have excellent knowledge of Execl, so you could put all your knowledge and create very useful project. thnak you dear friend.
just amazing Mike what you do here! there is still good people live in this life! just like you! i wrote you 3 days, but you didnt understand what i meant, and that is because English is not my first language. Ok dear, could you create project, for ex, employee attendance, and then we will learn alot from the project, and we can use it in real life. god bless you and your family.
Could you envision making a quick video to show the different, and especially, most efficient ways to rank tens (hundreds) of thousands of rows without duplicates ? I have tried many ways but can't seem to figure out the right way to go. Tons of thanks in advance Mike !
That one is in the book or on the DVD.
Glad the video is of use!
Thanks for your great video. You mention that there is a formula that can sort extract record A to Z so that the dynamic drop down list will automatically sort as well.could you show it? Thanks again for your great video
Hey!! You mentioned not to use the IRERROR and use IF instead to shut the formula after certain rows, is there a specific reason for that? I understand IFERROR is slowe, but never actually understood why?
Glad you liked it!
what about access with VB?
You're very good. Even at the start of the video... no wonder i was already subscribed to you.
haha. holy shit. that was like being hit by a truck of advanced formulas. didn't even know this was possible :D
Mike, where in MrExcel forum is Dominic's discussion about the sorting solution? It seems this issue of Unique Items Only has been around for decades. At the forum, we're really getting into it now! I've rallied for this functionality to be an option in Data Validation Lists. www.mrexcel.com/forum/excel-questions/942043-data-validation-list.html#post4525625
How should I change the formulas in order to extract and count names that are listed in two separate columns (names of workers in the night shift and morning shift) they are duplicates mostly. I tried to change the range but it’s not working…
Hi, your tutorial is very helpful indeed. I would like to ask how to do it it columns.
e.g. in your "unique items" table, instead of it is by row, Is it possible in a column/s?
(Chin Sioux Gigi etc etc)
Mike you have unraveled excel for me. Thanks a million. You have changed me into an excel lover.
I liked your video and found the formula to be helpful, but in cases where we wanted a quick rough sketch of how many names and the list of unique names in a long list of names, you could also create a "dummy" pivot table off your data. In this case have "customer" as the row labels and quickly highlight them all to count number of cells.
Just to be clear, what I was referring to was the most efficient way to replace the classic :
=RANK(A2,$A$2:$A$50000,0)+COUNTIF(B2:B$2,B2)-1
On a side note, I tested your aggregate formula out & it seems to me like it is a ranked extraction of the values, which I will also definitely make good use of...as of today, I was using a sumifs based on the aforementioned descending ranking. Thx again Mike !
I tried to do one of these independently at work the other day, by writing a formula that returned the array of unique numbers with no helper cells. The formula worked fine, but you couldn't use it directly in a data validation list either as a formula or assigned to a named range. Data Validation just wouldn't accept it.
The use of the FREQUENCY function in this video it's something that Iv never seen before !!! Truly pioneering !!!
You're awesome, but move way too fast for me...seems you'd have to already be a pretty strong wiz to understand this...but you go, boy!
Edit: Fixed, _Control_...*Shift*...*_Enter_*...DUH!
Hey Mike, you've been my saving grace for a while now! I finally have a question I cannot locate in your tutorials.
I want to perform this function based on a series of vlookup values.
So here's a sample formula, sans error management, that runs through say A2:A1750, =VLOOKUP(*$A2*,'Some Page'!$A:$F,6:FALSE) so, I drag this formula all the way down, and pull my values over.
I have: John, Suzy, John, John, Suzy, Bill, Bill, etc., but the formula says "1" unique value - I only assume it's validating the formula, not the values. I know I have 16 values. I'm using this as a template so when data changes I don't have to constantly adjust the data validation. Any advice here?
6:01 as soon as I hit ctrl+shift+ enter excel became unresponsive. Note that I have 20k records.I think the space complexity is way too high for this formula. Any faster solution?
Excellent, love this dynamic DV list. Have used the countif before and for large data set it does seem to drag. Will replace with freq formula and see the difference. Thanks Mike.
You're awesome Mike ! By the way, just to let you know that you among other factors also inspired me to pursue some statistics studies starting next Oct or so ! I found a good university, but out of curiousity, should you have any nice recommendations for distance learning - I would gladly look into it/them, as I am in switzerland.. Great wkend !
Thanks for this video; I found it extremely informative. Quick question - I am trying to adapt the method to information grouped horizontally rather than vertically. I adjusted the cell references and replaced ROW and ROWS with COLUMN and COLUMNS. The Unique Count formula works fine, but Return Unique Items is not working correctly. What other adjustments are needed to adapt this to columns rather than rows?
Powerful!
Wow
Is this not easier to you to do in VBA? I'm coming from a background in a language that has a simple unique() formula, and the necessity of all this is tough to accept. I learn from almost all of your videos, but I don't see how I ever could have created this on my own.
MIke this is Great, but what if you have some conditions in this unique list? such as you only want a list with sales larger than $100?
Add an additional condition with IF or Array Multiplying.
Ok thank you.
HEY SIR, CAN YOU HELP ME TO SORT LIST OS NAMES USING THE SECOND AND THIRD CHARECTER OF THE WORDS. I'VE USED =MID(A1,3,100) BUT IT DELETES THE FIRST TWO CHARECTERS AND DISPLAYS ONLY THE REST OF THE LETTERS. IS THERE ANY OTHER WAY BY WHICH WE CAN GET COMPLETE LIST
Can you select in dropdown menu without clicking with mouse?
If I have a growing list, can the Return Unique Item column expanse automatically as well?
based on this video I created a dynamic drop list conditional to 3 IFs statements. how can I show the first value of my drop down list in a cell conditionally formatted to have the values of my drop down list? Thanks for your troubles . . .
Mike you have unraveled excel for me. Thanks a million. You have changed me into an excel lover.
I have seen this abbreviation in on of your videos, written in red ink. What does it mean please? CSE
Thank you so much for this video. It was really helpful :)
I'm buying your book right now at amazon, truly amazing!
7min of this tutorial you can do with =counta() function...
Hey Mike,
I know this is an old vid, but for unqiue count we could also use this formula I think:
{=SUM(IFERROR(1/COUNTIFS($C$2:$C$12,$C$2:$C$12),0))}
+MrSarky1992 For large data sets, the COUNTIF takes a long time to calculate. I test the different formulas in the book and time to verify that FREQUENCY is much faster than COUNTIF. I hope that helps.
I've been studying this for 1 hour now :) amazing
Hi Mike,
I have a dynamic data base table (supplier, product code, description & cost), to which I add new records all the time and I use this dynamic table to creat a DV supplier list and again another DV list to extract all the products and their costs which relates to the particular supplier.
I would like to be able to see the records sorted either numerically OR by letters. At the moment when you open the DV drop down the list the records come up as entered on the dynamic table BUT not sorted.
Is there a solution for sorting the records? (Either on the dynamic table or at the DV side of things by way of Formula or maybe advance filter) and what if BOTH numbers & letters needs sorting? (As the dynamic table records includes both numbers & letters).
I'm happy to email the workbook (heavy in size) should you wish to have a look otherwise I'de appreciate a short answer or any answer.
I'de like to add that I couldn't find your book in Australia and would appreciate your advise how I can buy it outside the USA.
Last but not least I think you have done (still do...) a great job, I've learned "nearly" all I know in excel from your videos and recommend any one reading this post/comment to follow your videos should they wish to extend their knowledge in excel. Keep up the good work and perhaps let us foreigners know where and how can we get your book & DVD in Australia. Thx in advance and apologies for the long comment.
I am working 12-20 each days for weeks. No time. Excel questions try: mrexcel.com/forum. For purchase questions ask the publisher: pub@mrexcel.com
So kind of you to respond, thanks!
I think I am in love with you
Much appreciated.
Any advice how and where can we purchase your book in AU?
Thx in advance. Shay
email pub:
pub@mrexcel.com
You are the excel god
What about a making conditional validation lists which depends on each other and the source is two columns where: Column 1 contain the item types (sorted, but not unique elements) and Column 2 contain the items itself (sorted, but not unique). Here is small example:
type 1, item 1
type 1, item 2
type 1, item 3
type 2, item A
type 2, item B
So each line contain a full record. And there are no spaces (empty rows).
For each column the drop down list has to be created dynamically so user can select first the item type and then item itself.
Could you please make a video with the solution for this problem (It has to be for Excel 2003)?
I tried to follow this video, but I got lost after a few minutes. :(
Update: Managed to do this using VB macro (1'st to create unique list, 2'nd to automatically create a names). :)
Bust still it is interesting how to handle such thing using only formulas.
Would you be willing to share your solution? I'm currently trying to achieve the same setup you've described but I simply cannot for the life of me figure out how to set it up.
Persona Non Grata I have the separated sheet called "Lists" where I have the two columns with my data (Column 3 = "Task", Column 4 = "Type"). And there are two macros which I have assigned it to the two buttons: "Create names", "Create unique list". The input does not change often so this solution works for me. The macros are following:
Sub create_names()
' Specify the sheet name and the location of the input data
sSName = "Lists"
iStartRow = 2
iCol1 = 3
iCol2 = 4
'------------------------------
iRow = iStartRow
bCheck = True
iRangeStart = iRow
Worksheets(sSName).Activate
' Process the rows
Do
With Worksheets(sSName)
' get the 1'st and 2'nd column cell data and 1'st from next row (for comparison)
sCol1 = .Cells(iRow, iCol1)
sCol2 = .Cells(iRow, iCol2)
sCol1Next = .Cells(iRow + 1, iCol1)
' Mark exit if there is no more rows to process
If IsEmpty(sCol1Next) Then
bCheck = False
End If
' if the data is changing in the first column, then create a name
If sCol1 sCol1Next Then
sRange = "R" & iRangeStart & "C" & iCol2 & ":R" & iRow & "C" & iCol2
sString = "=" & sSName & "!" & sRange
sName = "__" & normalize_name(.Cells(iRow, iCol1))
Worksheets(sSName).Range(.Cells(iRangeStart, iCol2), .Cells(iRow, iCol2)).Select
ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:=sString
' update RangeStart
iRangeStart = iRow + 1
End If
End With
' increment row
iRow = iRow + 1
Loop Until bCheck = False
End Sub
Private Function normalize_name(ByVal sText As String) As String
sText = Replace(sText, " ", "_")
sText = Replace(sText, "(", "")
sText = Replace(sText, ")", "")
sText = Replace(sText, "/", "")
sText = Replace(sText, "-", "")
sText = Replace(sText, "__", "_")
normalize_name = sText
End Function
And then there is the second macro:
Sub create_unique_list()
' the list has to be sorted first
' Specify the sheet name and the location of the input data
sSName = "Lists"
iRowHeader = 1
iRowStart = 2
iColSrc = 3
iColDst = 5
iRow = iRowStart
bCheck = True
iRowCnt = iRow
Worksheets(sSName).Activate
' Process the rows
Do
With Worksheets(sSName)
sRow1 = .Cells(iRow, iColSrc)
sRow2 = .Cells(iRow + 1, iColSrc)
' Mark exit if there is no more rows to process
If IsEmpty(sRow2) Then
bCheck = False
End If
If sRow1 sRow2 Then
.Cells(iRowCnt, iColDst) = sRow1
iRowCnt = iRowCnt + 1
End If
End With
' increment row
iRow = iRow + 1
Loop Until bCheck = False
' Make a Work name
With Worksheets(sSName)
.Range(.Cells(iRowStart, iColDst), .Cells(32, iColDst)).Select
iRowEnd = Columns(iColDst).Find("", Cells(Rows.Count, iColDst)).Row - 1
End With
sWorkRange = "=" & sSName & "!" & "R" & iRowStart & "C" & iColDst & ":R" & iRowEnd & "C" & iColDst
ActiveWorkbook.Names.Add Name:="Work", RefersToR1C1:=sWorkRange
End Sub
So the idea behind this is to create the names by the script (Column "Task" is used as a source for the name and the content is from the second "Type" column).
The second macro will create the Column 5 = "Work" and name is as "Work".
So in my target sheets, where I use these list as validation I have the following:
(Example for the row nr 2)
Column F = Category (Validation "List", Source "=Cat"). Name "Cat" contain a
Holidays
Sick Leave
Work
Column G = "Task" (Validation List, Source "=INDIRECT($F2)" ). This will get me the Column 5 ("Work") drop down list.
Column H = "Type" (Validation List, Source "=INDIRECT(CONCATENATE("__",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G2," ","_"),"/",""),")",""),"(",""),"-",""),"__","_"))) ).
There are lots of substitutes here as the name definition can not contain blanks and also any of the characters specified in the "normalize_name" function.
Maybe this is not the best solution, but at least it is easy to see what the macros do, which you can not say in many cases when the functions are created in the cell content. :)
Is this formula possible with added criteria? Example: If you want to see the unique list of names used between date ranges.
Yes, if have to add a few extra IFs inside the data_array argument of the FREQUENCY, right after the not empty condition.
THANK YOU! I believe I got it to work!
Great!!!
ExcelIsFun smxland84 Would you mind sharing the code you used smxland? I'm trying to do the same thing.