A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel - filter results in column, need to find adjacent column data



 
 
Thread Tools Display Modes
  #1  
Old July 21st, 2004, 10:22 AM
adeater
external usenet poster
 
Posts: n/a
Default Excel - filter results in column, need to find adjacent column data

I have columns of data P3:P1007 and that is filtered for the 5 largest
and 5 smallest values by

=IF(ROW()-ROW(AF$3)+1=10,IF(ROW()-ROW(AF$3)+1=5,LARGE($P$3:$P$1007,ROW()-ROW(AF$3)+1),SMALL($P$3:$P$1007,ROW()-ROW(AF$3)+1-5)),"")

The problem is that the columns P3:P1007 and Q3:Q1007 and so on till
AB3:AB1007 are values related to column O3:O1007 which has dates in the
form of 20000809 (9th august 2000). When the 5 largest and 5 smallest
values for P3:P1007 are shown i need a way to display the corresponding
date from O3:O1007 to show up in an adjacent column. Havent been able
to understand Lookup or Match functions and would appreciate help.


---
Message posted from http://www.ExcelForum.com/

  #2  
Old July 21st, 2004, 12:52 PM
Domenic
external usenet poster
 
Posts: n/a
Default Excel - filter results in column, need to find adjacent column data

Hi,

Assuming that your list of 5 largest and smallest values are in Column
AC,

AD3, copied down:

=IF(N(AC3),INDEX($O$3:$O$1007,MATCH(AC3,$P$3:$P$10 07,0)),"")

Hope this helps!


---
Message posted from http://www.ExcelForum.com/

  #3  
Old July 21st, 2004, 01:25 PM
adeater
external usenet poster
 
Posts: n/a
Default Excel - filter results in column, need to find adjacent column data

Hi Domenic,
Unfortunately i keep getting"the formula you typed contains an error)

The column with the 5 greatest and smallest data is AF and i'm trying
to get the result in AE

hence i changed your formula to:
=IF(N(AF3),INDEX($O$3:$O$1007,MATCH(AF3,$P$3:$P$10
07,0)),"")

do i need to make any other changes? and excuse me for my ignorance but
would the MATCH part possible be MATCH(AF3,$O$3:$)$1007,0)),"")

thanks for your help


---
Message posted from http://www.ExcelForum.com/

  #4  
Old July 21st, 2004, 02:05 PM
Domenic
external usenet poster
 
Posts: n/a
Default Excel - filter results in column, need to find adjacent column data

I checked the formula, with the changes you made...

=IF(N(AF3),INDEX($O$3:$O$1007,MATCH(AF3,$P$3:$P$10 07,0)),"")

and it works fine. Make sure you haven't inadvertently added some
other character in the formula -- like a bracket where it doesn't
belong.

Post back if you're still having trouble.


---
Message posted from http://www.ExcelForum.com/

  #5  
Old July 21st, 2004, 02:46 PM
adeater
external usenet poster
 
Posts: n/a
Default Excel - filter results in column, need to find adjacent column data

Hi Domenic. Strangely it still isnt working. I dont know if it makes a
different but more info:

The entries in column P from 3 to 1007 are all in the format
=(C4-C3)-(B4-B3) for P3 and =(C1007-C1006)-(B1007-B1006) for P1006.

O3 is the date 20000809 (9th aug 2000), down to 1007 (4 years).

AF3 to AF12 has 10 rows of data taken from P3 to P1006.

I've double checked for unwanted characters but cant see any.
Any further advice?


---
Message posted from http://www.ExcelForum.com/

  #6  
Old July 21st, 2004, 04:08 PM
Domenic
external usenet poster
 
Posts: n/a
Default Excel - filter results in column, need to find adjacent column data

I don't know why the formula is not working for you. If you're still
getting the same error -- "The formula you typed contains an error" --
then, as far as I know, that means that the syntax is incorrect.

But, as I previously mentioned, the syntax is correct. So, unless
you're entering the formula incorrectly, I don't know what the problem
could be.

Hopefully some else might be able to help.


---
Message posted from http://www.ExcelForum.com/

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
decipher log of scanpst.exe km General Discussion 0 July 18th, 2004 09:00 AM
Countif with 2 or more data ranges in same column Doug Worksheet Functions 1 July 4th, 2004 08:57 AM
Word mail merge with Excel data Peter Jamieson Mailmerge 1 April 26th, 2004 07:30 PM
Linking Excel data through Access to use a Form as an Interface Laura Links and Linking 0 March 23rd, 2004 04:59 PM


All times are GMT +1. The time now is 11:11 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.