Create PO number List skip blank cells

S

SDCarroll10

New Member
Joined
Sep 9, 2019
Messages
5
Existing data is Sheet4!E2:Sheet4!E700 and appears as below:

[TABLE="width: 99"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Retailers PO[/TD]
[/TR]
[TR]
[TD="align: right"]9608561996[/TD]
[/TR]
[TR]
[TD]blank
blank[/TD]
[/TR]
[TR]
[TD]blank[/TD]
[/TR]
[TR]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: right"]5008561749[/TD]
[/TR]
[TR]
[TD]blank[/TD]
[/TR]
[TR]
[TD]blank[/TD]
[/TR]
[TR]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: right"]4908561680[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
This will list the highest to the lowest, ignoring blank rows.


ABCDE
No Blank RowsRetailers PO
96085619969608561996
5008561749blank
4908561680blank
blank
blank
5008561749
blank
blank
blank
4908561680

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2
[/TH]
[TD="align: left"]=IFERROR(AGGREGATE(14,6,$E$2:$E$700,ROWS($A$1:$A1)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?
 
Upvote 0
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.



[TABLE="class: cms_table"]
<tbody>[TR]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]No Blank Rows[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Retailers PO[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]9608561996[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]9608561996[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]5008561749[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]4908561680[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]5008561749[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]blank[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]4908561680[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
ABCDE
No Blank RowsRetailers PO
96085619969608561996
5008561749
4908561680
5008561749
4908561680

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2
[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Last edited:
Upvote 0
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))),"")
 
Upvote 0
Code: 
=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))),"")
 
Upvote 0
sdcarroll10 said:
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))),"")
Click to expand...


it works!!! You are amazing! Thank you thank you thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Cell with 0 value showing blank
2
Replies
15
Views
803
mhessnm
M
A
  • Solved
Formula to take cells in A2:A100 and paste them in Column B leaving blank row in between (A2 in B2, A3 in B4, A4 in B6, etc)
Replies
7
Views
631
dreid1011
dreid1011
S
  • Solved
Got my Macro to skip blank rows when creating the CSV, but I need it to remove them
Replies
5
Views
598
sbar7
S
SkywardPalm
  • Question Question
Vlookup all blank cells taking a long time..
Replies
1
Views
799
SkywardPalm
SkywardPalm
B
  • Solved
Help with Index/ Small formula to return valid values and not give a blank row for cells that don't
Replies
4
Views
798
Fluff
Fluff

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top