| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If any specific #s are present in range, show each in another cell
If I have a range, e.g. A1:A10, and in that range are various #'s, such as
1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the DIFFERNT #'s that are in that range. i.e. If A1:A10 has only 1s in it, I'd like 1 in cell B1 If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1 If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1 If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1 etc. The number of times each # is in the range is irrelevent. Thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If any specific #s are present in range, show each in another cell
Hi Steve
This UDF should do it: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim IsUnique As Boolean ReDim Unique(0) For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For v = LBound(Unique) To UBound(Unique) If Unique(v) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next UniqueNumbers = Unique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & Unique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Regards, Per "Steve" skrev i meddelelsen ... If I have a range, e.g. A1:A10, and in that range are various #'s, such as 1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the DIFFERNT #'s that are in that range. i.e. If A1:A10 has only 1s in it, I'd like 1 in cell B1 If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1 If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1 If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1 etc. The number of times each # is in the range is irrelevent. Thanks, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If any specific #s are present in range, show each in another
I'm sorry, but I've never used a UDF before, and have no idea how to even
begin with this. Does this go into the cell ? "Per Jessen" wrote: Hi Steve This UDF should do it: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim IsUnique As Boolean ReDim Unique(0) For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For v = LBound(Unique) To UBound(Unique) If Unique(v) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next UniqueNumbers = Unique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & Unique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Regards, Per "Steve" skrev i meddelelsen ... If I have a range, e.g. A1:A10, and in that range are various #'s, such as 1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the DIFFERNT #'s that are in that range. i.e. If A1:A10 has only 1s in it, I'd like 1 in cell B1 If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1 If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1 If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1 etc. The number of times each # is in the range is irrelevent. Thanks, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If any specific #s are present in range, show each in another
Steve,
An UDF is an userdefined function, ie a macro. To use it you have to insert it in the VBA editor. Here's what to do: Open the VBA editor (ALT+F11) Goto Insert Module Paste the code i posted to you earlier in the codesheet which appear Close the VBA editor. Now you are ready to use the UDF. To call the function select the desired cell, and type: =UniqueNumbers(A1:A10) Change the range in the paranthesis as needed and press enter. Hopes this helps. -- Per "Steve" skrev i meddelelsen ... I'm sorry, but I've never used a UDF before, and have no idea how to even begin with this. Does this go into the cell ? "Per Jessen" wrote: Hi Steve This UDF should do it: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim IsUnique As Boolean ReDim Unique(0) For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For v = LBound(Unique) To UBound(Unique) If Unique(v) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next UniqueNumbers = Unique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & Unique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Regards, Per "Steve" skrev i meddelelsen ... If I have a range, e.g. A1:A10, and in that range are various #'s, such as 1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the DIFFERNT #'s that are in that range. i.e. If A1:A10 has only 1s in it, I'd like 1 in cell B1 If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1 If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1 If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1 etc. The number of times each # is in the range is irrelevent. Thanks, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If any specific #s are present in range, show each in another
Amazing. This is working perfectly. Thank you so much. And thanks for the
addional primer on UDFs. One more question, and not important at all, but, is there a way to have the numbers returned be in ascending order ? E.g. I guess they're returning as they're found in the range, such as 2100, 2300, 2130, 2200, which in my range, the 2300 happens to be before the first 2130 number. Again, not really needed, just wondering if it can be made to return the numbers in ascending order. Thanks again, Steve "Per Jessen" wrote: Steve, An UDF is an userdefined function, ie a macro. To use it you have to insert it in the VBA editor. Here's what to do: Open the VBA editor (ALT+F11) Goto Insert Module Paste the code i posted to you earlier in the codesheet which appear Close the VBA editor. Now you are ready to use the UDF. To call the function select the desired cell, and type: =UniqueNumbers(A1:A10) Change the range in the paranthesis as needed and press enter. Hopes this helps. -- Per "Steve" skrev i meddelelsen ... I'm sorry, but I've never used a UDF before, and have no idea how to even begin with this. Does this go into the cell ? "Per Jessen" wrote: Hi Steve This UDF should do it: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim IsUnique As Boolean ReDim Unique(0) For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For v = LBound(Unique) To UBound(Unique) If Unique(v) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next UniqueNumbers = Unique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & Unique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Regards, Per "Steve" skrev i meddelelsen ... If I have a range, e.g. A1:A10, and in that range are various #'s, such as 1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the DIFFERNT #'s that are in that range. i.e. If A1:A10 has only 1s in it, I'd like 1 in cell B1 If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1 If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1 If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1 etc. The number of times each # is in the range is irrelevent. Thanks, Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If any specific #s are present in range, show each in another
Thanks for your reply, I am glad you made it work.
To return the list sorted ascending, replace the existing code with the code below: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim SortedUnique() As Integer Dim IsUnique As Boolean Dim Counter As Variant ReDim Unique(0) IsUnique = True For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For V = LBound(Unique) To UBound(Unique) If Unique(V) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next ReDim Preserve SortedUnique(UBound(Unique)) For c = o To UBound(Unique) SortedUnique(c) = WorksheetFunction.Min(Unique) For r = 0 To UBound(Unique) If Unique(r) = SortedUnique(c) Then Unique(r) = "" Exit For End If Next Next UniqueNumbers = SortedUnique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & SortedUnique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Best regards, Per "Steve" skrev i meddelelsen ... Amazing. This is working perfectly. Thank you so much. And thanks for the addional primer on UDFs. One more question, and not important at all, but, is there a way to have the numbers returned be in ascending order ? E.g. I guess they're returning as they're found in the range, such as 2100, 2300, 2130, 2200, which in my range, the 2300 happens to be before the first 2130 number. Again, not really needed, just wondering if it can be made to return the numbers in ascending order. Thanks again, Steve "Per Jessen" wrote: Steve, An UDF is an userdefined function, ie a macro. To use it you have to insert it in the VBA editor. Here's what to do: Open the VBA editor (ALT+F11) Goto Insert Module Paste the code i posted to you earlier in the codesheet which appear Close the VBA editor. Now you are ready to use the UDF. To call the function select the desired cell, and type: =UniqueNumbers(A1:A10) Change the range in the paranthesis as needed and press enter. Hopes this helps. -- Per "Steve" skrev i meddelelsen ... I'm sorry, but I've never used a UDF before, and have no idea how to even begin with this. Does this go into the cell ? "Per Jessen" wrote: Hi Steve This UDF should do it: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim IsUnique As Boolean ReDim Unique(0) For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For v = LBound(Unique) To UBound(Unique) If Unique(v) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next UniqueNumbers = Unique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & Unique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Regards, Per "Steve" skrev i meddelelsen ... If I have a range, e.g. A1:A10, and in that range are various #'s, such as 1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the DIFFERNT #'s that are in that range. i.e. If A1:A10 has only 1s in it, I'd like 1 in cell B1 If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1 If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1 If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1 etc. The number of times each # is in the range is irrelevent. Thanks, Steve |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If any specific #s are present in range, show each in another
Thank you again so much. This is even more perfect.
Much appreciated. Steve "Per Jessen" wrote: Thanks for your reply, I am glad you made it work. To return the list sorted ascending, replace the existing code with the code below: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim SortedUnique() As Integer Dim IsUnique As Boolean Dim Counter As Variant ReDim Unique(0) IsUnique = True For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For V = LBound(Unique) To UBound(Unique) If Unique(V) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next ReDim Preserve SortedUnique(UBound(Unique)) For c = o To UBound(Unique) SortedUnique(c) = WorksheetFunction.Min(Unique) For r = 0 To UBound(Unique) If Unique(r) = SortedUnique(c) Then Unique(r) = "" Exit For End If Next Next UniqueNumbers = SortedUnique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & SortedUnique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Best regards, Per "Steve" skrev i meddelelsen ... Amazing. This is working perfectly. Thank you so much. And thanks for the addional primer on UDFs. One more question, and not important at all, but, is there a way to have the numbers returned be in ascending order ? E.g. I guess they're returning as they're found in the range, such as 2100, 2300, 2130, 2200, which in my range, the 2300 happens to be before the first 2130 number. Again, not really needed, just wondering if it can be made to return the numbers in ascending order. Thanks again, Steve "Per Jessen" wrote: Steve, An UDF is an userdefined function, ie a macro. To use it you have to insert it in the VBA editor. Here's what to do: Open the VBA editor (ALT+F11) Goto Insert Module Paste the code i posted to you earlier in the codesheet which appear Close the VBA editor. Now you are ready to use the UDF. To call the function select the desired cell, and type: =UniqueNumbers(A1:A10) Change the range in the paranthesis as needed and press enter. Hopes this helps. -- Per "Steve" skrev i meddelelsen ... I'm sorry, but I've never used a UDF before, and have no idea how to even begin with this. Does this go into the cell ? "Per Jessen" wrote: Hi Steve This UDF should do it: Public Function UniqueNumbers(InputRange As Range) As Variant Dim Unique() As Variant Dim IsUnique As Boolean ReDim Unique(0) For Each c In InputRange.Cells If WorksheetFunction.CountIf(InputRange, c) = 1 Then If Unique(0) = "" Then Unique(0) = c.Value Else For v = LBound(Unique) To UBound(Unique) If Unique(v) = c.Value Then IsUnique = False End If Next If IsUnique Then ReDim Preserve Unique(UBound(Unique) + 1) Unique(UBound(Unique)) = c.Value End If IsUnique = True End If End If Next UniqueNumbers = Unique(0) For c = 1 To UBound(Unique) UniqueNumbers = UniqueNumbers & ", " & Unique(c) Next If UniqueNumbers = "" Then UniqueNumbers = CVErr(xlErrNA) End If End Function Regards, Per "Steve" skrev i meddelelsen ... If I have a range, e.g. A1:A10, and in that range are various #'s, such as 1s, 2s, 3s or 4s. I'd like a formula in cell B1 that would produce all the DIFFERNT #'s that are in that range. i.e. If A1:A10 has only 1s in it, I'd like 1 in cell B1 If A1:A10 has 1s and 2s in it, I'd like 1,2 in cell B1 If A1:A10 has 1s,2s, and 3s in it, I'd like 1,2,3 in cell B1 If A1:A10 has 1s,2s, 3s, and 4s in it, I'd like 1,2,3,4 in cell B1 etc. The number of times each # is in the range is irrelevent. Thanks, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup specific value in a column and show it in a new cell | Excel Worksheet Functions | |||
| Display characters present after a specific character | Excel Discussion (Misc queries) | |||
| conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
| show name when a cell has specific word | Excel Discussion (Misc queries) | |||
| How do I show dates older than present date? | Excel Worksheet Functions | |||
Linear Mode
