Excel VBA - Formatting and Data Extraction

The task is to create a VBA script which achieves the following:

Format the raw data on sheet 1 and then extract relevant values into a newly created sheet titled "Input sheet".

SPECIFIC STEPS:

- Take the raw data from Sheet 1 and format it as per Sheet 2 (Ex. Eliminate irrelevant columns) . For the VBA script, do not create a new sheet for the formatted data and just format the existing sheet. Sheet 2 is simply there to demonstrate the desired format.
- Create a dialog box in the sheet asking for user input. The box should say "Input the row number corresponding to an eligible sale". The dialog box should not prevent the user from scrolling through the sheet and should be placed in a convenient location.
- Once a row number is entered by the user, values are extracted from the following columns of the formatted data: A,C,E,F,G,J,K- The values are input into a new sheet formatted as per "Input Sheet". This is the desired output for the script. Refer to the attached workbook to see where all the data from each cell should be entered. The empty cells are to remain empty
- Values from column A are to be cross-referenced with an internal lookup table as per sheet3 so that the Distributor ID is entered for the corresponding Division.
-Likewise, values for column D "AHRI Reference Number" are to be taken from an internal table like on sheet3, so that the corresponding AHRI number is populated for the corresponding Model number
-Column C must only display the first 9 characters of the model number.