Convert Pivot Table Format to Normal Format

[mapleaes]

Hi,
Is it possible to convert pivot table data into a normal format?
I was told to compare two worksheet, one is in normal format while another is an un-edit-able, pivot table format

And I need the pivot table format restored to normal format to be able to do anything.
While I understand it is possible to manually restore the worksheet, I need an automated way (Or at least a more easier/quicker way than editing the whole sheet) to complete this task as there is a lot of worksheet i need to restore.

In my searches for a solution, I came across this thread:
http://www.excelforum.com/excel-gene...ar-format.html
Which had a similar problem as mine, but the problem is, I dont kinda understand the solution posted

Can anyone suggest a new solution to me or atleast explain the solution in the thread mentioned above?

I have attached the worksheet in pivot table format in this thread, along with the restored format inside.

Any help is much appreciated, thank you very much

[simarui]

I think the solution at that other link is suggesting you copy the pivot table and then elsewhere you paste, special, values...

I don't think this is what you're looking for. you want the various data labels to repeat themselves in every row, correct?

Assuming so, I fairly often use this little guy:

Please Login or Register  to view this content.

as in the attached...

[mapleaes]

Hi! Thanks for replying.

Errr....this is kinda embarrassing, can you explain to me how to use that formula?
I had all kinds of weird problems with it in my process of studying the formula, like it only allows me to draw one row or one column with the fill method, or that it only copies the value of the very first instance of the formula, or only one cell was fill even after i select one whole range and copy the formula

And it fills blank cells with 0?

Am I missing something?

Thank you for your help.

[simarui]

Those are rather strange errors and i'm not really sure I understand what you mean. can you upload a sample where you tried to use the formula and it didn't work...?

Couple thoughts off the top of my head:

the 'My solution' tab of the workbook I uploaded shows how it should work... if you don't have a 'My solution' tab in your file you'll need to remove the reference to 'My solution' from the formula or replace it with whatever your worksheet name is...

make sure you don't have any absolute references, (i.e. $a$2). that might be causing the "only copies the value of the very first instance of the formula" problem...

The 0's are basically saying the value that formula is returning is 0 (column B of 'My solution')... you can either delete column b or you can put a ' in b1 and those cells will be blank...

What do you mean "draw one row or one column with the fill method"?

[mapleaes]

Well, its not like its didn't work, its more like weird things happen when it work ^^lll

Some times when I pasted the formula and press enter, a dialog open up and tell me to specify the excel file I wanted to refer to, is that how it is suppose to work? Since the formula behaves the most normal when that dialog opens up, other times, it just copied the cell with 'Area code' in it whenever I press the enter.

And another new error came to me, after having a whole range of 'Area Codes', an error message pops up when I wanted to save and convert those cell with values to what they should be, the blank cell are still 'Area Codes'

I didn't put any absolute references in the formula, so its weird why it does that, the error message that pops up during saving seems to mention about some formula incompatibility?

Ah, this is my bad, I thought we could select a entire range using the fill method (The small black square at the lower right of the selected cell), while in truth we can only select the whole column or rows at a time.

Thanks for your help.

EDIT:
Oh I just realized, could it be because of program versions? I'm using Excel 2007

[simarui]

The formula I presented above should be copied into cell A2 of 'My solution'. A1 of 'My solution' needs to be

Please Login or Register  to view this content.

That should fix the circular reference error.

As for the dialog box asking you to specify which excel file you want to refer to, that probably indicates the name of your sheet isn't exactly the same as what's in the formula... other than that i'm not sure.

Finally the compatibility mode error does have something to do with changing versions of excel... or rather saving in an old format I think (.xls instead of .xlsx) I don't actually know much about that error but I believe there are certain formulas which would not work if you opened the workbook in earlier versions of excel... I'm pretty sure you can ignore it as long as you're going to use excel 2007 or higher.

[mapleaes]

Hmm....Well.....the values still repeats themselves after I've filled an entire range with the formula, but it turned normal once i clicked the save button.

And that compatibility error thingy vanished after I changed the file format.

Thanks for taking to help me in my problem, you have my deepest thanks, thank you very much.

Its a shame that I could only rep you once at a time :D