Postcode appears in two areas

[excelstar1978]

Hi,

I have a list of postcodes which appear in two areas within the UK.

I need to be able to pivot this data so I only see the postcodes which appear in two areas. I also need to see which areas these are in.

For example the postcode of CH 1 4EF appears in two areas and shows this on the pivot table but I am struggling to separate this out to only show the postcodes that appear in two areas.

Thanks

Paul

[CK76]

For this, you'll either need to add helper column to source table or take advantage of Data Model based Pivot Table.

My recommendation is to take advantage of Data Model based pivot table.

1. Select your data range. Insert -> Pivot Table. Make sure to select "Add this data to the Data Model" in the dialog.

2. Add Post Code to row label. Area into values field.

3. Click on 'Count of Area'. Value field settings. Summarize value field by... select "Distinct Count".

4. Filter row labels based on Value field = 2. Then add Area to row label.

See attached.

[ChemistB]

It's probably possible using Calculated Pivot Table Fields but I went outside the PT.
First, I modified the format to Tabular and "Repeat all Item labels" (Design tab> Report Layout)
Then in C4 copied down

Please Login or Register  to view this content.

Then apply a filter to that column and select "2"

EDIT: I like CK's solution. I had never used the "Distinct Count" functionality.

[excelstar1978]

Both work brilliantly but I went with the Distinct Count. Excellent work! Thanks

[CK76]

You are welcome