Stephan
Member
QUESTION: Is there a SIMPLER A to Z DATA SORT of alphabetical & numerical DATA to exclude DUPLICATES?
Perhaps VLOOKUP? Or maybe my edit is the best solution, as avoiding “PRE” DEFINED NAME RANGEs as populating individually of all possibilities is too time consuming, especially when these AZ sort it for you!
To be specific the long INDEX MATCH via DATA VALIDATION named ranges Formulas I have used together of the following hyperlinks? My version works like complex INDIRECT data range similar to but on a larger scale =INDIRECT(A2&B2&C2&D2)
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 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 edit is on a few TABS, 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
http://www.hkrebs63.karoo.net/files/DataValIndVL.xls
http://www.hkrebs63.karoo.net/files/DataValIndVL.xls
Defined name ranges, Index Indirect Substitute Vlookup example: 52kb’s
Perhaps VLOOKUP? Or maybe my edit is the best solution, as avoiding “PRE” DEFINED NAME RANGEs as populating individually of all possibilities is too time consuming, especially when these AZ sort it for you!
To be specific the long INDEX MATCH via DATA VALIDATION named ranges Formulas I have used together of the following hyperlinks? My version works like complex INDIRECT data range similar to but on a larger scale =INDIRECT(A2&B2&C2&D2)
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 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 edit is on a few TABS, 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
http://www.hkrebs63.karoo.net/files/DataValIndVL.xls
http://www.hkrebs63.karoo.net/files/DataValIndVL.xls
Defined name ranges, Index Indirect Substitute Vlookup example: 52kb’s
