AWK is an excellent tool for beating the raw data into submission before importing it into your spreadsheet.
Once the data is in the spreadsheet, you can use the built-in functions like vlookup and pivot tables mentioned earlier, and/or you can write macros using
VBA.
Attached is a very simple stripped-down example how to use the built-in VLOOKUP function. In cell A1, enter any of the values in cells F1 through F12, and Excel will look up that value in the table and update the corresponding value in cell B1.