Unique Data

[Crughp]

Good afternoon,

I have a long list of Windows versions from around 10,000 machines in my environment. I am trying to get a unique list of the values to print on another sheet. Obviously I can use the =UNIQUE(Range) formula when I am using office 365, but I am trying to get the formula so that I can run it in Office 2019. The name of the sheet where the values are stored is BossDesk. The name of the sheet that I am trying to print them out on is WindowsVersion2Name. I have tried several combinations of INDEX, COUNTIF, MATCH, and IFERROR, but none of them seem to return the values I am looking for.

The workbook is macro enabled for other reasons, but I can't seem to get this to work in a non macro format.

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D
1	Hole ID
2	2018PJAC001			2018PJAC001
3	2018PJAC001			2018PJAC002
4	2018PJAC001			2018PJAC003
5	2018PJAC001			2018PJAC004
6	2018PJAC001			2018PJAC005
7	2018PJAC002
8	2018PJAC002
9	2018PJAC002
10	2018PJAC002
11	2018PJAC002
12	2018PJAC003
13	2018PJAC003
14	2018PJAC003
15	2018PJAC003
16	2018PJAC003
17	2018PJAC003
18	2018PJAC003
19	2018PJAC003
20	2018PJAC004
21	2018PJAC004
22	2018PJAC004
23	2018PJAC005
24	2018PJAC005
25	2018PJAC005
26	2018PJAC005
27	2018PJAC005
28	2018PJAC005
29	2018PJAC005
30	2018PJAC005
31	2018PJAC005
32	2018PJAC005
33
Main

Cell Formulas
Range	Formula
D2:D15	D2	=IFERROR(INDEX($A$2:$A$150,AGGREGATE(15,6,(ROW($A$2:$A$50)-ROW($A$2)+1)/ISNA(MATCH($A$2:$A$50,$D$1:$D1,0))/($A$2:$A$150<>""),1)),"")

 

[Crughp]

=IFERROR(INDEX($A$2:$A$150,AGGREGATE(15,6,(ROW($A$2:$A$50)-ROW($A$2)+1)/ISNA(MATCH($A$2:$A$50,$D$1:$D1,0))/($A$2:$A$150<>""),1)),"")

How did you determine to cut the bold and underlined number off at 50 instead of the 150 on the other cell references?

 

[Fluff]

Just a typo, those ranges should all be the same.

 

[Crughp]

That is only returning values of 0. Here is the formula I am using.

=IFERROR(INDEX(BossDesk!$G$2:$G$1048576,AGGREGATE(15,6,(ROW(BossDesk!$G$2:$G$1048576)-ROW(BossDesk!$G$2)+1)/ISNA(MATCH(BossDesk!$G$2:$G$1048576,$A$2:$A2,0))/(BossDesk!$G$2:$G$1048576<>""),1)),"")

 

[Crughp]

Here is sample data:

10.0.10240
10.0.10586
10.0.10586
10.0.10586
10.0.10586
10.0.10586
10.0.10586
10.0.14393
10.0.14393
10.0.14393
10.0.14393
10.0.14393
10.0.14393
10.0.14393

 

[Fluff]

Firstly you should limit your ranges rather than using virtually the entire column.
Secondly what is the 1st cell that has the formula?

 

[Crughp]

$A$2:$A2

 

[Fluff]

In that case you need to use $A$1:$A1 otherwise you will get a circular reference.

 

[Crughp]

That did it, TYVM for your help!