VBA Pivot Table with Fixed Rows

[excelnewbie80]

Hi,

This is my first post. I have been stuck with this for some time. I am trying to create a pivot table with fixed rows but it was not possible. The reason for doing this is that sometimes I get data without certain row labels and I do not want the rows to go missing.

Data (monday)
Apple---1
Apple---1
Pear----1

Data (tuesday)
Apple---1
Orange-1
Pear----1


The pivot table I want to achieve ( for monday):

Fruit----Count
Apple-----2
Pear -----1
Orange----0

Even though the count for orange is 0, I want to still see it in the table. Is there any way to do this?

[arlu1201]

Firstly, you cannot add a row into a pivot table manually, even if its via VBA.

Also, i just did a dummy pivot using your data above and i can see orange in the pivot. I used fruit as the row label (you can even use as a column label) and count in the values field.

[excelnewbie80]

Hi, thanks for your reply. I think you misunderstood my question. I am trying to fix the row "orange" so that I can see it even when my data does not have "orange". I have edited my question to better explain the problem.

[arlu1201]

As far as i know, the pivot only reflects what entries you have in your data.

Are you trying to show "orange" in the pivot, so that you can then use it for other analysis using the getpivotdata function?

[excelnewbie80]

No, I am not using getpivotdata function. My pivot table is based on "Sheet1" and the values in "Sheet1" changes daily. Sometimes, it may have "orange", sometimes it does not have. I have having trouble trying to fix the rows so that I can see all the row labels even though they do not have any data attached to it.

[arlu1201]

I can suggest something - You can have the pivot in sheet1 and keep refreshing it as your data gets updated. However, you can also have a rolling report in another sheet in the same workbook which will add in any new row labels that keep getting added to your pivot.

[JosephP]

I would use functions instead of a pivot table - SUMIF and so on - assuming you have a fixed list of items to show.

[excelnewbie80]

I can suggest something - You can have the pivot in sheet1 and keep refreshing it as your data gets updated. However, you can also have a rolling report in another sheet in the same workbook which will add in any new row labels that keep getting added to your pivot.

What do you mean by adding new row labels that keep adding to my pivot? I thought you mentioned I cant manually add new row labels? Do you mean I add empty data? - like Orange --0 in my data?

[Pichingualas]

The PivotTable won't show an item label if it's not in your source data, so I think that's exactly what arlu is saying: if it isn't there, make sure it gets added to your source data so even when you have 0 oranges you will still see the label because you have an empty row called orange in your source data.

[arlu1201]

You can put labels in your data and show the value as 0 so that it shows up in your pivot. Or you can have a small macro that checks for any new row labels and adds them to a report. Whichever is easy for you.

[JosephP]

you can set the pivot table up initially with a row for every item then replace that data with your actual data and the old items will remain there.

[excelnewbie80]

Thanks for all your help. Joseph that's a great idea

[taccat]

Dear Joseph,

May I know how to set up "you can set the pivot table up initially with a row for every item then replace that data with your actual data and the old items will remain there. "? as mentioned earlier?

Tac

[JosephP]

I mean that you create dummy rows of data for each item you want and then after refreshing the pivot table you delete those rows. you also need to set that field in the pivot table to display items with no data

[taccat]

I have more 100 items and 10 different criteria to will create at least 1000 possibilities, may I know is there other way to create 1000 rows of dummy data in a faster way rather than creating it manually one by one?

[JosephP]

you do not need to enter every combination of items-just make sure that every item you want appears at least once in the data. honestly I don't know why you would always want 100 items to appear in a pivot table which is supposed to be a summary of your data