Problems with INDEX, MATCH

Hey! I have a table similar to the strongly eased down version seen below:

------Column 1----Column 2---Column 3----Column 4
A-----123 -------------------------------------------
B-----234 -------------------------------------------
C-----345 -------------------------------------------
A-------------------121-----------------------------
B----------------------------------------------------
C-------------------141-----------------------------
A-----------------------------------------------------
B-------------------------------242-----------------
C-------------------------------252----------------
A-------------------------------------------919
B-------------------------------------------818
C-------------------------------------------717

I want that table to look like this:

------Column 1----Column 2---Column 3----Column 4
A------123----------121--------------------- 919
B------234-----------------------242---------818
C------345----------141---------252---------717

conditions are as follows:
- A, B, C are not always in the same order and don't appear in similar numbers
- Not every letter has a value in every column, as hinted in the example
- The table contains 500.000 rows - and 36 columns. That means that I'm in dire need for an automatic solution via formulars or macros

My problem: my excel-knowledge is too limited for that problem. I tried INDEX, MATCH but that failed because excel would always just look for the first "A". column 2 automatically gets a "0" for that reason. That makes my table look like that:

------Column 1----Column 2---Column 3----Column 4
A------123----------0------------0-------------0
B------234----------0------------0-------------0
C------345----------0------------0-------------0


A guess would be, that the solution lies in usage of "LARGE", but that is beyond my understanding
Or would a VBA-Macro be the way?

Help would just be so incredible great!!
Thanks a lot in advance!

Hi

Use the following formula
=IFERROR(INDEX(A$1:A$9999,AGGREGATE(15,6,1/(1/(ROW($A$1:$A$999)*(A$1:A$9999<>""))),ROW($A1:$A9999)),1),"")
and copy down and right

Formula:

Please Login or Register  to view this content.

uploaded file with a view of a solution will be greatly appritiated

Originally Posted by JeteMc

Here is a pivot table solution. It will take care of the 500 000 rows automatically, however the 36 columns will have to be handled individually. The columns are dragged to the values window and the field setting is changed from count to sum. You'll need to enable editing to see the field list.
Here is a file containing a mock up of the data in post #1 with a PT applied: Attachment 451279
Let me know if you have any questions.

O my.. that was easier than expected. Thanks a lot, that helped me a ton! You single handedly saved my day Sir!

Originally Posted by José Augusto

Hi

Use the following formula
=IFERROR(INDEX(A$1:A$9999,AGGREGATE(15,6,1/(1/(ROW($A$1:$A$999)*(A$1:A$9999<>""))),ROW($A1:$A9999)),1),"")
and copy down and right

Formula:

Please Login or Register  to view this content.

That looks like magic! I litteraly have not a single clue what you did there, thanks a lot for you effort!
The formula works to some extend, but has problems with empty rows from what I tried. I would dive deeper and change stuff back and forth, but the solution JeteMc provided was what I needed, so there is no necessity. Thanks again anyway, highly, highly appreciated!

Originally Posted by JeteMc

You're Welcome and thank you for the feedback. If you haven't already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

Done. You too!