This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(265.6, 0%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDS%3C/text%3E%3C/svg%3E)
i have data in column a to c, i want to know how to ignore blank cell and 0 values cell and arrange the data with formula as it order, replay as soon as possible thank you.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(147.20000000000002, 13%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJH%3C/text%3E%3C/svg%3E)
Hi @Dipak Sodha
Please try formula like following one:
=INDEX(A$1:A$17,SMALL(IF($A$4:$A$17="","",ROW($A$4:$A$17)),ROW(A1)))
Then you may drag the formula down and to the right until it stops at #NUM!.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 32%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMT%3C/text%3E%3C/svg%3E)
Can you clarify what you're looking for here? Ignoring the Data section, your Obtained Order section is already sorted by accno and blanks are skipped (don't see any 0 cells in your dataset). The Required Order section is skipping blanks as well but is not ordering by accno.
Based upon my understanding, you are already skipping blanks and ordering the way you want in Obtained Order section. For assistance, the ISBLANK function tells you whether a cell is empty.
get list ignoring blank cells, not small to large shorting value
Please be more specific. You are not providing us enough information. In the original screenshot you showed are you not getting the results you expected? If not then identify exact which section/rows are wrong and why. Again, the ISBLANK function can be used to filter out rows that are blank.