Unique Data

Crughp

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCD
1Hole ID
22018PJAC0012018PJAC001
32018PJAC0012018PJAC002
42018PJAC0012018PJAC003
52018PJAC0012018PJAC004
62018PJAC0012018PJAC005
72018PJAC002 
82018PJAC002 
92018PJAC002 
102018PJAC002 
112018PJAC002 
122018PJAC003 
132018PJAC003 
142018PJAC003 
152018PJAC003 
162018PJAC003
172018PJAC003
182018PJAC003
192018PJAC003
202018PJAC004
212018PJAC004
222018PJAC004
232018PJAC005
242018PJAC005
252018PJAC005
262018PJAC005
272018PJAC005
282018PJAC005
292018PJAC005
302018PJAC005
312018PJAC005
322018PJAC005
33
Main
Cell Formulas
RangeFormula
D2:D15D2=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)),"")
 
Solution

Crughp

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
Just a typo, those ranges should all be the same.
 

Crughp

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Excel Formula:
=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)),"")
 
Last edited by a moderator:

Crughp

New Member
Joined
Nov 23, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
In that case you need to use $A$1:$A1 otherwise you will get a circular reference.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,806
Messages
5,574,424
Members
412,591
Latest member
dawnkotzebue
Top