General excel question concerning variable formatting, find and copy a row of data based on results

[BeeFrench]

I have a very unusual situation, in that the formulas I am seeking pertain to MS Excel, however is not actually excel at all.

The program I am using is Alamode in which there is a worksheet, which pretty much emulates excel in almost every way with the exception of being able to enter VBA code.

What I am trying to do is compare a list of data from one style import, inevitably the second list of data is not in similar order whatsoever but does contain a cross reference in a certain number which corresponds with each record via both types of exports.

The one I am trying to compare it to has its "RECORD" In cells A2 through A7(Comps 1-6), skips one then to A9(subject), skips another then goes from A11 through A13(comps 7-9). So these are spaced because of the formatting I have already done and have been using for quite some time for imports. The reason is that the worksheet allows me to import this data directly to my software and also keep separation from the subject and the comps.

So from the second data import, the data is as I mentioned, not in any particular order when compared to cells A2-A13, the data is actually from cells FZ2-11 without any spaces.

What I am looking to do is sort this second list which has the same number of records but not fields to make it correspond to the first list. and put them in the same order with the spacing as with column A in the rudimentary chart below.

For instance
A B C D
1 MLS # Comp# MLS Realist What # it should be THIS WAS NOT CALCULATED
2 846238 1 875188 8
3 779596 2 846238 1
4 742208 3 779596 2
5 737260 4 742208 3
6 739809 5 682376 6
7 682376 6 877631 7
8 739809 5
9 SUBJECT 737602 4
10 N/A 9 (Would need to populate nothing as there is no comp here)
11 877631 7 SUBJECT SUBJECT
12 875188 8
13 N/A 9

sorry if the formatting is not even above

so on line A15 lets say I want to start populating MLS realist data which corresponds with that line, C3 and from this data, pull the corresponding line into A50:A61 through FZ50:FZ61 where the row of actual data has been imported.

In line with all the data Imported, I would like to transfer the data in this row in the order on the far right of the chart above and be able to correspond it with the MLS number on the far left in the chart.

or to rephrase that, I want all the MLS realist data lines which have been imported with an order of 8,1,2,3,6,7,5,4, n/A and subject and place them in the same order as the MLS numbers on the far left in the same format with the spaces at lines 8 and 10, but have the sorted data populated starting on lines 70-81 with its spacing at lines 77 and 79. Again, this would be imported from the data which is attained from A50-FZ61.

If I am unclear about something, please let me know. I tried to be as thorough as possible, and tried not to leave anything out.

I have tried many Vlookup variation formulas for this as well as index and match formulas and cannot seem to get any of them to work right especially with the spacing of column A as well as the unmatched data which fills column C.

[BeeFrench]

data.PNG