Link to home
Create AccountLog in
Avatar of Pedro

asked on

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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

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

End Sub

Open in new window

Avatar of byundt
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Pedro



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.
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.
User generated image2020-01-27_13-15-26--1-.mp4