Pivot Table problem

[hollies]

Hi I have a pivot table that is not acting how I want it and I cannot fathom out the problem

It is a simple PT at the moment with 4 columns, see attached.

The main table has 13 Columns and several hundred rows and is a record of the places we have been to.

Some places we have been to only once and others up to 18 times over the years.

The pivot table is to show only the last time we visited a place but then I want another column to show the Next Time we are heading there.

From my attached sample table you can see that Malvern is just showing (blank) and that Guildford is on 2 lines showing the Next Date and (blank) below it. The Next Date entry is correctly in line with the Max Of Date (2015) (last time) but the (blank) is in line with an even earlier time we visited (2013).



Malvern was identical but is just showing (blank) on the one line because I have removed the Next Date from the main table. If I double click on the Malvern row, the (blank) disappears along with the border lines leaving just white space.

Over 810 of the other rows are perfect but I cannot get to the bottom of why these couple of places should be like this.

I have checked all entries for accuracy, where a place as more than one entry, I have actually copied the first entry to the following cells so there are no typos or extra spaces anywhere, that I can find anyway.

Any help on this would be appreciated.

Thanks Rob

[xor]

I don't think the attached file (without underlying data) enables anyone to help.

[hollies]

Ok

Here is a cut down version of my table.

In this example, I am trying to show with a PT the last date that we visited a particular place. Then if a new date is arranged, the Next Date column will be added to.

The Pivot Table is exactly how I want it except for the following.

It shows (blank) in the empty date field. I don't want this but cant work out how to stop it, I just want a "blank" cell.

Whenever I add a Next Date to the list then the PT shows two "Last Dates" for the venue. See Forum Malvern and G Live, Guildford.

It appears that the PT is displaying the Last Date with the Next Date and also the Last Date with the latest "Blank" Date.

Is there a way round this to display it as I want it, just a single line with the 2 dates side by side, the next one either empty or with the next date added.

Thanks,

Rob

[xor]

I don't know how to get the pivot table react as you want but you might want to take a look at columns E:G in the attached where I have tried to solve it by means of normal formulas.

I have converted your table in Sheet1 to a range but you can convert it back to a table if you want.

[hollies]

Many Thanks XOR. That is exactly how I want it.

I was actually advised to covert the range to a Table. If this is not a matter of importance I will stay with a range.

Your example has allowed me to move my next date column after the last date in the PT. Something I was trying to achieve but failed.

My file proper has over 800 rows and 15 columns so I will try to replicate your formula in that. I certainly don't understand them but will try to work through it.

The only strange thing is Day (Column B) has converted to Scandinavian language, I believe.

Thanks again.

Rob

[xor]

@hollies

I am glad if you can use it.

Be aware that the formulas in column E and F are so-called array formulas. Array formulas are special in that they require to be entered a certain way. Where you normally just press Enter when you finish a formula, an array formula must be entered by holding down Ctrl and Shift before pressing Enter. If you do it correctly Excel will automatically put braces {} around the formula. Do not try to enter these braces manually. Excel will then think it is a text string and not a formula. When you have entered one array formula correctly you can copy it to other cells as you normally do.

I do not understand what may have happened to column B in Sheet1 (Day). I surely use Scandinavian settings but I wouldn't have expected that should influence one with US settings.

I attach a file PivotTest_3 where I have converted Sheet1 back to a Table and adjusted the formulas accordingly.

Please write back should you get problems in adapting the formulas to your actual file.

[hollies]

Many thanks XOR

I have successfully transferred the formulas to the proper table and it has worked a treat, almost!

The information now shows the last date of every visit we have made in the last 20 years.

How do I now filter that information so that I can select just one Country, or the UK (4) or Australia for example.

I also want to sift out only those entries in Note2 that match certain criteria, ie Yes, Poss, Good etc. I don't want the Overseas and No entries to show in this particular list, but I might next month.

I found this worked well in the pivot Table.

Is it a case of using a pivot table for some columns and then bringing in the formulas in other columns to get them right.

Thanks

Rob

[xor]

Maybe you can use the attached.

[hollies]

xor

Brilliant, you have been exceptionally helpful and I appreciate it.

I am learning so much about Excel and it's hidden capabilities (to most people anyway).

Having used it for many years, I have never got passed the basic table stage and although I knew there was much more to it, because I have not really needed it until now, I have not bothered to learn the more advanced features.

Thank you once again.

Rob