Link to home
Start Free TrialLog in
Avatar of Cook09
Cook09Flag for United States of America

asked on

Filtering - Visible Rows

Attached is a Workbook where Column B (Table1 Worksheet) is filtered and the results for Col A can be recorded in P1, Q1, R1, etc.  Currently, it's a bit of a work around to display the values of Col A, C, and F (from a filtered Col B) to be displayed on the Main worksheet.

Is there a better way to display the filtered rows in the Main worksheet?

Cook09
Filtering.xlsx
Avatar of Professor J
Professor J

what is the version of your excel?   if you have excel 2010 or above you can do this with power query.
Have you tried Pivot? Tabular Form
Select Sender from Report Filter.
Filtering_Pivot.xlsx
Avatar of Cook09

ASKER

ProfessorJimJam...I have Excel 2016...Don't know Power Query

Shums...Part of what I'm going to do...in a larger context...is use the value from Column N (Inbound/Outbound) to decide if the data starts on Row 6...or Row 13.  I assume the Pivot could be done to separate the two.??  Not quite sure?
Avatar of Cook09

ASKER

I was thinking of both Inbound / Outbound as there could be both...
Filtering_Pivot_v1a.xlsx
Cook09

i have made a solution for you with a helper column in the table.

it works just fine.

no need for pivot table or power query.

please find attached.
Filter.xlsx
Avatar of Cook09

ASKER

Shums,
Why is it when I try to build a Pivot Table...all I get is the image from K2..L18?  The Field List and others are highlighted at the top
Filtering_Pivot_v1b.xlsx
Cook09,

When you create Pivot, it asked you range to paste in same sheet or new sheet, you would have selected same sheet and range K2.

Anyway, why you had to create Pivot, when I already created.

You should also try Professor's last approach. Formula would also work.
Avatar of Cook09

ASKER

Professor,
I like it, but only want the data to show if there is a value in..."A1"
Filter1.xlsx
Avatar of Cook09

ASKER

Shums,
I was trying to duplicate it on the main workbook...Yes, the Professor seems to be close to what I was looking for..
Another approach would be VBA,

Filter in Table1 and come to Main and press Refresh Data.
Filtering_Pivot_v2.xlsm
SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cook09

ASKER

Shums...I like it...

Professor...Is there a reason it only displays the first row?
Filter2.xlsx
Cook09,

I don't understand, why you want to do double work. First filtering in Table1 then in Main you want to extract few columns, Pivot can do the same thing, as you are filtering Column B in Table1 Sheet, directly filter in Pivot Sheet as per Sender, it will list the custom columns.
Filtering_Pivot_v3.xlsx
Avatar of Cook09

ASKER

I like your VBA code and can use it to populate the exact cells needed...but if I run into problems sharing it...corporate is really clamping down on potential security issues...I would like to know I can use the Professor's to fill in the same data..
You mean like attached?
Filtering_Pivot_v4.xlsm
Avatar of Cook09

ASKER

Yes...and that's what I'm currently using.  As the professor seemed to provide another acceptable solution, and could be used, did not want to slight him at all.  If I did, then the next time, he may not even look at a question I may pose...
Cook09
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cook09

ASKER

Excellent...Thanks
You're Welcome Cook! But Professor also deserved points.
Avatar of Cook09

ASKER

Can I change it?