Avoiding Pivot But Need Extract a Report

[JennNnc]

First, let me admit I am an Excel novice. I am self-trained and I spend time watching videos and digging. Which, of course, led me here. I have a spreadsheet with several thousand rows of data. Here is what I'm trying to do:
Goal: I want to send a report to managers monthly without sending them the entire spreadsheet. So I want to create a report that I can extract out of the primary spreadsheet so that I can send it separately from the primary spreadsheet ( I do not want to send the spreadsheet with thousands of pieces of information out).

The spreadsheet has columns A through AD, but I only want the data in columns B, D, E, F, J, L M, and N for my report.

Additionally, Column D is a "Status" Column, it has 4 different statuses in it. There is only 1 status I care about, I do not care about the other 3. I want to ignore "Cancelled", "Closed", "Completed". I only care about "Need Device Info".

This is something I will be running regularly, every month for distribution in a manager's meeting.

These are test devices that have to be tracked by the company the users are required to report the device serial number and IMEI number upon receipt of the testing devices. We generally have to chase down the information. So I'm trying to create a report to send out to the managers letting them know the outstanding devices for their department that we have been chasing and have not been getting any feedback or assistance on. It will tell them
Column B: The order number
Column D: Status (Need Device Info)
Column E: Date (of the order)
Column F: The name of the requestor
Column J: Manager Name
Column L: Business Group
Column M: Brand of device
Column N: Model of the device

I'd like for the post-report to be able to be manipulated afterward. For that I mean, it would be nice if the Managers could filter on their names so they can only see their group and filter everyone else out.

Everything I have searched on tells me to run Python or C+ which is beyond my knowledge. A pivot point doesn't quite work, while it gives me the information I need, I need to strip it away from the main spreadsheet and I lose data when I do that.

Any help or advice to point me in the right direction, I'm willing to do the work and learn what I need to do. I just need some guidance on where to go. I've been digging for over a week now. I think maybe I'm just looking for the wrong thing or don't know what I should be looking for.

 

[Skovgaard]

Hi and Welcome to the forum

I'm thinking if it was me, doing the same task every month, I would record a macro, that could do it for me.
Do you know how to do all steps manually? If so, try and record the steps (you don't have to know VBA to do this) and see how it works out.

/Skovgaard

 

[JennNnc]

Hi and Welcome to the forum

I'm thinking if it was me, doing the same task every month, I would record a macro, that could do it for me.
Do you know how to do all steps manually? If so, try and record the steps (you don't have to know VBA to do this) and see how it works out.

/Skovgaard

I'm not sure I understand. I understand the recording of a macro part. But how do you create a report that literally creates a separate excel file that strips only the columns you want and filters specific data out of those columns? I think the answer to the question of "Do you know how to do all the steps manually?" is no. I know how to record a macro, but what I don't know how to do at this point, is how to manually create this report outside of using a pivot which I don't want.

 

[Skovgaard]

Sorry, I might be a bit uncertain what you want then...
Do you want the Pivot functionality, but without sending the underlying data?

If that is the case, you can then create a Power Pivot (get external data, only create connection and add to the data model) instead?

/Skovgaard

 

[JennNnc]

First, thank you for welcoming me
Second, again I'm still a novice and self-training, so I appreciate your patience while I try and explain.

1) I need to send a report of the data completely independent of the master spreadsheet (I do not want the master going out to individuals in any capacity)
2) I like the grouping of the pivot, but I can't use the pivot because it keeps the data tied to the master spreadsheet and if I copy and paste it into a new file I lose integrity/ data
3) I would like a format where the managers would be able to filter on their names so that they could see only their information for their teams because there will be a lot of information on the report.

 

[Skovgaard]

My best solution I can think of (with my knowledge to Excel), is to use PowerQuery and PowerPivot, to create some kind of dashboard. Then you don't need to send the fact-data with the final spreadsheet.
There is a lot of Youtube tutorials that explain and help on this topic. During the pandemic (working from home), I did a lot of self-training like this, I can only recommend it

Perhaps other users, can see a better (other) solution, but that would be the direction I would go, if it was me.

/Skovgaard

 

[JennNnc]

My best solution I can think of (with my knowledge to Excel), is to use PowerQuery and PowerPivot, to create some kind of dashboard. Then you don't need to send the fact-data with the final spreadsheet.
There is a lot of Youtube tutorials that explain and help on this topic. During the pandemic (working from home), I did a lot of self-training like this, I can only recommend it

Perhaps other users, can see a better (other) solution, but that would be the direction I would go, if it was me.

/Skovgaard

I will look this up! Thank you!