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

 

[mphansen]

Looks like Aladin's formula will get me there - Thanks !

 

[jaacatro]

I am absolutely amazed by the wealth of knowledge that was displayed in this post, especially Aladins, Brilliant!

 

[goblin]

Just wanted to improve on Mark W's database approach. There is no need to go throught the Data->Get External Data... wizard/query environment.

Assuming the filename 'c:\temp\querySelf.xls' and the original list is in a range name OrigList and the header of the original list is 'List', array enter the following into a suitably large range:

=SQL.REQUEST("DSN=Excel Files;DBQ=C:\temp\queryself.xls";;4;"SELECT DISTINCT List FROM `C:\temp\queryself`.OrigList WHERE (List<>'')";FALSE)

A more easily maintainable approach is to place to connection and query strings in e.g. cells E2 and E3 respectively (without double quotes) and the formula becomes:

=SQL.REQUEST(E2;;4;E3;FALSE)

That is pretty much for everybody to do. No wizards, no need to manually update query.

Goblin

 

[Juan Pablo González]

That is pretty much for everybody to do. No wizards, no need to manually update query.

Only need to activate the ODBC AddIn, but yes, that is a very good approach too !

 

[XL-Dennis]

Aladin,

Excellent

For the VBA-approaches I´m confused why all approaches involves looping through the range.

After all, reading cellvallues into an array (variant) and then loop through the array is much faster.

Nevertheless, excellent Aladin!

Dennis

 

[Corticus]

Hi all,

Awesome post!

I was succesfully able to use SQL.request on another notebook, could someone please post an example of using SQL.request on the same notebook, same sheet.

I don't know why I can't seem to get it to work...

He's what I did:

SQL.xls
	A	B	C	D	E
1	List
2	A		DSN=ExcelFiles,DBQ=C:\Cort\testss\SQL.xls
3	A		SELECTAAA,BBBFROMtTableWHEREAAA=A
4	A
5	B	Filename:	SQL.xls
6	C	Path:	C:\Cort\testss\
7	D	Criteria:	=	A
8	E
9	F	Single:	#N/A
10	F
11	F	Array:	#N/A	#N/A
12	G		#N/A	#N/A
13	H		#N/A	#N/A
14	I		#N/A	#N/A
15	J		#N/A	#N/A
Excel

Thanks for any help!

 

[Juan Pablo González]

Cort, this worked:

Test.xls
	A	B	C	D
1	Nombre	Edad
2	JuanPablo	24
3	Alberto	28
4	Catalina	20
5
6
7	FileName	C:\Misdocumentos\Test.xls
8	Conection	DSN=ExcelFiles;DBQ=C:\Misdocumentos\Test.xls
9	Output
10	Controller	4
11	Query	SELECTEdadFROM`C:\Misdocumentos\Test.xls`.ListWHERENombre='JuanPablo'
12	Column_Names	FALSO
13
14	Edad	24
Hoja1

 

[Corticus]

Thanks Juan,

That took way too long to figure out.

It ended up being this stupid quote: `
that was messing me up. I was trying to use a straight single quote. It's always a **** quote...

but...yes...

Perfecto!

I like this SQL.request method...very cool and useful, too (not to detract from any other solutions on the page, I like the SQL because of the versatility that the SQL string allows for data retreival for situations beyond just eliminating duplicates).

Take care,

 

[Aladin Akyurek]

...
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...

What follows is a revised, considerably faster version of Method 1, which figures for a great deal in...

http://www.mrexcel.com/board2/viewtopic.php?t=59957

involving a query about a conditional unique list (a list without duplicates).

The data is the same data as in this thread:

B3:B17 in Sheet1 houses...

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

"List" in B3 is a label. The actual data starts at row 4.

We want to extract a list of unique items to a different worksheet,Adminin this case.

Since B4:B17 is of "text" type, let first define BigStr...

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

=REPT("z",255)

Click OK.

Activate Admin.

In A2 enter:

=MATCH(BigStr,Sheet1!B:B)

In A3 enter:

=A2-(CELL("Row",Sheet1!B4)-1)

In A4 enter, merely for cosmetic reasons:

="Sorted "&Sheet1!B3

In A5 enter and copy down:

=IF((Sheet1!B4<>"")*ISNA(MATCH(Sheet1!B4,Sheet1!$B$3:B3,0)),SUMPRODUCT((Sheet1!B4>OFFSET(Sheet1!$B$4,0,0,$A$3,1))+0)+1,"")

In B3 enter:

=COUNT($B$5:INDEX(B:B,A3+ROW(A5)-1))

In B4 enter:

Rank [ which is just a label ]

In B5 enter and copy down:

=IF(A5<>"",RANK(A5,$A$5:INDEX(A:A,$A$3+ROW($A$5)-1),1)+COUNTIF($A$5:A5,A5)-1,"")

In C4 enter, merely for cosmetic reasons:

="Uniquified "&Sheet1!B3

In C5 enter and copy down:

=IF(ROW()-ROW($D$5)+1<=$B$3,INDEX(Sheet1!$B$4:INDEX(Sheet1!B:B,$A$3+ROW($A$5)-1),MATCH(ROW()-ROW($D$5)+1,$B$5:INDEX(B:B,$A$3+ROW($A$5)-1),0)),"")

The above formula replaces the lengthy array formula, improving speed considerably.

aaUniqList Method1.xls
	A	B	C	D
1
2	17
3	14	10
4	SortedList	Rank	UniquifiedList
5	8	5	aladin
6	2	1	bob
7	11	8	brian
8	7	4	chris
9	9	6	damon
10			juan
11			julie
12	10	7	mark
13	4	2	ricky
14	5	3	steve
15
16	14	10
17
18	13	9
19
Admin

If you're willing to adapt Russell Hauf's code in

http://www.mrexcel.com/board2/viewtopic.php?t=32375&start=0

to use the info in B3 and copy down A5:C5 as far as required in order to make the system of formulas to work fully automatically.

 

[Juan Pablo González]

This is the adapted version of Russell's code, to fit Aladin's new Method 1.

The code goes in the Admin sheet module, to make it completely "independent" of all the sheets, and based on the results of the formulas.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#007F00">'Admin sheet module</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()
    <SPAN style="color:#00007F">Static</SPAN> PrevCounter <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> NumRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">If</SPAN> PrevCounter <> Me.Range("A3").Value <SPAN style="color:#00007F">Then</SPAN>
        PrevCounter = Me.Range("A3").Value
        Me.Range("A6:C65536").ClearContents
        <SPAN style="color:#00007F">If</SPAN> PrevCounter < 2 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        Me.Range("A5:C5").AutoFill Destination:=Me.Range("A5:C5").Resize(PrevCounter)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
HandleErr:
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Err.Number
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
        MsgBox Err.Description, vbCritical, "Error in Worksheet_Change Event"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>