Updating analysis for new data points

brinks19

New Member
Joined
Sep 21, 2017
Messages
10
Hi -

Struggling with what I've thought would be a simple exercise. I've got a data set that will constantly be updated with new record types and options that are not necessarily known that this point. The problem is I can do the analysis now, but have to recreate / update all the reports when a new "name" or many other factors are introduced. There's no standardization for the names that come in, which is the part that I need to solve for in my update strategy. All columns are static, but the field options could change essentially. I'm now able to get all data points into a table and was hoping that I could leverage pivot tables to complete the analysis and update, but am having problems. The end goal is trying to associate how many records were modified by name, for example. I'm able to do it in pivot tables using the % to total piece, but only if both are showing. So i would have a % for modified and not modified for each name, which is not necessary especially when I need to quickly cut the data in a handful of ways and graph each. Ideally I would be able to show the % modified by name and it would include a new row each time a new name is introduced. Table below is a bare bones example of my data set with the expected output further below. Any advice / help is more than appreciated!

NameDateModified
A
2/1/2020​
Yes
A
2/1/2020​
Yes
B
2/1/2020​
Yes
B
2/1/2020​
Yes
B
2/1/2020​
No
B
2/1/2020​
No
B
2/1/2020​
No
B
2/1/2020​
No
B
2/1/2020​
No
B
2/1/2020​
No
C
2/1/2020​
No
C
2/1/2020​
No


Expected Output for analysis / graphing (months would be across the top since the data set spans years)
NameModified (%)
A
100%​
B
25%​
C
0%​
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top