Formula: copy rows in a range, based on non-blank values in one of the columns

[Glenn Kennedy]

It never fails to amaze me what Excel can do - however, I'm still a novice!

There are lots of similar threads on the www, but I haven't been able to find exactly what I need.

I have a large data table (20+columns, 500+ rows) of data.

In one column (column C in the attached example), there may be some blank cells.

I want to use a formula (trying to avoid VBA - a dark art...) to copy onto another sheet, only those rows where there is a value (always a number) in column C.

As usual, this is part of a larger project. I've got all the fancy data analysis at the end of the process all sorted out, but I'm struggling to get the valid dataset arranged. Can you help??

[JBeaucaire]

1) Move the "Before" list up so that it starts in A1.
2) On Sheet2, put in these formulas:
A1: =IF(ISNUMBER(Sheet1!A1), Sheet1!A1, "")
B1: =IF(ISTEXT(Sheet1!A1), "", LOOKUP(2, 1/(ISTEXT(Sheet1!$A$1:$A1)), Sheet1!$A$1:$A1))

3) Copy A1:B1 down.



Notice one of your dates is broken.

[Glenn Kennedy]

Jerry,

Arrgghhh. Where do I begin? In my rush out htis morning, I attached the wrong file!! I have replaced it with the correct one.
I wondered what you meant by the wrong dates...

100,000 apologies...

[ramananhrm]

Try this file

[wenqq3]

L2

=IFERROR(INDEX($A$1:$E$11,SMALL(IF(ISNUMBER($D$2:$D$11),ROW($A$2:$A$11),""),ROW($A1)),1),"")

M2

=IFERROR(INDEX($A$1:$E$11,SMALL(IF(ISNUMBER($D$2:$D$11),ROW($A$2:$A$11),""),ROW($A1)),2),"")

N2

=IFERROR(INDEX($A$1:$E$11,SMALL(IF(ISNUMBER($D$2:$D$11),ROW($A$2:$A$11),""),ROW($A1)),3),"")

O2

=IFERROR(INDEX($A$1:$E$11,SMALL(IF(ISNUMBER($D$2:$D$11),ROW($A$2:$A$11),""),ROW($A1)),4),"")

P2

=IFERROR(INDEX($A$1:$E$11,SMALL(IF(ISNUMBER($D$2:$D$11),ROW($A$2:$A$11),""),ROW($A1)),5),"")

Every time you copy and paste above code, dont just enter, CTRL + SHIFT + ENTER

After that, drag down.

[JBeaucaire]

Non array solution:

G1: "key"
G2: =IF(ISNUMBER(D2), N(G1)+1, N(G1))

Copy G2 down the data set.

L2: =IF(ROW(A1)>MAX($G:$G), "", INDEX(A:A, MATCH(ROW(A1),$G:$G, 0)))

Copy L2 down and across the table.