I require a complex function that will paste transpose multiple data points specific list

[Jeremy Forsythe]

Hello again,

Once again I am in need of what I think to be a complex function, though I am sure you master's of excel will scoff at its simplicity! ha.

Anyway's here is what I need.

Simply put I have two separate excel sheets and I have to copy and paste a set of data from one sheet to another. However, the way that the data is set up in both sheets makes the process incredibly difficult.

In the first sheet, I have a huge set of data (40,000 rows of data). Each row corresponds to a specific number (10001, 10002, 10003, for example). There are, however, 50 or 60 rows of data that all correspond to the same number (so 50 rows are 10001, and 50 rows are 10002, and so on down). Each number then has a piece of numerical data attached to it in the next column.

In the second excel sheet I have a specific vertical ordering of each of the numbers.

10001
10002
10003
10004
10005

I need to take the 50 or 60 pieces of numerical data corresponding to the number and special paste transpose it into another sheet (as the data is vertically oriented in the first sheet and horizontally oriented in the second sheet.) while also taking into consideration the specific ordering of the vertical list in sheet two, AND i need a way to do this fast where I can simply apply a function to the whole set of data.

(Hopefully this made some sense. Though, I fear my explanation is incredibly nonsensical.) I hope a visual representation will shed light on what I require. so I have included an excel sample. On the first sheet is what the excel spreadsheet looks like BEFORE THE FUNCTION is applied. On the second sheet is what the excel spreadsheet would hopefully look like AFTER THE FUNCTION is applied. Please note that the data from A1-A15 does not correspond exactly to the ordering of the data in I1-I5

If anything is unclear which I am assuming it is please reply and I will try to clarify.

Thank you.

[samba_ravi]

J1=IFERROR(INDEX($B:$B,SMALL(INDEX(($A$1:$A$15<>$I1)*10^10+ROW($A$1:$A$15),0),COLUMNS($J1:J1))),"")
Try the above in J1 then copy and paste across

[Jeremy Forsythe]

J1=IFERROR(INDEX($B:$B,SMALL(INDEX(($A$1:$A$15<>$I1)*10^10+ROW($A$1:$A$15),0),COLUMNS($J1:J1))),"")
Try the above in J1 then copy and paste across

This worked perfectly. Thank you!

Once again I am astounded at both the possibilities inherent in excel and at users who are capable of deriving such complex formulas!

Great work.