Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF or similar but true result to display in consecutive row
Im looking for something along the lines of IF but I want to return the
succesful results in consecutive rows. I cannot use filter or sort as original data needs to be displayed on the same sheet in its original order. eg if cell in Column A = X then return value from same row Column C in the next available row in Column E. Column A Column B Column C Column D Column E Row A X ABC ABC Row B X DEF GHI Row C X GHI MNO Row D X JKL Row E X MNO |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF or similar but true result to display in consecutive row
Try this:
E1: =INDEX($C$1:$C$10,SUMPRODUCT(SMALL((($A$1:$A$10="X ")*ROW($A$1:$A$10))+(($A$1:$A$10="")*10^99),ROWS($ E$1:E1)))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Oldersox" wrote: Im looking for something along the lines of IF but I want to return the succesful results in consecutive rows. I cannot use filter or sort as original data needs to be displayed on the same sheet in its original order. eg if cell in Column A = X then return value from same row Column C in the next available row in Column E. Column A Column B Column C Column D Column E Row A X ABC ABC Row B X DEF GHI Row C X GHI MNO Row D X JKL Row E X MNO |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF or similar but true result to display in consecutive row
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=COUNTIF(A$1:A$5,"x"),INDEX(C$1:C$5 ,SMALL(IF(A$1:A$5="x",ROW(A$1:A$5)-MIN(ROW(A$1:A$5))+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "Oldersox" wrote in message ... Im looking for something along the lines of IF but I want to return the succesful results in consecutive rows. I cannot use filter or sort as original data needs to be displayed on the same sheet in its original order. eg if cell in Column A = X then return value from same row Column C in the next available row in Column E. Column A Column B Column C Column D Column E Row A X ABC ABC Row B X DEF GHI Row C X GHI MNO Row D X JKL Row E X MNO |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF or similar but true result to display in consecutive row
Thanks Ron. This is exctly what I needed.
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF or similar but true result to display in consecutive row
Thanks Biff
Another great solution |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF or similar but true result to display in consecutive row
You're welcome. Thanks for the feedback!
Biff "Oldersox" wrote in message ... Thanks Biff Another great solution |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display the source for a pivot table page field | Excel Worksheet Functions | |||
How to convert Value to words?Eg.Rs.1000/- to Repees One thousand | Excel Discussion (Misc queries) | |||
Please Help Me with Custom menus | Excel Worksheet Functions | |||
Stop renaming or moving sheet tabs | Excel Discussion (Misc queries) | |||
Spellnumber | Excel Worksheet Functions |