• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy certain columns from a table to new sheet for summary

Kiran2012

Kiran2012

New Member
Hi Experts,

I have a table with large data. Say Project details. I have to create a chart with values of some columns. Two columns are - Vertical , Horizontal. Under Vertical col, many verticals like BFS,COMM,LIFESCIENCE etc are there. Same for Horizontal col also
There are another 3 columns (documentation status, testing status,compliance status)
for creating chart, i am creating a new table with total of 4 columns
1.- Type - Vertical or Horizontal
2.-Name
2.- Doc Status
3.-Test Status
4.-Comp Status
Am using Countif() function to get the total counts.
=COUNTIFS('Compliance Data Sheet'!W:W,"BFS",'Compliance Data Sheet'!N:N,"Approved")

Now the problem here is when the data is populated, some may update as Completed/not completed instead of Approved/not approved.

Is there any way to create a copy of the table but only 4 columns? which will update dynamically when original data changes. Cant upload file(from office)
 
Hello,

I'm not quite sure what the question is. Do you need to include the "Completed" rows in you Countifs? If so, you can use

=COUNTIFS('Compliance Data Sheet'!W:W,"BFS",'Compliance Data Sheet'!N:N,"Approved")+COUNTIFS('Compliance Data Sheet'!W:W,"BFS",'Compliance Data Sheet'!N:N,"Completed")

Or you can use a Find and Replace operation to replace "Completed" with "Approved" and then run your original formula.

If that is not what you need, please provide more detail about the need to create a copy of the table.
 
Hi Teylyn,

Thank you.. Its working.
Why i need to create a copy of the table - table is dynamic. As the user enter the data, chart should change.
For this, i am creating a separate table with the completed & non-completed count
Screenshot attached(sorry cant upload an xl file) using your formula.

But as the size of table is unknown, how could i get this for all the unique counts ?
I added Type column with values Vertical & Horizontal. To use in slicer in chart

In Brief: From the large table, i want a chart which is having following filters(slicers) -
Slicer 1. Vertical / Horizontal
Slicer 2. Type of testing (Documentation, Testing, Compliance)
 

Attachments

  • screenshot.jpg
    screenshot.jpg
    281.2 KB · Views: 3
Instead of formulas you may need to use a pivot table. When you refresh the pivot table, it will update and include new unique values.
 
Hi SM & Teylyn,

Thanks for your time & reply....

attached is a sample sheet. You can see the summary table which i created. Main reason is to get the chart (with desired filters ).

The problem here i am facing is - The total number of verticals or horizontals are not consistent. in the summary table, the first 3 columns are manually entered. Is there any way to populate this table dynamically when the original data is changed?

Please look into this and suggest a way to get the desired output(chart)
thanks in advance
 

Attachments

  • Test.xlsx
    32.2 KB · Views: 3
I'm not sure I understand the bigger picture. All the columns in the chart have the same height. You may need to start your pivot table based on the original data. I can't really give better advice because I cannot see what you want to achieve. Maybe you need to step back a bit and explain the requirements. So you have data in this table in the Compliance Data Sheet. What question do you want to answer with a chart? What information do you need to see from the data?
 
Hi Teylyn,

All the columns in the chart have same height because of the dummy data i entered.
With chart, user should be able to :-
1. Filter vertical wise and horizontal wise
2. Filter the statuses separately

Like the slicers i used in the chart
 
No. All the columns in the chart have the same height because you use Count and that returns a 1 for each item.

You have not answered my question for clarification. Step back from your immediate issue. You need to explain what you want to achieve. Don't get tangled in approaches that you THINK are a solution.

Step back. This is not about "filter horizontal" or "filter vertical".

Explain what you want your users to learn if they look at the charts. Unless you do that, I cannot advise you on what formulas or techniques to use.
 
Users want to see how many "Completed/not completed" count in each sections.
They should be able to see how many under Vertical and how many under Horizontal.
Also from a user point of view, showing status count for individual section (doc status, test status & compl status) will be easier to understand.
 
I think am bad in explaining the scenario. Could someone help me in getting the chart as output from the given data?
 
You must log in or register to reply here.
Back
Top