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

SpreadsheetsMicrosoft ExcelMicrosoft Office

Last Comment

Martin Liss

8/22/2022 - Mon

Martin Liss

1/27/2020

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

ASKER CERTIFIED SOLUTION

byundt

1/27/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Pedro

1/27/2020

ASKER

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

1/27/2020

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-15-26--1-.mp4

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23