Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array with multiple results
I would like to have an array formula that returns the order# contained in
column A if column B has an error in it. Is it also possible to enter this formula into one cell as I don't know how many errors could be in column B. Example A B 123456 S-12-04 123457 #N/A 123458 DS-12-06 123459 #N/A 123460 S-12-05 Array formula would return.... 123457, 123459 Thanks in advance |
#2
|
|||
|
|||
Hi
why not use a filter for this- together with a helper column and the formula =ISERROR(B1) -- Regards Frank Kabel Frankfurt, Germany "James W." schrieb im Newsbeitrag ... I would like to have an array formula that returns the order# contained in column A if column B has an error in it. Is it also possible to enter this formula into one cell as I don't know how many errors could be in column B. Example A B 123456 S-12-04 123457 #N/A 123458 DS-12-06 123459 #N/A 123460 S-12-05 Array formula would return.... 123457, 123459 Thanks in advance |
#3
|
|||
|
|||
One way
Assume the data starts in A2 =INDEX($A$1:$A$10,SMALL(IF(ISNA($B$2:$B$10),ROW($B $2:$B$10)),ROW(1:1))) entered with ctrl + shift & enter note that INDEX starts with A1 and not A2, that is because row always count from row 1, so you either use index from the first row (A1) or offset the small part by 1 copy down until you get a #NUM error Regards, Peo Sjoblom "James W." wrote: I would like to have an array formula that returns the order# contained in column A if column B has an error in it. Is it also possible to enter this formula into one cell as I don't know how many errors could be in column B. Example A B 123456 S-12-04 123457 #N/A 123458 DS-12-06 123459 #N/A 123460 S-12-05 Array formula would return.... 123457, 123459 Thanks in advance |
#4
|
|||
|
|||
This works but returned only the first order#in column A... I understand this
formula except for small refrencing to ROW(1:1) at the end. For this to display all the errors am I going to have to enter it into multiple cells, I would really like to avoid that. Thanks again for all your help. "Peo Sjoblom" wrote: One way Assume the data starts in A2 =INDEX($A$1:$A$10,SMALL(IF(ISNA($B$2:$B$10),ROW($B $2:$B$10)),ROW(1:1))) entered with ctrl + shift & enter note that INDEX starts with A1 and not A2, that is because row always count from row 1, so you either use index from the first row (A1) or offset the small part by 1 copy down until you get a #NUM error Regards, Peo Sjoblom "James W." wrote: I would like to have an array formula that returns the order# contained in column A if column B has an error in it. Is it also possible to enter this formula into one cell as I don't know how many errors could be in column B. Example A B 123456 S-12-04 123457 #N/A 123458 DS-12-06 123459 #N/A 123460 S-12-05 Array formula would return.... 123457, 123459 Thanks in advance |
#5
|
|||
|
|||
To be more specific, I would like to place this formula in a single cell
only, on another worksheet. I think I might be asking to much. "Peo Sjoblom" wrote: One way Assume the data starts in A2 =INDEX($A$1:$A$10,SMALL(IF(ISNA($B$2:$B$10),ROW($B $2:$B$10)),ROW(1:1))) entered with ctrl + shift & enter note that INDEX starts with A1 and not A2, that is because row always count from row 1, so you either use index from the first row (A1) or offset the small part by 1 copy down until you get a #NUM error Regards, Peo Sjoblom "James W." wrote: I would like to have an array formula that returns the order# contained in column A if column B has an error in it. Is it also possible to enter this formula into one cell as I don't know how many errors could be in column B. Example A B 123456 S-12-04 123457 #N/A 123458 DS-12-06 123459 #N/A 123460 S-12-05 Array formula would return.... 123457, 123459 Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Working with array equations | Excel Discussion (Misc queries) | |||
Displaying the results of multiple formulas in a single cell. | New Users to Excel | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |