Sorting in Advcanced Filter ?

[jobinet]

Hi All,

I use my excel as a mini database w more than 20,000 rows of data. One ne to navigate for me is to use Advanced Filter feature, that helps me to filter quite a fair bit of details.

Often, in order to pull out information for specific records, I use a field which is a unique identifier. For example my database can you look as such:

UniqueKey Field1 Field2 ...
===================
ZA143 fifi fofo ... [row 1]
YT987 bibi bobo ... [row 2]
GG123 yoyo lol ... [row 3]
and so on

Now in my filter criteria If I use following 2 inputs:

UniqueKey
=======
GG123
ZA143

Then Excel will filter out 2 rows and will display in the 'row sequence of excel' :

UniqueKey Field1 Field2 ...
===================
ZA143 fifi fofo ... [row 1] becomes visible
GG123 yoyo lol ... [row 3] becomes visible, row 2 being hidden

My problem is that I would like to keep the order or sequence which has been specified in the criterias inputs. In short I wish I get :

UniqueKey Field1 Field2 ...
===================
GG123 yoyo lol ...
ZA143 fifi fofo ...

Is there a way to do so ? Its a way to sort based on a given sequence not based on any ascending or descending alphanumerical data.

Your lights or advice will be very much appreciated.

Have a good weekend
Joff

 

[Peter_SSs]

Joff

Welcome to the MrExcel board!

As you have found, Advanced Filter does not have a sort feature. In any case, your sort is based on a user-defined order so is a bit trickier.

If you are working manually, see if the method outlined below is any use. Also, a macro could be constructed to do all this for you.

I have added a helper column (D) with formula in D2 copied down.

When doing the Advanced Filter, I chose 'Copy to another location' so the results appear from Col H onwards.

I haven't done so yet, but I would then sort cols H:K based on col K.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	UniqueKey	Field1	Field2	Order		UniqueKey		UniqueKey	Field1	Field2	Order
2	ZA143	fifi	fofo	3		GG123		ZA143	fifi	fofo	3
3	YT987	bibi	bobo	#N/A		ZA143		GG123	yoyo	lol	2
4	GG123	yoyo	lol	2		T5678		ZA143	sss	www	3
5	ZA143	sss	www	3				T5678	aaaa	bbbb	4
6	T5678	aaaa	bbbb	4
7
Filter & Sort

 

[jobinet]

Hi Peter

Thanks for such a prompt and detailed answer. I think ill start smiling a lot down here.

Well that sounds a good suggestion, in VBA i can make my way there:

* Add a Field MySequence in criterias list, w sorted 1,2,3,... values.
* use advanced filter w/o this field
* work on output, and assign sequence number for every UniqueKey
* Sort output w my assigned sequence number.

I can do like that yup, thanks a lot.

 

[Peter_SSs]

Hi Peter

Thanks for such a prompt and detailed answer. I think ill start smiling a lot down here.

Well that sounds a good suggestion, in VBA i can make my way there:

* Add a Field MySequence in criterias list, w sorted 1,2,3,... values.
* use advanced filter w/o this field
* work on output, and assign sequence number for every UniqueKey
* Sort output w my assigned sequence number.

I can do like that yup, thanks a lot.

Sounds like you have it under control now but you could consider leaving out "Add a Field MySequence in criterias list, w sorted 1,2,3,... values" given that you originally said the criteria list is already the order you want.

Then for "work on output, and assign sequence number for every UniqueKey" you could have the code insert a formula directly in to K2:Kxx

Depending on your layout, something along these lines:

With Range("K2:K" & Range("J" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=MATCH(RC[-3],C[-5],0)"
End With