Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #7  
Old 05-09-2014, 12:39 AM
Catalin.B Catalin.B is offline Probably a Noob-Question: How to 'resort' a spreadsheet? Windows Vista Probably a Noob-Question: How to 'resort' a spreadsheet? Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Nice
Instead of posting an image of the error, you can describe it in english, just the word "error" is not enough, details matter, like: "Formula is too long", (i guess you can translate such a message in english )

2. If you type ROW($A$2:$A$12) it will return wrong results, offseted with 1 row, and you have to apply a correction: ROW($A$2:$A$12)-1

3. To return a list of unique entries, you can use another array formula, in J2:
=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($B$2:$B$ 12,"<"&$B$2:$B$12)-SUM(COUNTIF($B$2:$B$12,$J$1:J1)),0)),"")
As you can see, only 1 column from those 3 is used to generate this list, if you want a list from all these 3 columns, it gets even more complicated.
4. To create a new list on the side, you have to consider the second argument of SMALL function: "k". If k=1, SMALL will return the smallest value from this array: {1;"";3;"";5;"";7;"";9;10;11} (these are the array results of rows found for "Red"). If k=2, it will return the second smallest value, (3 in our example)
To indicate the k value, i used COLUMN()-1 in the formula. The formula starts from column B, COLUMN() will return the column B number (which is 2). Substracting 1 from that column number , will result 1 (so, in column B SMALL function will return the smallest value from an array of row numbers), this is the way to create a dinamic argument.
If you want to start the formula from column M, this is column 13, so you have to substract 12 from column number to show the first match:
Change COLUMN()-1 to COLUMN()-12 to start from column M.
You will also need to change this:
From: FIND($A17 to FIND($L2 (according to your example)

L.E.: An error may occur because i used the ";" separator in formula, instead of "," if you tried to paste the formula from my first message and you have a comma separator in your regional settings...make the replacements..
Catalin
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Probably a Noob-Question: How to 'resort' a spreadsheet? Word Noob Needs Header & Footer Help citizenzen Word 1 01-17-2013 11:43 AM
Probably a Noob-Question: How to 'resort' a spreadsheet? Noob question Fantasytrader Word VBA 1 03-14-2012 06:30 PM
Button click event from a noob jsampson Outlook 0 10-12-2010 01:48 PM
Noob questions punktilend Word 0 07-24-2009 02:41 AM
A Noob Question Techknology Outlook 0 11-30-2006 07:47 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft