Pivot Table - Count data From Multiple Columns

Hi,

Need to take 'count' of data from multiple columns and show on Pivot Table

Hi All,

Have a spreadsheet that looks as follows:

Battery No : Battery No : Battery No

5 : 8 : 1
3 : 9 : 5
1 : 5 : 3

Need data to be displayed on a pivot table with count of number of times each number appears
eg. Total of 5 = 3 times; Total of 3 = 2 times etc.

Got it to work for 1 column but when I use multiple columns, total is of individual columns rather than a collective count for each piece of data.
Would like pivot to show data from all "Battery No" Columns to reflect as a column and the corresponding "Cumulative Count" to show in adjacent row

Thanks - PS: Fairly Illiterate in Excel

For a Pivot you would need to transpose your data set first I'm afraid.

If your data is really numeric you could use the set as is by using formulae to generate the frequency table

Assume your matrix is A1:C4 (headers row 1) then:

Please Login or Register  to view this content.

Yes, post a sample file that accurately reflects your present set up - within that file outline also the "desired" results

I will try and look at this at some point in depth but the fact you intend to store each day on a separate sheet and have separate files per month basically means whatever approach you adopt it will be cumbersome.

It is always best to store data in a coherent tabular manner - to think along the lines of database storage.

In a database for ex you would not have a separate table for each day - and/or separate databases for each month.

You would have a single database with a single table in which one field represented date value.

In reality you should be storing your data along the lines of:

Date | Mach No. | Time In | Batt Out | Batt In | Time Out | Elapsed | Charger No. | Attendant | Comment

| denotes new "field" (column)

so for repeat changes for a given machine that machine would have 2 transactions (rows) for that given date (for 3 you would have 3 transactions/rows)

That said...

I appreciate that from a data entry perspective having single sheets per day with 1 row per machine might well be the only practical solution from your perspective.
When it comes to conducting analysis on the data you would be best served putting code/approaches in place that consolidate the daily sheets into a single data set as outlined.

From that single set you could generate a multitude of Pivot Charts / Tables etc with ease and efficiency.

FWIW, I don't believe (personally) that Multi Consolidation Pivots are viable in this instance - you will need to consolidate first.

Attached would be the earlier suggestion "in action"

A button on one sheet that consolidates the daily sheets for sake of subsequent analysis - the Pivots will update as part of the process.

This is not bullet proof by any means and assumes a consistent layout across all daily sheets.
I'm afraid approaching this as you have things won't be straightforward whichever route you take.

hi,

i put your solution on trial and it seems to be working well. Thank you.
I would also like to analyse the 'time in' column from the data sheet you created to track the no of batt. changes done per hour per month
eg. 11:00 to 12:00 = 50 batt changes
12:00 to 13:00 = 23 batt changes
02:00 to 03:00 = 11 batt changes etc.
Is this possible on a pivot chart? I've attached a workbook

Thank You

see if the attached is along the right lines...

FWIW I'd make the point that the way you're calculation Duration columns on the daily sheets may cause you problems
(generally better to store time values as Time rather than Decimal hours etc...)

Originally Posted by Ashraf1

...on the pivot table, I've introduced a chart to count the no of changes/day but I can't get the horizontal axis on the chart to reflect a 'day' instead of a date

Assuming you will have more than 7 days realistically you will want to add a Column at source to hold the "ddd" value and use that in the Pivot as Row Label.

Originally Posted by Ashraf1

I've used a circ ref formula to create a time stamp for 'time in' column, everytime data is entered into 'attendant' column....this works fine but has messed up calculations for 'time elapsed' and 'downtime' columns.

New thread for the above please.

Sorry, wrong post...

Originally Posted by Ashraf1

...would only need 7 days ie sun to sat. Pivot to show count of changes on Mondays;Tues etc for a month.

I'm not sure I made my earlier point very well.

If you have greater than a weeks worth of transactions (ie > 7 days) then your approach won't work.

You can not (AFAIK) group a Pivot Table by the Weekday (Mon, Tue) of the underlying Date values.

You will need to create an additional column at source that stores the weekday [=TEXT(date,"ddd")] by transaction and use that new column as the Row Label in your Pivot.

You needn't store Day for each transaction - simply add to the consolidated "Data" sheet as part of the VBA consolidation routine

In your sample remove "Day" columns from 01 March sheet.

Modify VBA to below:

Please Login or Register  to view this content.

Sorry about that !

Change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Based on the last sample you also need to change value of C_COLUMNS from 9 to 8 as you have 8 columns per instance (excl. machine number)