Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"

Hello,

First post (aside from intro) and I believe I have found the correct place after reviewing other posts.

I am attempting to restructure a list of data (a cross-reference) - I have included a small sample set here.
My sheet has pages in Col A and B-FL are design requirements shown on the pages. Many pages do not have requirements in every cell to FL column

Col A occupies 329 rows

Sample Data in Sheet1
Page Req Req Req Req
63 4 55
64 9 5 7
65 8 6 16 9
66 53 17 5
67 67 556 75


My desired results follow
Desired Results on sheet2:
REQ Page
4 63
5 64
5 66
6 65
7 64
8 65
9 64
9 65
16 65
17 66
53 66
55 63
67 67
75 67
556 67

I currently have all the requirements from sheet1!B2:B329 in a list on Col A on sheet 2!ColA
"=IF(ROW() > COUNT(Sheet1!$B$2:$FL$329)," ", (IF(SMALL(Sheet1!$B$2:$FL$329,ROW())= " ","X",SMALL(Sheet1!$B$2:$FL$329,ROW()))))"

Question: how to grab the page number from sheet1!Col A and place those in correct places on Sheet2!ColB (only column B)
(NOTE: Some requirements are on multiple pages - notice requirement 9 (NOTE in NOTE: some are on as many as 8 pages))

My choices are to create the sheet2! list (listed above) or use another column for removing duplicates in sheet2!ColA and them use multiple columns for the pages on sheet2

I am open to ideas...I have about 5000 requirements to map to the document pages - need back and forth traceability for the customer.

Hi excel-card-pulled. Welcome to the forum.

I can do this with array formula ... for a small data set.

This one requires too many array formulas. I've tried every helper column trick I know to avoid those. I've had no success.

I tested some arrays anyway. Everything I come up with slows the workbook down ridiculously.

Of note: Sheet1 B230 has this item among the numbers ... "1656 through 1800". I can't do anything with that even if I could come up with a sane solution.

Maybe someone will be inspired.

So you would be copying and pasting values?

Would you like to see one of the array files? They take about 25-30 seconds to calculate. Every non related edit you make after that is slow.

@BoredWorker
Interesting approach. It never occurred to me to add the decimal like that. I kept using a COUNTIF for the k argument in SMALL. It's slow.

Got it and without array formulas. The calc times are down to @ 18 seconds for the final formula. It still lags and recalculates on every irrelevant edit though, and I can't figure out why.

None of these need to be committed CSE ... all committed by enter.
In column A

Formula:

Please Login or Register  to view this content.

In column B to distinguish the multiple occurrences of numbers

Formula:

Please Login or Register  to view this content.

The final formula in column C

Formula:

Please Login or Register  to view this content.