aligning two sets of data by row number data and both sets have missing/extra data.

Please Login or Register  to view this content.

results in sheet3

I will need to research the commands to fully grasp this, but your output is as I intended.
What I think I understand is:
the 'a = Sheets' assignment is assigning 'a' as the original array, defining the dimensions of the array by current data fields.
The ReDim statement is declaring the dimensions of the output array, 'b'.
In the For statement, 'i' is used as the row counter, starting at 2(for after headers?) through the row limit defined by the UBound command that gets the limit of the a array. j appears to be one less
than the value in the cell defined by the value of i and the first column (which is actually just the row number). Why is there a "-1" when setting the value? This part I'm not sure at all about.
max_j appears to be checking for the max number of columns(?), then later is used to number the rows in sheet 3.
Don't know Application.Max (j, j_max).
I also don't get the lf/then line. We're checking for a blank cell in column 4? There are no blanks until after the last line.

Thanks in advance for any clarity/explanations you can provide. I'd rather understand the why with the how. The whole 'teach a man to fish' thing.
On my project, the sorting was output on the same sheet, but it's good to see how to output to a different sheet. Also there are 17K+ rows in my first set of data and 10K+ in the second. The match-able data is 6193 rows. I tried defining the array search range to these smaller limits, but I kept getting Spill errors trying to do this with formulas. I'm guessing now it was because maybe once it hit the first blank cell and the rest shifted down, it exceeded the array range.

Here is a solution with a formula:
Please try

Formula:

Please Login or Register  to view this content.

Annotated code- hope this helps

Please Login or Register  to view this content.