CODEWITHSUNDEEP
excellistexcel-formulaexcel-2010
Lance
Lance

Reputation: 1171

How to create sublist from list

Below is an example of the original column's information and the data I want to extract (using a formula, not a filter).

Where List 1 is column A and List 2 is column B. Example:

List 1:

1 Ok
2 Ok
3 Ok
4 Ok 
5 FC34534534534
6 Ok
7 Ok
8 Ok
9 FC34534576878

List 2: 

1 FC34534534534
2 FC34534576878

Any tips would be greatly appreciated as always!

To be clear - I don't want to copy/paste, sort or do anything but have the formula work this out.

Upvotes: 2

Views: 10135

Answers (2)

Jerry
Jerry

Reputation: 71578

Assuming that list 1 is in column A:A, you can use:

=IFERROR(INDEX($A$1:$A$9,SMALL(IF($A$1:$A$9<>"Ok",ROW($A$1:$A$9),9.99E+207),ROW())),"")

In cell C1 (the column is not important, but the row is since we have the function ROW() in the formula).

Then drag down.

EDIT: I forgot to mention that this is an array formula and as such will work properly only with Ctrl+Shift+Enter.

Upvotes: 3

pnuts
pnuts

Reputation: 59485

Guessing that the numbers on the left are row numbers and that your list is in ColumnA without a header, and based on Excel 2007:

  1. Put =IF(A1="OK","",A1) in B1 and copy down to suit.
  2. Copy ColumnB and Paste Special values over the top.
  3. Select enough of ColumnB and replace nothing with say "".
  4. Then replace "" with nothing.
  5. Go To Special and select Blanks.
  6. Delete with Shift cells up.
  7. Tidy to suit.

Upvotes: 0

Related Questions