• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort Data and Hide Rows

P

Patrick Spence

New Member
I am trying to fill in the table with the job name from column J depending on the data entered into B16. I want the rows to be filled in with no blank spots and if possible to hide the blank rows at the bottom. I can't think of a simple way to do this and would appreciate any suggestions on how to accomplish this.

I have attached a basic example of what I am looking to do.
 

Attachments

Hi Patrick,

What are you trying to do can be done with formulas i.e. filling job number, but the process can be slow if data is large. Please find the attached file, which uses macro to do the task of sorting and filling job number in table.

As far as hiding rows are concerned, I did not understand your requirement. Kindly, explain a bit more.

Regards
 

Attachments

Hope this help.

This is an array formula so must be enter with Ctrl+Shift+Enter.

Code: 
=IFERROR(INDEX($J$1:$J$12,SMALL(IF($K$1:$K$12=$B$16,ROW($J$1:$J$12)),ROW()-1),1),"")
 
Deepak said:
Hope this help.

This is an array formula so must be enter with Ctrl+Shift+Enter.

Code: 
=IFERROR(INDEX($J$1:$J$12,SMALL(IF($K$1:$K$12=$B$16,ROW($J$1:$J$12)),ROW()-1),1),"")
Click to expand...

I tried to copy the formula in the actually sheet but it did not work. However, I was able to make it work on the original one I uploaded. Any suggestions, I have attached the exact sheet I am just with some unimportant data removed?

Thank you in advance for your help
 

Attachments

Somendra Misra said:
Hi Patrick,

What are you trying to do can be done with formulas i.e. filling job number, but the process can be slow if data is large. Please find the attached file, which uses macro to do the task of sorting and filling job number in table.

As far as hiding rows are concerned, I did not understand your requirement. Kindly, explain a bit more.

Regards
Click to expand...

Thank you for your help, I might try the formula method first, but if that does not work this seems like a win. Just trying to cut down what they have to click. Thank you for your help
 
@Patrick Spence

I hope you are trying Deepak Formula in cell A28 in your last uploaded file. If so, try these steps:

1. Unmerge cell.
2. Insert below formula and copy down.
=IFERROR(INDEX($M$1:$M$51,SMALL(IF($N$1:$N$51=$K$10,ROW($M$2:$M$51)-ROW($M$2)+1),ROWS(A$28:A28))),"")

Enter with Ctrl+Shift+Enter.

3. Merge cells.

Regards,
 
Dear Patrick Spence,

In addition to what Somendra Misra said.

You have to follow unmerge & merge one time activity as array formula doesn't accept merge cell, however later merge is allowed.

You can also go with vb where no need for this step & macro can be trigger with worksheet change event.
 
You must log in or register to reply here.
Back
Top