cleaning up data for a sort

[stephme55]

I have data that I would like to sort by state. However the data is very messy. What is the best approach (formulas, VBA?) to clean it up for a sort? Spreadsheet attached. The real data is 10,000 rows long. Thanks.

[David A Coop]

Hi Steph,

I am confused by the data! Perhaps D3 should be CA instead of AZ?

If so, a helper column in C (headed Sort could have:
C3: =D3
C4: =IF(D4="",C3,LEFT(D4,2)) and copied down.

Then use Column C to sort (with the Channel column as the secondary sort).

(BTW: I had do delete some spaces which were in the "blank" cells D5:D8 I think)

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".