Create List including Duplicates from Range

[Webbers]

I have created a spreadsheet which is constantly changing. I need to use a formula that would convert the format of how everything appears on Sheet1 to how everything appears on Sheet2. These IDs and the teams responsible. We don't have a "queue" so I want to have a report, which is why the IDs intentionally show duplicates on sheet 2. My starting point is Sheet 1, I need to know how to create Sheet2 with the use of a formula. The IDs always go from highest # to lowest #. Attached is the range, and then the result I want is the 2 column list, and my sample workbook.

[Fluff13]

Do you have the LET function?

[Webbers]

Fluff13,

No I do not see a LET function. I am using Office365.

[AliGW]

You should have it, then: https://support.microsoft.com/en-us/...5-b8b8f9908999

[Fluff13]

In that case let's do it the old way, otherwise the formula is long.
In A2 copied down
=IFERROR(INDEX(Sheet1!$A$2:$A$14,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$14)-ROW(Sheet1!$A$2)+1)/(Sheet1!$B$2:$J$14<>""),ROWS(A$2:A2))),"")
and in B2 copied down
=IF(A2="","",INDEX(Sheet1!$B$1:$J$1,AGGREGATE(15,6,(COLUMN(Sheet1!$B$1:$J$1)-COLUMN(Sheet1!$B$1)+1)/(Sheet1!$B$2:$J$14=A2),COUNTIF(A$2:A2,A2))))

[Fluff13]

@AliGW Not sure it's been released to the semi-annual channel yet.

[AliGW]

Ah - yes, missed that. Sorry!

[Webbers]

Fluff13 & AliGW----

wooohoooo! I tried in my sample workbook and it worked (duh)! Then I tried it in a copy of my live file. That took a bit longer to make work, as I had to change all the references, my "before" and "after" ranges both appear on the same worksheet tab within my workbook.... a codes tab that is used in development only, the user never sees it. As usual, you have come to my rescue once again. I was not sure exactly how to ask, but I knew exactly what I wanted. And I will keep my eyes open for the LET function.

[Fluff13]

You're welcome & thanks for the feedback.