• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to create pivot table from a Table with merged cells

P

Pcosta

New Member
Hi


I have an issue about using pivot tables constructed with a table that has cells merged. For instance:

The table


Nº Nº task

M00130264 TKS00034660

M00130265 TKS00034560

TKS00034561


gives you a pivot table like this:


Row Labels Count of Nº task

M00130264 1

M00130265 1

(blank) 1

Grand Total 3


The blanck represents the cell that was merged (vertical) with the one that has M00130265. since M00130265 has two Nº tasks TKS00034560 and TKS00034561 the count should be something like this


Row Labels Count of Nº task

M00130264 1

M00130265 2


Now imagine that the table has a lot of lines and has many issues like the one I described. You will have, when constructing the pivot table, a count that is not correct being a line "blanck" the number of merged cells.


Can anyone help me?


Thanks

Regards
 
oldchippy said:
Hi Pcosta,


Welcome to the forum, with your data table you need to un-merge the cells, select cells and press Alt+e+a+a


Then follow Luke's instruction from this link here, then try your pivot table again


http://chandoo.org/forums/topic/fill-in-blank-cell-from-cell-above
Click to expand...
Hey oldchippy !

So I'm trying to do this too, done it before and I think it was this post that lead me to the solution but now I can't find the post of Luke you reference here.

http://chandoo.org/forums/topic/fill-in-blank-cell-from-cell-above
(URL doesn't work)


Hoping you are still here, watching this thread and can help - it's driving me crazy. I know it takes having a master name, then the next cell = original and the next = the previous, and something to do with "Alt+e+a+a", but I'm missing a step . Help appreciated !
 
YOU SHOULD NEVER USE MERGED CELLS


Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.
 
Thank you guys - love this community, I'm hooked.

Luke Bobhc, I agree with you, and while I can technically avoid it it's more of having it look like it's a merged section, Wish I could post a picture here. Basically I have a product list that feeds into another tab that kicks out proposals for my sales team. I recall last September I found the answer on how to do this in this forum , definitely was some excel hack that made the cells appear to be merged and associated the block to the products on the right for the header on the proposal maker (which is a pivot table).

It's been working like a charm but now that I've revised the product list with new additions I can't recall how to hack the cells. It was something like A1 = MEDIA, then A2=A1, A3=A2, etc, and I recall having to do that Alt+e+a+a, and something else but can't recall the exact steps

does this make sense?
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top