Remember Me?

#1
August 31st 18, 08:23 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2018 Posts: 2
Best way to create a sorting table with non numerical values

Basically I have some data which is organised like this :

--------------------------------------------------------------------------
Date | Q1 | Q2 | Q3 | Q4 | Q5 |
--------------------------------------------------------------------------
08/18 | Yes | No | Yes | No | Yes |
--------------------------------------------------------------------------
07/18 | No | Yes | No | No | No |
--------------------------------------------------------------------------
07/18 | Yes | Yes | Yes | No | Yes |
--------------------------------------------------------------------------
08/18 | Yes | No | No | Yes | No |
--------------------------------------------------------------------------

I'd like to use maybe pivot table or something else to display the information like this :

-------------------------------------------------------------------
| Q1 | Q2 | Q3 | Q4 | Q5 |
--------------------------------------------------------------------------
Yes | 3 | 2 | 2 | 1 | 2 |
--------------------------------------------------------------------------
No | 1 | 2 | 2 | 3 | 2 |
--------------------------------------------------------------------------

and use the date a sorting mechanism to dynamically change the output

How can I achieve this? Any tips or ideas?

#2
September 1st 18, 12:43 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 148
Best way to create a sorting table with non numerical values

To make an example, I put the input data in A1:F5.

The result data will be in A8:F10. I filled in A9:A10 with Yes & No; B8:F8 with the Qs.

In B9, I put
=COUNTIF(B\$2:B\$5,\$A9)
and copied it into B9:F10.

So far, that got the result specified in the post.

Then I tried to understand what "use the date a sorting mechanism" means here. Sorting A2:F5 by date leaves the result unchanged, which seems reasonable, but I suspect something more is intended here by the mechanism.
#3
September 4th 18, 12:48 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2018 Posts: 2
Best way to create a sorting table with non numerical values

For calculating the results this works well thank you.

For the date I was looking for a way to have a field/button that could help me display either : All date, 07/18 or 08/18 (since these are the dates I have for now, but will eventually have more to account for).

This way I can generate monthly reports or annual ones.

On Friday, August 31, 2018 at 7:43:39 PM UTC-4, zvkmpw wrote:
To make an example, I put the input data in A1:F5.

The result data will be in A8:F10. I filled in A9:A10 with Yes & No; B8:F8 with the Qs.

In B9, I put
=COUNTIF(B\$2:B\$5,\$A9)
and copied it into B9:F10.

So far, that got the result specified in the post.

Then I tried to understand what "use the date a sorting mechanism" means here. Sorting A2:F5 by date leaves the result unchanged, which seems reasonable, but I suspect something more is intended here by the mechanism.

#4
September 4th 18, 08:56 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 148
Best way to create a sorting table with non numerical values

I was looking for a way to have a field/button that could help me
display either : All date, 07/18 or 08/18

Here's one way, starting with the input data and result data arranged as before.

Step 1: Set up filtering by selecting A1:A5 and using
Home Editing Sort & Filter Filter
(I use Excel 2010; other versions might vary.)
This enables either viewing all rows or selecting rows by date.

Step 2: In G2, enter
=SUBTOTAL(103, A2)
and copy down to G5. This returns 1 for visible rows; 0 for hidden rows.

Step 3: In B9, I put
=COUNTIFS(B\$2:B\$5, \$A9, \$G\$2:\$G\$5, 1)
and copy it into B9:F10. This limits the counting to visible rows only.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Bruce Excel Discussion (Misc queries) 4 October 22nd 08 09:20 PM Learning Excel Excel Discussion (Misc queries) 2 April 20th 07 08:32 PM Wynn Excel Worksheet Functions 3 November 18th 06 01:08 AM Smohrman Excel Worksheet Functions 9 April 7th 06 07:58 AM feirin Charts and Charting in Excel 2 February 1st 06 03:45 AM

All times are GMT +1. The time now is 01:52 PM.