Line up values from one column based on value from another column

I have a data table of size 40'000 X 2:
- Column A has 8'000 unique values.
- Column B has 30'000 unique values.

So, on average, every unique value in column A has 5 values of column B. In reality it varies from 2 to up to 10 values.

I need this table to be resorted in such a way that it would end up being 8000 rows and 1+10 columns listing unique values from column A and lining up corresponding values from column B.

Example:

Sample.PNG

I've been trying to use these:
E2{=INDEX($A$2:$A$17, MATCH(0, COUNTIF($E$1:$E1,$A$2:$A$17), 0))}
F2{=IFERROR(INDEX($B$2:$B$17, MATCH(0, COUNTIF($E2:E2,$B$2:$B$17)+IF($A$2:$A$17<>$E2, 1, 0), 0)), "")}

It works, however, in the last 24 hours somehow I managed to get only 1000 rows finished. I played with manual and auto calculation, that doesn't help and Excel is either stuck at Calculating (8 threads): 0%, turns off automatically or just stops calculating.

Any ideas on how to improve these functions or replace them with something else that would allow Excel to do these calculations much faster would be very welcomed.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Load your data into PowerQuery (Data ribbon | Form Table/Range ...).

Use this M Code in the Advanced Editor window:

Please Login or Register  to view this content.

Using your data - attached.

Well, you should have mentioned it!!! Inadequate sample data will always lead to inappropriate solutions.

Provide an updated sample and I will have another look if I have time.

Here's a version that should work for you. I had to remove the Changed Type step.

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

No worries. You can refresh the query using Refresh on the Data ribbon, or set it to refresh automatically.

Please see post #10 and update your profile accordingly.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

For Office 365 Please try

E2
=UNIQUE(A2:A17)

F2:F6
=TRANSPOSE(FILTER($B$2:$B$17,$A$2:$A$17=E2))

Unique Function is available if you update office 365.

https://support.office.com/en-us/art...a-40204fb85e1e