Posting GM Warranty Payments With Excel

(<< Continued from previous page)

Step #2: Clean Up the Spreadsheet
1. Click the unnamed, unlabelled cell in the upper left-most corner of the spreadsheet (left of "A" and above "1"). This will select the entire spreadsheet.

2. Place your cursor between column-label cells "A" and "B", as shown here with the red arrows:

3. When you're precisely between the cells, your cursor will change to a single line with left- and right-facing arrows. When this happens, double-click. Excel will resize all cells to fit their contents.

4. Column A should contain repair order numbers. Column B should contain VINs. Column C should contain payment amounts for each repair order. You can probably delete COLUMNS D and beyond (E, F, G, etc.).

5. Delete ALL non-payment ROWS (rejected operations, rejected ROs, payment recap information, etc.). Basically, if it's a row with no payment amount in Column C, delete it.

For example, notice the blank payment info in this screenshot. That row contains a rejected claim. We now must delete that entire row, and any others like it, or else, in the next few steps, the data will get really screwed up. You'll also need to delete any page-header information from the Claim Memo, as well as the summary data ("Claim Memorandum Recap") and the rejected claim descriptions after that.

NOTE: You could also take care of clearing away all the non-payment info when the file is still in text form (either as a .rtf or .txt file). Personally, I just prefer to do it in Excel.

6. When you've deleted all the "rejected claim" rows and other peripheral stuff, your spreadsheet should resemble this:

7. Now we need to get rid of all those blank rows:
1. Select the first blank cell in the first blank row. (A2 in the pic above)
2. EDIT --> GO TO --> SPECIAL --> BLANKS --> OK
3. Blank rows should be highlighted now.
4. EDIT --> DELETE --> Select SHIFT CELLS UP --> OK

8. After that, your screen should look something like this:

9. Just as a matter of error-checking, the total of all amounts in column C should equal the total claims paid from your claim memo.

Now you're ready to "cut 'n' paste" the payment info from Excel, and post it into ADP!

Step #3: Post the Claim Memo >>