How to Auto Create Tables from the Master Table ?

[SKumar]

Hi,

This is a simple problem for someone good in Excel,but looks very complicated to me. Here is the Problem Statement.

There is a Master Sheet with data as below (I've replaced with grocery instead of actual business sensitive data)
C1 Jan Feb Mar Apr
R1 Apple $10.00 $11.00 $10.00 $13.00
R2 Banana $11.40 $12.40 NA $10.40
R3 Orange $4.50 $9.50 $4.50 NA
R4 Citrus NA NA $41.50 $24.50
R5 Mango $50.00 $20.00 $50.00 NA
R6 Grapes $12.00 NA $10.00 NA
NA means it's not available during that month.

Now in the Next sheet, the data needs to be displayed based on the Month i choose(from a list), for example, if Jan is select, then Column C1 will be displayed except for Cirtus since it;s not available in Jan , if Feb is chosen, then Citrus and Grapes should not be visible.

If i'm not clear,i can make an excel and share.

Thanks in advance for your help.

 

[Chihiro]

It's easily done using Pivot Table.

If possible I'd organize data differently and use Pivottable or Advanced Filter to extract info.

See attached sample (Value filter set for Sum of Amount <> 0).

Pivot1 uses your original structure and requires manually moving Fields for each month from Values. And the filter will need to be reset each time.

Pivot2 uses restructured data and can have Month as filter at the top. And Value filter will be retained.

 

[SKumar]

Thanks Chihiro for your quick response. I tried Pivot table, but was not able to change the label "Sum of" or "Count of". And even if i change once, it gets reset everytime i remove and add a month to view. Few questions:
1. Is it possible to to have just the month in the Values box instead of Sum of/Count of ?
2. With reference to my attachment, I added another column to find the most expensive value. Is it possible to display in the next column on which month it costed that value,and if it's more than one month then display with comma. I've written there manually what i'm trying to do.

 

[Chihiro]

Here's another version using advanced filter method.

Pick month you want in B4. It will update C1 & C7 with formula.
Press "Run" and it will run small macro to pull info.

If you google "Advanced filter tutorial" there are many sites/videos that explain how.

1. You will need to restructure your data like Pivot2 data range

2. Not something Pivot is meant to do. Nor is it easy to do using formula since it requires concatenating multiple strings. Let me think on this one.

 

[Chihiro]

Here, try this one.

 

[SKumar]

Thanks Dude, I just saw it with some Macros. I've never written one, but would try to decipher and apply to my code.
Thanks again.