Create PO number List skip blank cells

[SDCarroll10]

Existing data is Sheet4!E2:Sheet4!E700 and appears as below:

Retailers PO

9608561996

blank blank

blank

blank

5008561749

blank

blank

blank

4908561680

<colgroup><col></colgroup><tbody>
</tbody>

I need to pull it to another sheet (column A) but skip the blank rows.

I have tried several Iferror formulas from different threads that are only showing zeros for every result.

Please help

 

[Toadstool]

This will list the highest to the lowest, ignoring blank rows.

	A	B	C	D	E
1	No Blank Rows				Retailers PO
2	9608561996				9608561996
3	5008561749				blank
4	4908561680				blank
5					blank
6					blank
7					5008561749
8					blank
9					blank
10					blank
11					4908561680

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A2 =IFERROR(AGGREGATE(14,6,$E$2:$E$700,ROWS($A$1:$A1)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[SDCarroll10]

Hi,
Thank you but I don't want it to change the order of the information. Is there a way to have it skip the blanks but keep them in the order they appear?

 

[Rick Rothstein]

Is there a way to have it skip the blanks but keep them in the order they appear?

Do you mean that you want to keep them on the same row? If so...

=IF(E2="","",E2)

 

[SDCarroll10]

Hi Rick,
I need it to take the info from column E and put it into column A as shown below. The formula Toadstool provided works but it re-arranges the PO numbers to be highest to lowest. I need them to stay in the same order they are already in.

	A	B	C	D	E
1	No Blank Rows				Retailers PO
2	9608561996				9608561996
3	5008561749				blank
4	4908561680				blank
5					blank
6					blank
7					5008561749
8					blank
9					blank
10					blank
11					4908561680

<tbody>
</tbody>

 

[Toadstool]

	A	B	C	D	E
1	No Blank Rows				Retailers PO
2	9608561996				9608561996
3	5008561749
4	4908561680
5
6
7					5008561749
8
9
10
11					4908561680
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1 (2)​

Worksheet Formulas Cell Formula A2 =IFERROR(INDEX($E$2:$E$700,AGGREGATE(15,6,ROW($E$2:$E$700)-ROW($A$1)/($E$2:$E$700<>""),ROWS($A$1:A1))),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rick Rothstein]

Maybe this array-entered formula (assumed to be placed in cell A2 to start)...

=IFERROR(INDEX(E:E,SMALL(IF(E$2:E$100<>"",ROW(E$2:E$100)),ROWS(A$2:A2))),"")

Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[SDCarroll10]

Hi,

Column E is actually on a separate tab so I edited the formula to the below but it is only providing a blank cell. What am I doing wrong?

=IFERROR(INDEX(Sheet4!E:Sheet4!E,SMALL(IF(Sheet4!E2:Sheet4!E700<>"",ROW(Sheet4!E2:Sheet4!E700)),ROWS(A$2:A2))),"")

 

[Toadstool]

=IFERROR(INDEX(Sheet4!$E$2:$E$700,AGGREGATE(15,6,ROW(Sheet4!$E$2:$E$700)-ROW($A$1)/(Sheet4!$E$2:$E$700<>""),ROWS($A$1:A1))),"")

 

[SDCarroll10]

hi,

column e is actually on a separate tab so i edited the formula to the below but it is only providing a blank cell. What am i doing wrong?

=iferror(index(sheet4!e:sheet4!e,small(if(sheet4!e2:sheet4!e700<>"",row(sheet4!e2:sheet4!e700)),rows(a$2:a2))),"")

it works!!! You are amazing! Thank you thank you thank you