Need Help with Tables and Filters

[Janice08]

Hi everyone, 

I am stuck in the different results from the same dataset in tables.

For example, when I filtered "Country" to "Australia", the other table showed “Japan", and the relevant tables didn't show corresponding dat

Seems they are not connected, but I don't know how to fix it.

Can anyone help? Thank you!

[#2]

@Janice08 It's difficult to judge from a picture, but it seems that you just filtered the first table on "Australia" and "Shipped" and it hides many rows on that basis. The first row displayed is 14. In your second table that row happens to be related to "Japan".

Otherwise, upload a sample of your file so that we can have a closer look at it.

[#3]

Hello,

Based on the picture you uploaded, when you filtered for Australia and Shipped in columns C and E, the table to the right "folded" hence you're seeing records for Japan. This is how filtering works in Excel when you have two or more Tables in a Sheet tab. If you look to the row numbers, you will see that they are in blue font color.

What you need to do is move the Table to the right to another sheet. Or if you have the new Dynamic Array Functions available in Office 365 Insiders and Monthly Channel (as of today), you can use the Filter function

[#4]

Hi, 

Thank you for your reply. I just uploaded the file, could you please take a look at it? Thank you.

[#5]

Hi,
Thank you for your reply. I just uploaded the file, could you please take a look at it? Thank you.

[#6]

@Janice08 

It's exactly what I expected. When you filter Australia and Shipped in the first table you simply hide the rows that are not Australia and Shipped. Then the rows in the other tables are also hidden. I guess you can make the summaries you want with pivot tables as demonstrated in the attached workbook.

[#7]

@Janice08 

This is unlikely to be of any immediate value to you but Office 365 has (or will soon have) a FILTER function.  I have never been attracted to playing 'peek-a-boo' with data for precisely the reason you have found; that is, hiding rows messes up any other content on the sheet.

The FILTER function allows one to select a subset of data from a data table and display it anywhere one chooses without affected the source sheet.

[For those who are confused by the expression 'peek-a-boo', it is an infant game in which people or objects are hidden from a baby to amuse it when they reappear.  It is part of cognitive development to realise that hidden objects still exist]

[#8]

@Peter Bartholomew 

One from Microsoft is still available https://www.microsoft.com/en-us/p/peekaboo/9nblggh0cwlq?source=lp&activetab=pivot:overviewtab

[#9]

@Sergei Baklan 

Wow! And I was impressed by your knowledge of Excel.

That pales into insignificance compared to your ability to find this within the Microsoft portfolio!