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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com