Sort column based on multiple values in another column

I have large data set that I need to filter. I need to identify all the cells in column A that have multiple occurrences in column A with different values in column B, while ignoring the ones who have only one or few values in B.

When using simple filter I have to manually filter each value in A and determine if it is reoccurring with multiple values in B. Since it is huge amount of data, that is time consuming. I can not use conditional filtering either since the values in B are not defined set and can not be used as condition.

In example that I have uploaded, only cells with values 101 and 103 are interesting since they have multiple values in column B, while cells with values 102 and 104 should be ignored since they have only one value in column B (even if the same value is reoccurring multiple times):

Sort column based on multiple values in another column - example.jpg

I would appreciate any help or assistance in solving this task.

Hi Bo_Ry, thanks for looking at this and spending your time.

I've tried your formula, but got errors from excel. It seems that it needed to replace comma with semicolon in formula in order for excel to accept it so did it like this:

=IFERROR(INDEX($A:$B;AGGREGATE(15,6;ROW($A$2:$A$99)/($A$2:$A$99=100+INT((COLUMNS($D2:D2)+2)/3));ROWS(D$2:D2));MOD(COLUMNS($D2:D2);3));"")

No more error, but no result either, only blank cells. I placed the formula in D2 and dragad it down, but no results, just empty cells.

Can you please check it one more time, maybe test it?

Thanks

Here is the file:

Thanks Glen, this works on the file I uploaded and you made a great solution for it.

But I probably was not clear enough, sorry. In my real set, there are thousends of values in A so I can not have separete results/column for each single A value

The columns from D to N in my uploaded test file were only to show how sorting should work, what would be relevant result, ant what would not be relevant.

The point is, I have only A and B columns with huge value sets and I need to identify A values that have multiple different values in B. Do you have any idea how to achieve that? Maybe to sort the whole A column so that top positions contain A cells that have the most different occurances is B and therefore to have most "interesting" values on top and it is easy to go through it?

Thanks again

Thanks Bo_Ry, it works now on test file. Great job.

But as I said to Glenn:

"I probably was not clear enough, sorry. In my real set, there are thousends of values in A so I can not have separete results/column for each single A value

The columns from D to N in my uploaded test file were only to show how sorting should work, what would be relevant result, ant what would not be relevant.

The point is, I have only A and B columns with huge value sets and I need to identify A values that have multiple different values in B. Do you have any idea how to achieve that? Maybe to sort the whole A column so that top positions contain A cells that have the most different occurances is B and therefore to have most "interesting" values on top and it is easy to go through it?"

Dear Glenn,

regarding xls format, well it is a habit from the old days when xlsx or docx were new and older versions of excel (2003) were uncompatible with new format. In bigger organisations there are still some Office 2003 versions, but more importanly a lot of people use LibreOffice or similair. Libre will open new format but it is not 100% compatible. So when I use basic functions of document I tend to save it in old format. The one I uploaded was suposed to be in xlsx I hope it is more clear now

About your solution, as I can see it only counts how many times certain value in B reocured. It is elegant sulution but it is not what was intended.

Let me try to be more helpful. I will upload new file with list of passengers in A column and stations in B column. Solution is required to find out which passenger traveled the most. It is not relevant if a passenger apeared several times in only one station. I would need only the passengers that had traveled, changed several stations (even if they had multiple stops in same station - as long as they had traveled to other as well).

Thanks

Answer for passenger 1 is:
5
35
2
2
69

Most apropriate solution is to somehow derive, identify ALL the persons who have traveled (changed at least two stations), sorted by person who have travelled the most, down to the persons who didnt move from single station. Maybe in new columns? It is neccessary to identify all moving passengers, in order to analyze every single one for sales oportunities for instance. Since the real data set can have tens of thousends values, it is neccessary to automate identification of the ones who moved.