Reformatting spreadsheet into 3 simple columns

**flowergirl_389** · 04-16-2014, 03:39 PM

Hi Everyone,

Can someone please help me solve this problem with reformatting my spread sheet quickly. I have attached a very small sample set of a very big table of data. Here is what I am dealing with now:
Current problem state.PNG

I would like my sheet to look like this ( I copied and pasted this manually....also added a line if there needed to be a separate transaction): SHOULD BE.PNG

could someone please show me a few simple formulas and/or steps to get my spread sheet to fit into 3 columns. I attached the excel spread sheet as well... Thank you.

**MarvinP** · 04-16-2014, 03:55 PM

Hi flowergirl and welcome to the forum,

Your question is common enough that I've written code to do it and have used the code for many of these "Crosstab to Table" types of problems.

Find the attached that has a Macro in it. Click on your Current tab and run the macro. It will convert it to a better table format. You will need to tell the macro that only 1 column is fixed.
Then sort the resulting table by the value column and delete all rows with no values. Then sort what's left by the first column and you will get your answer.