• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation A to Z in Number oder & exc Duplicates

Stephan

Member
SIMPLE A to Z DATA SORT of alphabetical & numerical DATA to exclude DUPLICATES

My solution totally avoids “PRE” DEFINED NAME RANGEs as populating individually of all possibilities is too time consuming, especially when this AZ sorts it for you!

PURPOSE DESCRIPTION:
These formulas I've compiled do work great together with it’s data set of fictitious cars, see 1st tab SEARCH, select from DATA VALIDATION DROP DOWN BOXES, TOP > DOWN.

See SEARCH tab and make your choice selections via the yellow 4 Data Validation COMBO BOXES:

http://www.hkrebs63.karoo.net/files/CarAZ#1-2-3-4.xls
0.7mb's A-Z: 7 Column sort, faster then alternative below

http://www.hkrebs63.karoo.net/files/CarAZ#Alt1-2-3-4.xls
0.7mb's A-Z: 3 Column sort, oddly pauses between selections, prompting XL to give % calculating in btm l/h corner.

http://www.hkrebs63.karoo.net/files/CarAZ#Photo1-2-3-4.xls
1.5mb's A-Z: 7 Column sort, PHOTO select feature, of FIELDS that include them.

Comment on what you think to these. If you’ve got examples that equal these or better, I’d love to see them! What would be great is a CONVERTER/EXPORTER XL > WEB PAGE, to include the SEARCH features.

Anybody know of such a great ADD IN?

I spoken to spreadsheet-converter.com & caspio.com, currently their export functions don’t include index match offset formulas!

My Spreadsheet is on a few TABS, the essential CATEGORIES selected in DV boxes perform a sort:

SEARCH: final sorts are for specific criteria already selected using INDEX MATCH

FORMULAS: pre sorts are arranged az using COUNTIF INDEX MATCH

CATEGORY: 1st pre sort INDEX MATCH per each CATEGORY selected via SEARCH.

My following spreadsheets sort DATA AZ on a seperate TAB to exclude duplicates, the non VB way! Hence these use FORMULAs! Perform great, unlike many apparently infallible spreadsheets, but obviously will depend on data set.

I designed these XL DB’s as was intrigued by the lack of any good examples of XL spreadsheets that sort data, if you didn't want to display all data via AutoFilter, hence I made these AZ data sorts excluding duplicates. Also DATA ACCESS PAGES via FRONTPAGE or ACCESS, are useless browse 1 record at a time.

Also I have a Macro versions of these that works, but they are slower and my FORMULA edit is more specific of FIELDS meeting selected/chosen CRITERIA.

I did look at DEFINED NAME RANGES in detail, but populating the categories & all individual types takes a very long time.

Unless it could select from available data range, but not from formula edits I looked at:

http://www.hkrebs63.karoo.net/files/DataValIndSubOffV.xls
Defined name ranges, Index Substitute Offset examples: 52kb’s
 

Attachments

Stephan

Member
Here's the Updated Versions, this is totally Generic, better then previous.

see Index Match pairing up on DATA tab, Columns Q to AQ.

2 versions with or without security login.
 

Attachments

Top