Rearranging Messy Spreadsheet Data

Question

Looking to rearrange data so every instance of EX is merged into one row with accompanying info to more easily read data.

Answer 1

Your data is separated by a blank row, this is good and we are going to use that.

Export the data to .txt file.

1. save as
2. file type = .txt

Use Word to strip all unneeded separation information:

Remove extra tabs

1. open search and replace ctrl+H
2. Find what = ^t^t
3. Replace with = ^t
4. replace all
5. repeat until no more are found

Reduce to single rows and remove the blank row, using a temp holding value

1. open search and replace ctrl+H
2. Find what = ^p^p
3. Replace with = XX
4. replace all
5. Find what = ^p
6. Replace with ^t
7. replace all
8. Find what = XX
9. Replace with ^p
10. replace all

Import back into Excel

1. Copy all (Ctrl+A, Ctrl+C)
2. Paste into Excel (Ctrl+V)