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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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)),"")
 
Upvote 0
Solution
=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?
 
Upvote 0
Just a typo, those ranges should all be the same.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
Firstly you should limit your ranges rather than using virtually the entire column.
Secondly what is the 1st cell that has the formula?
 
Upvote 0
In that case you need to use $A$1:$A1 otherwise you will get a circular reference.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top