load only one instance(no duplicates)

[white6174]

What I'm trying to do is use either data validation or a list box and have it loaded with text from a range (B4:B100) and have it not load duplicates.

Any ideas would be appreciated

thanks steve w

[Aladin Akyurek]

What I'm trying to do is use either data validation or a list box and have it loaded with text from a range (B4:B100) and have it not load duplicates.

You could apply Advanced Filter to the range of interest. Is there a reason that would make this approach undesirable/not applicable?

[white6174]

Thanks I think it will work.

Do you know how to set it up to do this?

What should the criteria be?
a formula?

Thanks for the help

steve w

[Aladin Akyurek]

On 2002-05-17 12:58, white6174 wrote:
Thanks I think it will work.

Do you know how to set it up to do this?

What should the criteria be?
a formula?

Thanks for the help

steve w

Put a label before your data if you don't have one already.

Make the label distinct by formatting it as bold and italic.

Activate the cell immediately underneath the label.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Make sure List range shows the whole range of data.
Leave the Criteria range box empty.
Enter a cell ref outside of your data area for Copy to.
Check Unique records only.
Activate OK.

Select all of the cells of the new range except the label, go to the Name Box on the Formula Bar, type List, and hit enter.

You can now use List as Source in data validation to get a duplicate-free dropdown list.

[Damon Ostrander]

Hi white6174,

___________________________________________

This comment responding to Aladin's suggestion:

An excellent suggestion, but I think that filtering will only work if you are willing to allow it to permanently remove the duplicates from the validation list using the advanced filter copy capability. If not copied the list will still contain the duplicates (albeit hidden) and I believe the validation in-cell dropdown will continue to display the hidden duplicates.
____________________________________________

Here's a macro-based method that will remove duplicates from the list by turning the validation list range into a cell validation string. Unfortunately it is limited to handling a total of no more than 255 characters in the validation string. This probably is not enough to do your job since you have 100 cells unless there are a lot of duplicates. If nothing else, it is an interesting method. To use this macro, select the cells that contain the validation (list validation should already be set to the list range containing the duplicate values) and run the macro.

Here's the code:

Sub UniqueValidationList()
Dim ValList As String
Dim iCell As Integer
Dim jCell As Integer
Dim iDup As Integer
Dim ValRangeName As String
Dim ValRange As Range
Dim Answer As Variant
iDup = 0: ValList = ""
If Left(Selection.Validation.Formula1, 1) = "=" Then
'strip off equal sign character
ValRangeName = Mid(Selection.Validation.Formula1, 2)
Else
ValRangeName = Selection.Validation.Formula1
End If
On Error GoTo BadList
Set ValRange = Range(ValRangeName)
For iCell = 1 To ValRange.Cells.Count
For jCell = 1 To iCell - 1
'skip if duplicate found
If ValRange.Cells(iCell) = ValRange.Cells(jCell) Then
iDup = iDup + 1
GoTo SkipItem
End If
Next jCell
If ValList = "" Then
ValList = ValRange.Cells(iCell)
Else
If Len(ValList) + Len(ValRange.Cells(iCell)) > 254 Then
Answer = MsgBox("Max list size exceeded. List truncated.", _
vbExclamation + vbOKCancel, "Make Unique Validation List")
If Answer = vbCancel Then Exit Sub
GoTo Finish
End If
ValList = ValList & "," & ValRange.Cells(iCell)
End If
SkipItem:
Next iCell
Finish:
Selection.Validation.Delete
Selection.Validation.Add Type:=xlValidateList, Formula1:=ValList
MsgBox iDup & " duplicates removed from validation list", _
vbInformation, "Make Unique Validation List"
Exit Sub
BadList:
MsgBox "Invalid or no validation list range", _
vbCritical, "Make Unique Validation List"

End Sub

[Aladin Akyurek]

___________________________________________

This comment responding to Aladin's suggestion:

An excellent suggestion, but I think that filtering will only work if you are willing to allow it to permanently remove the duplicates from the validation list using the advanced filter copy capability. If not copied the list will still contain the duplicates (albeit hidden) and I believe the validation in-cell dropdown will continue to display the hidden duplicates.
____________________________________________




Damon,

I'm filtering to another location and using the filtered (clean) data as the source for data validation. That will not have any duplicates.

Aladin

[white6174]

Is there a way to have this list update as the range changes and grows.

Thanks for the help you've already provided me.
steve w

[Aladin Akyurek]

On 2002-05-17 13:48, white6174 wrote:
Is there a way to have this list update as the range changes and grows.

Thanks for the help you've already provided me.
steve w

Yes, there is. However, it's a complicated system of formulas. If you're willing to undergo all that, I need to know the name of the sheet where your data containing duplicates is located and in which row it starts. And, one more thing: What is the expected size of the unique list that you need?

[white6174]

sheet name sheet1
cell B4
200

thanks for your help
steve w

[Aladin Akyurek]

On 2002-05-17 15:45, white6174 wrote:
sheet name sheet1
cell B4

Steve,

I'll assume that B3:B17 the following sample where the actual data starts in row 4:

{"List";
"damon";
"aladin";
"mark";
"chris";
"juan";
"mark";
"";
"julie";
"bob";
"brian";
"brian";
"steve";
"aladin";
"ricky"}

The problem satement: Create a unique list from a dynamically changing area of items, which can be used in a cell-dropdown set up with data validation.

Insert a new worksheet in your workbook and name it Admin.

Method 1a

Activate Admin.

In A1 enter:

=MATCH(REPT("z",90),Sheet1!B:B)-(ROW(Sheet1!$B$4)-1)

In A2 enter:

=IF(LEN(Sheet1!B4),SUMPRODUCT((Sheet1!B4>OFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")

and copy this down to as many rows as the original data area in Sheet1 has. Keep copying down for 25 more rows (a number that presumably reflects the expected growth of data area in Sheet1).

In B1 enter:

="Sorted "&Sheet1!B3

In B2 enter:

=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")

and copy this down to as many rows as the formula in A2 has been copied to.

In C1 enter:

="Uniquified "&Sheet1!B3

In C2 array-enter:

=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1>ROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)<>"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))

and copy this down to as many rows as the formula in A2 has been copied to.

Note. To array-enter a formula, hit control+shift+enter at the same time, not just enter.

In D1 enter:

=MATCH("*",C:C,-1)-(ROW($C$2)-1)

Activate Insert|Name|Define.
Enter UniqList as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Admin!$C$2,0,0,Admin!$D$1,1)

Activate OK.

The figure that follows shows how Admin looks after applying Method 1a.

Microsoft Excel - aaUniqListMethod1a White6174.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1		=	=MATCH(REPT("z",90),Sheet1!B:B)-(ROW(Sheet1!$B$4)-1)
*	A	B	C	D
1	:alert('=MATCH(REPT("z",90),Sheet1!B:B)-(ROW(Sheet1!$B$4)-1)')>14	:alert('="Sorted%20"&Sheet1!B3')>Sorted List	:alert('="Uniquified%20"&Sheet1!B3')>Uniquified List	:alert('=MATCH("*",C:C,-1)-(ROW($C$2)-1)')>10
2	:alert('=IF(LEN(Sheet1!B4),SUMPRODUCT((Sheet1!B4%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>8	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>aladin	*
3	:alert('=IF(LEN(Sheet1!B5),SUMPRODUCT((Sheet1!B5%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>2	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>aladin	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>bob	*
4	:alert('=IF(LEN(Sheet1!B6),SUMPRODUCT((Sheet1!B6%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>11	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>brian	*
5	:alert('=IF(LEN(Sheet1!B7),SUMPRODUCT((Sheet1!B7%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>7	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>bob	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>chris	*
6	:alert('=IF(LEN(Sheet1!B8),SUMPRODUCT((Sheet1!B8%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>9	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>brian	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>damon	*
7	:alert('=IF(LEN(Sheet1!B9),SUMPRODUCT((Sheet1!B9%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>11	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>juan	*
8	:alert('=IF(LEN(Sheet1!B10),SUMPRODUCT((Sheet1!B10%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>chris	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>julie	*
9	:alert('=IF(LEN(Sheet1!B11),SUMPRODUCT((Sheet1!B11%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>10	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>damon	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>mark	*
10	:alert('=IF(LEN(Sheet1!B12),SUMPRODUCT((Sheet1!B12%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>4	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>juan	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>ricky	*
11	:alert('=IF(LEN(Sheet1!B13),SUMPRODUCT((Sheet1!B13%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>5	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>julie	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>steve	*
12	:alert('=IF(LEN(Sheet1!B14),SUMPRODUCT((Sheet1!B14%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>5	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>mark	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
13	:alert('=IF(LEN(Sheet1!B15),SUMPRODUCT((Sheet1!B15%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>14	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
14	:alert('=IF(LEN(Sheet1!B16),SUMPRODUCT((Sheet1!B16%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>2	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>ricky	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
15	:alert('=IF(LEN(Sheet1!B17),SUMPRODUCT((Sheet1!B17%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>13	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>steve	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
16	:alert('=IF(LEN(Sheet1!B18),SUMPRODUCT((Sheet1!B18%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
17	:alert('=IF(LEN(Sheet1!B19),SUMPRODUCT((Sheet1!B19%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
18	:alert('=IF(LEN(Sheet1!B20),SUMPRODUCT((Sheet1!B20%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
19	:alert('=IF(LEN(Sheet1!B21),SUMPRODUCT((Sheet1!B21%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
20	:alert('=IF(LEN(Sheet1!B22),SUMPRODUCT((Sheet1!B22%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
21	:alert('=IF(LEN(Sheet1!B23),SUMPRODUCT((Sheet1!B23%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
22	:alert('=IF(LEN(Sheet1!B24),SUMPRODUCT((Sheet1!B24%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
23	:alert('=IF(LEN(Sheet1!B25),SUMPRODUCT((Sheet1!B25%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
24	:alert('=IF(LEN(Sheet1!B26),SUMPRODUCT((Sheet1!B26%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
25	:alert('=IF(LEN(Sheet1!B27),SUMPRODUCT((Sheet1!B27%3EOFFSET(Sheet1!$B$4,0,0,$A$1,1))+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),INDEX(OFFSET(Sheet1!$B$4,0,0,$A$1,1),MATCH(ROW()-ROW($A$2)+1,OFFSET($A$2,0,0,$A$1,1),0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1%3EROWS(OFFSET($B$2,0,0,$A$1,1))-COUNTIF(OFFSET($B$2,0,0,$A$1,1),"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(OFFSET($B$2,0,0,$A$1,1)%3C%3E"-@-",ROW(OFFSET($B$2,0,0,$A$1,1)),ROW()+ROWS(OFFSET($B$2,0,0,$A$1,1)))),ROW()-ROW(OFFSET($C$2,0,0,$A$1,1))+1),COLUMN(OFFSET($B$2,0,0,$A$1,1)))))}')>*	*
Admin

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Method 1b

This is still the same method as the previous one. In order to shorten the formula, names are defined for relevant ranges and used used as such in the target formulas. All these names must be created using the option Insert|Name|Define.

Name: OrigList
Refers to: =OFFSET(Sheet1!$A$4,0,0,Admin!$A$1,1)

Name: LocList
Refers to: =OFFSET(Admin!$A$2,0,0,Admin!$A$1,1)

Name: SortedList
Refers to: =OFFSET(Admin!$B$2,0,0,Admin!$A$1,1)

Name: NoDupsList
Refers to: =OFFSET(Admin!$C$2,0,0,Admin!$A$1,1)

And Uniqlist (see Method 1a).

The figure that follows shows the Admin layout that uses the defined names.

Microsoft Excel - aaUniqListMethod1b White6174.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1		=	=MATCH(REPT("z",90),Sheet1!B:B)-(ROW(Sheet1!$B$4)-1)
*	A	B	C	D
1	:alert('=MATCH(REPT("z",90),Sheet1!B:B)-(ROW(Sheet1!$B$4)-1)')>14	:alert('="Sorted%20"&Sheet1!B3')>Sorted List	:alert('="Uniquified%20"&Sheet1!B3')>Uniquified List	:alert('=MATCH("*",C:C,-1)-(ROW($C$2)-1)')>10
2	:alert('=IF(LEN(Sheet1!B4),SUMPRODUCT((Sheet1!B4%3EOrigList)+0)+1,"")')>8	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>aladin	*
3	:alert('=IF(LEN(Sheet1!B5),SUMPRODUCT((Sheet1!B5%3EOrigList)+0)+1,"")')>2	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>aladin	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>bob	*
4	:alert('=IF(LEN(Sheet1!B6),SUMPRODUCT((Sheet1!B6%3EOrigList)+0)+1,"")')>11	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>brian	*
5	:alert('=IF(LEN(Sheet1!B7),SUMPRODUCT((Sheet1!B7%3EOrigList)+0)+1,"")')>7	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>bob	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>chris	*
6	:alert('=IF(LEN(Sheet1!B8),SUMPRODUCT((Sheet1!B8%3EOrigList)+0)+1,"")')>9	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>brian	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>damon	*
7	:alert('=IF(LEN(Sheet1!B9),SUMPRODUCT((Sheet1!B9%3EOrigList)+0)+1,"")')>11	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>juan	*
8	:alert('=IF(LEN(Sheet1!B10),SUMPRODUCT((Sheet1!B10%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>chris	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>julie	*
9	:alert('=IF(LEN(Sheet1!B11),SUMPRODUCT((Sheet1!B11%3EOrigList)+0)+1,"")')>10	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>damon	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>mark	*
10	:alert('=IF(LEN(Sheet1!B12),SUMPRODUCT((Sheet1!B12%3EOrigList)+0)+1,"")')>4	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>juan	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>ricky	*
11	:alert('=IF(LEN(Sheet1!B13),SUMPRODUCT((Sheet1!B13%3EOrigList)+0)+1,"")')>5	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>julie	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>steve	*
12	:alert('=IF(LEN(Sheet1!B14),SUMPRODUCT((Sheet1!B14%3EOrigList)+0)+1,"")')>5	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>mark	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
13	:alert('=IF(LEN(Sheet1!B15),SUMPRODUCT((Sheet1!B15%3EOrigList)+0)+1,"")')>14	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
14	:alert('=IF(LEN(Sheet1!B16),SUMPRODUCT((Sheet1!B16%3EOrigList)+0)+1,"")')>2	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>ricky	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
15	:alert('=IF(LEN(Sheet1!B17),SUMPRODUCT((Sheet1!B17%3EOrigList)+0)+1,"")')>13	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>steve	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
16	:alert('=IF(LEN(Sheet1!B18),SUMPRODUCT((Sheet1!B18%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
17	:alert('=IF(LEN(Sheet1!B19),SUMPRODUCT((Sheet1!B19%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
18	:alert('=IF(LEN(Sheet1!B20),SUMPRODUCT((Sheet1!B20%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
19	:alert('=IF(LEN(Sheet1!B21),SUMPRODUCT((Sheet1!B21%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
20	:alert('=IF(LEN(Sheet1!B22),SUMPRODUCT((Sheet1!B22%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
21	:alert('=IF(LEN(Sheet1!B23),SUMPRODUCT((Sheet1!B23%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
22	:alert('=IF(LEN(Sheet1!B24),SUMPRODUCT((Sheet1!B24%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
23	:alert('=IF(LEN(Sheet1!B25),SUMPRODUCT((Sheet1!B25%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
24	:alert('=IF(LEN(Sheet1!B26),SUMPRODUCT((Sheet1!B26%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
25	:alert('=IF(LEN(Sheet1!B27),SUMPRODUCT((Sheet1!B27%3EOrigList)+0)+1,"")')>*	:alert('=IF(ISNUMBER(MATCH(ROW()-ROW($A$2)+1,LocList,0)),INDEX(OrigList,MATCH(ROW()-ROW($A$2)+1,LocList,0)),"-@-")')>-@-	:alert('{=IF(ROW()-ROW(NoDupsList)+1%3EROWS(SortedList)-COUNTIF(SortedList,"-@-"),"",INDIRECT(ADDRESS(SMALL((IF(SortedList%3C%3E"-@-",ROW(SortedList),ROW()+ROWS(SortedList))),ROW()-ROW(NoDupsList)+1),COLUMN(SortedList))))}')>*	*
Admin

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Method 2

This method requires using a UDF from Longre's Morefunc add-in, which is downloadable from:

http://longre.free.fr/english/index.html

Activate Admin.

Define first OrigList as described under Method1b.

In A1 enter:

=MATCH(REPT("z",90),Sheet1!B:B)-(ROW(Sheet1!$B$4)-1) [ same as Method 1 ]

In B1 enter:

="Uniquified "&Sheet1!B3 [ same as Method 1 ]

In B2 enter:

=IF(ROW()-1<=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")

and copy this down to as many rows as the original data area in Sheet1 has. Keep copying down for 25 more rows (a number that presumably reflects the expected growth of data area in Sheet1).

In C1 enter:

=MATCH("*",B:B,-1) [ same as Method 1 ]

Now define UniqList as was done under Method 1.

The figure below shows how Admin looks after applying the foregoing method.

Microsoft Excel - aaUniqListMethod2 White6174.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
C1		=	=MATCH("*",B:B,-1)
*	A	B	C	D
1	:alert('=MATCH(REPT("z",90),Sheet1!B:B)-(ROW(Sheet1!$B$4)-1)')>14	:alert('="Uniquified%20"&Sheet1!B3')>Uniquified List	:alert('=MATCH("*",B:B,-1)')>11	*
2	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>aladin	*	*
3	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>bob	*	*
4	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>brian	*	*
5	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>chris	*	*
6	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>damon	*	*
7	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>juan	*	*
8	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>julie	*	*
9	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>mark	*	*
10	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>ricky	*	*
11	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>steve	*	*
12	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
13	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
14	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
15	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
16	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
17	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
18	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
19	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
20	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
21	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
22	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
23	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
24	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
25	*	:alert('=IF(ROW()-1%3C=$A$1,INDEX(UNIQUEVALUES(OrigList,1),ROW()-1),"")')>*	*	*
Admin

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Method 3

Use SQL.

See Mark W.'s contrib in

http://www.mrexcel.com/board/viewtop...c=7253&forum=2

The definition of OrigList must be slightly modified in order to be used in this method.

What method should be prefered?

(1) Method 3, if you can realize the required setup.

(2) Method 2, if (1) cannot be realized.

(3) Method 1b, if (2) is not possible, simply because your users cannot add required add-in.

PS. I used Method 1 quite a few times at the old board and also once at the current board. I dedicate this method to Chris Davison. He will know why .

Aladin