Extract Specific Rows from Spreadsheet

[Frank Bacchus]

Hello:

I would like to extract the rows from a spreadsheet if a column (column A) contains any value and place the extracted rows in a different part of the spreadsheet. Here is an example below and I have attached the same. Thanks.

frank

Datasheet
Date Name Value Company
1/1/2013 John Doe2 North
Ken Smith 6
Abraham Johnson 4
1/3/2013 Don Williams 5 West
Brenda Jones South
Kenneth Brown East
1/6/2013 Jennifer Davis 3 West
Brittany Miller West
Martin Wilson South
1/10/2013 Roger Moore 1 East

Result (only where there is a value under Date):
1/1/2013 John Doe 2 North
1/3/2013 Don Williams 5 West
1/6/2013 Jennifer Davis 3 West
1/10/2013 Roger Moore 1 East

 

[coolsac12]

Hello:

I would like to extract the rows from a spreadsheet if a column (column A) contains any value and place the extracted rows in a different part of the spreadsheet. Here is an example below and I have attached the same. Thanks.

frank

Datasheet
Date Name Value Company
1/1/2013 John Doe2 North
Ken Smith 6
Abraham Johnson 4
1/3/2013 Don Williams 5 West
Brenda Jones South
Kenneth Brown East
1/6/2013 Jennifer Davis 3 West
Brittany Miller West
Martin Wilson South
1/10/2013 Roger Moore 1 East

Result (only where there is a value under Date):
1/1/2013 John Doe 2 North
1/3/2013 Don Williams 5 West
1/6/2013 Jennifer Davis 3 West
1/10/2013 Roger Moore 1 East

Hi Frank bacchus,

Please find the attached sheet...

And refer the link...

http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[coolsac12]

Hello:

I would like to extract the rows from a spreadsheet if a column (column A) contains any value and place the extracted rows in a different part of the spreadsheet. Here is an example below and I have attached the same. Thanks.

frank

Datasheet
Date Name Value Company
1/1/2013 John Doe2 North
Ken Smith 6
Abraham Johnson 4
1/3/2013 Don Williams 5 West
Brenda Jones South
Kenneth Brown East
1/6/2013 Jennifer Davis 3 West
Brittany Miller West
Martin Wilson South
1/10/2013 Roger Moore 1 East

Result (only where there is a value under Date):
1/1/2013 John Doe 2 North
1/3/2013 Don Williams 5 West
1/6/2013 Jennifer Davis 3 West
1/10/2013 Roger Moore 1 East

Hi,

Please ignore previous attachment...

 

[Frank Bacchus]

Hi Coolsac:

Thanks for sending this. I have having an issue applying it to work. If I copy the formula to another spreadsheet with the same kind of data in the A cell, I am ending up with the in the cell and not a the results. This is what I see when I copy the formula in a "A" cell:
{=INDEX(A:A,SMALL(IF($A$2:$A$11<>"",ROW($A$2:$A$11)),ROW(A4)))}

This shows but not the results. Can you please see the attached. Thanks

frank

 

[coolsac12]

Hi Coolsac:

Thanks for sending this. I have having an issue applying it to work. If I copy the formula to another spreadsheet with the same kind of data in the A cell, I am ending up with the in the cell and not a the results. This is what I see when I copy the formula in a "A" cell:
{=INDEX(A:A,SMALL(IF($A$2:$A$11<>"",ROW($A$2:$A$11)),ROW(A4)))}

This shows but not the results. Can you please see the attached. Thanks

frank

Hi @Frank Bacchus ,

Formula is
=INDEX(A:A,SMALL(IF($A$2:$A$11<>"",ROW($A$2:$A$11)),ROW(A4)))

You need to press Ctrl+Shift+Enter instead of Enter...Please remove { }, by pressing Ctrl+Shift+Enter it will automatically add.