We help IT Professionals succeed at work.

make list of Unique Distinct number in Array C2-G46

I am having trouble with a formula to return distinct numbers in a range of cells. In this case the range of cells is C2-G46 all of which only contain numbers 1-99.

Using the following array formula:

{=IFERROR(INDEX($C$2:$H$46, MATCH(0, COUNTIF($O$1:O1, $C$2:$G$46), 0)), "")}

returns blank. I have pressed CTRL + SHIFT + ENTER to get the curly brackets required in an array formula but not getting the results I want. Instead a get a blank.

I am interest in a list of unique numbers in sequence beginning in one cell and continuing left to right until all unique numbers are listed. The numbers can be from 1 -99 in case that is needed for a different solution.Unique-DistinctinArrayC2-G46.xlsm
Comment
Watch Question

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Do you really mean G46? In any case assign this macro to a button.

Sub GetUnique()
Dim cel As Range
Dim lngEntry As Long
Dim lngNextRow As Long
Dim colUnique As New Collection

lngNextRow = 2

For Each cel In Range("C2:G46")
    On Error Resume Next
    colUnique.Add cel, CStr(cel)
    On Error GoTo 0
Next

For lngEntry = 1 To colUnique.Count
    Cells(lngNextRow, "O") = colUnique(lngEntry)
    lngNextRow = lngNextRow + 1
Next

End Sub

Open in new window

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
The smallest number in C2:G46 may be returned using:
=MIN(C2:G46)

Open in new window


Assuming that formula is placed in cell P2, you may return the remaining unique numbers sorted in ascending order using:
=IFERROR(AGGREGATE(15,6,C$2:G$46/(C$2:G$46>P2),1),"")

Open in new window

Unique-DistinctinArrayC2-G46.xlsm

Author

Commented:
Martin,

Thank you for all your hard work. At this point I am leaning towards the formula since it seems simpler to edit and use.

As to macros I am puzzled as to how to move a button.

Please advise as to moving a macro button. I would like to know how to place buttons on the attached sheet so they are not overlapping as they are in the attached sheet.

A bit more formula tweaking and testing on my part and you can get full credit for this.

Thanks again.
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
To move a button, the easiest thing to do is to right-click on the button, moving the mouse slightly when you do so. You may have to try a couple of times. If you are successful you'll see a little context window that includes a 'Move here' option. Click that option and then grab and move the button to where you want it. See the attached video.

You may want to look at the properties of a shape. To do that, right-click on the shape and choose Format Shape from the context window. Choose Properties (explore the others some time) and you'll see this. You could consider choosing 'Don't move or size with cells' if you essentiall want the button frozen in place.
2020-01-27_13-25-06.png2020-01-27_13-15-26--1-.mp4