Automatically sorting input data

[simonhg1]

I need some help with excel. I am not sure if what I am trying to achieve is doable with basic formulas or if VBA is needed.

My idea is the following. I have one sheet that serves as a data input sheet. Another sheet is then supposed to pull information from the first sheet.
I've added an overview in the attachment. Usually the sorted data would be shown in a different sheet than the input data.

When the information is pulled, it should automatically get sorted. So all the names that are assigned to the number 1 are shown first. Then all the names that are associated with number 2 and so on. It is not predetermined how many names are associated with one number and this count can always change if a new entry is made.

Is there a way to automatically sort the input data?

Thank you for all ideas and/or solutions.

[Logit]

The following is based on your image where the NUMBER column is located in COLUMN F and the term NUMBER is located in F1 :

Please Login or Register  to view this content.

Paste the above in the SHEET module.

[oeldere]

This can also be done with a pivot table.

[simonhg1]

Pivot table is not an option unfortunately. There is more information and calculations that have to happen in the sorted data set

[simonhg1]

Thank you for your reply. Would you mind quickly explaining that piece of code to me, so I understand the thought process behind it and can adopt it in the future?
If I paste your code, only column F gets sorted. How can I accomplish that's associated with that number sorts accordingly?

[simonhg1]

The following is based on your image where the NUMBER column is located in COLUMN F and the term NUMBER is located in F1 :

Please Login or Register  to view this content.

Paste the above in the SHEET module.

Thank you for your reply. Would you mind quickly explaining that piece of code to me, so I understand the thought process behind it and can adopt it in the future?
If I paste your code, only column F gets sorted. How can I accomplish that's associated with that number sorts accordingly?

[Logit]

.
This will auto sort both columns when the date is copied to Sheet 2 :

Please Login or Register  to view this content.

If you are using formulas in F2:G20 to capture the data from Sheet 1, a different macro will most likely be required.

Paste the above macro into SHEET2 module.

[simonhg1]

Yes, formulas are being used to grab the data from sheet 1. Why and what needs to be changed to still make this code work? Why do formulas interfere with this code?

[Logit]

.
Give the macro a try and verify that it works for you. I probably misspoke myself regarding how the macro works.

My apologies.

[wyowhite]

simonhg1, Here is a formula solution to the problem. It uses 3 helper columns(C-E). it concatenates the Number and Name then Ranks the result. Indexes the concatenated column(C); then splits the Number and Name. If data is going to be added consider using dynamic named ranges ' won' get in

[alansidman]

@simonhg1

Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding