PDA

View Full Version : Report on a Count of Colours Cells by Unique Row and by Column.



psionic
06-03-2008, 08:47 PM
Hi guys me again :hi:

:motz2:A really,really tricky and definitly frustating VBA solution for me to solve a problem. :banghead:

Got a spreadsheet with over 1000 rows of data with 20+ columns of data (from a database) and I have coloured the cells according to some administrative decisions or criteria.

* If someone can show me using Pivot tables for coloured cells would be great, however I'm sceptial that there isn't one available. I use Excel 2002 not 2007, mind you. *

Anyway, now I wanted to produce a (pivot sort of like) report listing by row showing the different colours (I used 4) and another listing by column showing the different colour ( I used 4).

So for me to discuss this subject clearly, let's say I create a fictional speadsheet of using RANDBETWEEN(1,100) function as data and contains headings like Name, Far, Near, Small and Big. Also contains a row describing the data so Joe, John and Jess will do. NOTE: Duplicates names are included so I also need to find unique names for the report when processing rows as well. Columns are unique anyway, thanks heavens!!

A B C D E
Name Far Near Small Big
1 Joe 54 23 68 75
2 John 60 72 15 89
3 Jess 99 12 54 32
4 John 13 79 33 69

I made a sub routine using a For each cell loop of the entire range and change the background colour of the cell. So any number within tens (10-19) change background colour as orange and within seventies (70-79)change background colour as blue - Easy and straightforward.

My muscle tension rises when I want to make a report (of the results) that lists all the colours for the above sheet including white (the default colour) with a total processed so I can see a some sort of distrubtion in what colour dominates the rest.

By Row
Name Orange Blue White Total
Joe 0 1 3 4
John 2 2 4 8
Jess 1 0 3 4
TOTAL 3 3 10 16

By Column
Field Orange Blue White Total
Name 0 0 4 4
Far 1 0 3 4
Near 1 2 1 4
Big 1 0 3 4
Small 1 0 3 4
TOTAL 4 2 14 20

Any assistance or clarification would be a smashing thing. :bow:

My previous attempts in doing this problem was a make a class that is put into a collection but found out I didn't know how to update an object in a collection so I gave up on that! GRrrr!

I thought of using dynamic multidimensional array but didn't know how I could seprate the colours while processing the rows knowing I need unique rows.

Hence my plea!

With thanks and warm regards,
Psionic

mikerickson
06-03-2008, 09:20 PM
Instead of counting colored cells, why not test the condition that determined the color?

=COUNTIF(A:A,"<20")-COUNTIF(A:A,"<10") is easier than counting orange cells.

mdmackillop
06-03-2008, 11:55 PM
Some useful advice here (http://www.xldynamic.com/source/xld.ColourCounter.html)

psionic
06-04-2008, 12:07 AM
Thanks for the comments so far, both are useful but does not serve the purpose I'm after.

I'm looking for a VBA macro solution instead of tweaking with functions so the series of steps I do at work are fully automated quickly.

Psionic

mdmackillop
06-04-2008, 12:11 AM
Can you post a small sample and desired solution?

Bob Phillips
06-04-2008, 01:45 AM
The link Malcolm provided gives you a fundamental procedure to do the coding. You can use that within your VBA to build a table of whatever results you want.

It does provide an example workbook usage, but it is not constrained to that.

psionic
06-05-2008, 04:43 PM
mdmackillop,
I'll create a spreadsheet that describes what I mentioned eariler shortly.
Folks,
I'll be busy for a next few days so pardon if I don't reply straight way.

Thanks and warm regards,
Psionic.

psionic
06-15-2008, 05:09 PM
Hi Guys,

I'm back.

Here is the sample spreadsheet showing the problem above more clearly.

Thanks amd warm regards,
Psionic

Bob Phillips
06-15-2008, 11:53 PM
So have you tried the link? Your example shows a requirement not a problem, MD gave you a solution ... try it.