Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Unique count within a pivot table Results

I know a pivot will display the unique variations but can it count the variations.
example if
fruit
3 apples
3 oranges
2 bananas
vegetables
3 brocoli
2 spinach

how can the pivot table return
fruit 3
vegetables 2
?????????????????????????????????????

My spreadsheet (about 24,000 rows) contains order numbers and promotion codes. I need to count the unique orders within each promo code and show just the PromoCode with the totals. The Sub-Total function doesn't seem to allow for "Count Unique records". I tried using an "Array" formula but I need to adjust it for each group and even then the PromoCode doesn't appear on the same line. I would like to avoid using a Pivot Table.

Sample Spreadsheet Data:
ORDER# PROMOCODE
123456 B100
987654 B100
555555 B100
444444 B100
111111 C700
222222 C700
999999 J300
777777 J300
666666 J300

Should Yield:
4 B100
2 C700
3 J300

Can anyone help me figure this out?
Thanks,

I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.

Thanks in Advance,

Margs

Afternoon all,

Ive been rattling my brain on this problem all morning and Im afraid the answers are coming along rather slowly.

Basically I have been given an Excel workbook with two sheets within it. The first sheet represents pipework, there is around 8.5k rows and 10 columns.

The other sheet represents parts of the pipework, such as the bends, elbows, tees etc. This sheet has over 37k lines.

Now what ive been asked to do is add another worksheet which shows the lines, but also the number of components on the lines.

So an example on the new sheet would be line number 1 would have 5 bends and 2 elbows. Rather than have line number one on Sheet1 and then the components spread over 7 lines on Sheet2. So it basically skips out all the details about each component and just counts how many there are for each line and adds that to a new column on the new sheet. I hope that made sense!.

Unfortuantly to distinguish each unique line, you need to compare data from three columns. So the only way I could do this with a lookup of sorts would require me to use arrays, or at best, the sumproduct. As you can imagine this simply isnt possible and Excel quickly runs out of memory and waves the white flag.

My next idea was to attempt to use a pivot table. Unfortuantly though to represent the data with all the right columns, I need it to look at both sources.

If this is actually possible, then someone please let me know, as Iv not come across a way of doing it.

So my next step was try and get Excel to sort it for me using VBA and SQL. So far I am able to collect the main data from sheet1 and add that to the new sheet. I am also able to count how many components are associated with each line and add that to the new sheet as a seperate column. If it is important, this was done using an inner join. This at the moment is not accurate as it only matches up one of the criteria of the three it needs to be doing.

My next step now is trying to break that count down, so it shows the different types of components, rather than bundle them all together on one column. This I seem to be having difficulty with. So im basically after it creating a few columns that will do something along the lines of
'Count(Type) = 'Bend' etc.

My plan once I can seperate this data into seperate columns is then to try add the rest of the criteria.

So, if anyone is looking at this and thinking 'god damn dude, why the heck dont you just do a #insert super solution here' then please let me know!.

Else if someone could help me along with some SQL statements that grab data from two sources, does muliple counts on the same field but with different criteria, then please reply .

wow and I went for a shortened version.

I am working on a pivot table that I need to indicate # of employees per
status of active or termed. Within the range there may be multiple entries
for one individual how would I get a count of employees by status
Example of current Pivot:

Emp Status Status Bus Unit Site Total(this would
be 3 of emps)
A Dist D. Sales Denver 0

Hello all, I'm a new visitor to these forums, I'm here because I'm stuck trying to solve an excel problem.

I've created a workbook that functions as a log to record issues that I've had with companies I work with. Generally speaking it is 3 tabs:The data page, which I call the "Issue log".A lists page, for validation lists "Validation Lists".A issue summary page, formatted for the external companies to see what I've logged, called "Company Summary".
My concept was to enter data into the log, and then I've set up my company summary page so that outside of the print area I select the company I want the summary to be for, and the date range (starting date & ending date) for the summary. Then (theoretically) the summary would auto-populate with each of the individual issues that I've entered into the log that match those criteria (company & date range).

My problems are:I can get it to pull the reference numbers for the right company from the first occurance of the company's name to the last, but if an issue is in the middle of that data out of order, it includes that issue as well.When I try to sort the data to fix that problem, it changes the reference number (because it's counted based on order of entry), and in some cases the other company's already have that reference number.

I've created my first column in the log as a concatenate that is formatted as "2AA1", where '2' is the total sequencial count for the log, "AA" would be a 2 letter abbreviation for the company in question, and "1" is the issue count just for that company. The log essentially looks like this:

1AA1 - 6/1/2011
- Issue notes
2BB1 - 6/2/2011 - Issue notes
3AA2 - 6/15/2011 - Issue notes
4CC1 - 6/10/2011 - Issue notes
So basically on the next tab I would select something like:
Builder - *AA*
Start Date - *6/1/2011*
End Date - *6/31/2011*

And I'd like it to pull the specific reference numbers in that range that match my criteria so I can vlookup the other data I need to populate the rest of the form.

I've tried SUMPRODUCT, VLOOKUP, arrays, Pivot Tables, INDEX/MATCH, SUMIF, COUNTIF, etc. I have literally spent the last 12 hours consecutively trying to figure this out on my own, and I just can't make it work. I know one of those methods (or more) can work, I just don't have the skills to do it.

Does anyone have any advice for me? I really appreciate it.

Thanks so much...
-LawC

I have read several posts about this, but I either do not understand the solution or it solutions described do not fit my need (as best I can tell)

I have a table of about 20,000 records. I attached a sample portion of this data. The second tab shows how I need the data to come out in a pivot table.

There are two fields of interest- Actual Plant and Product Structure. The records are production entries so there will be many duplicate instances of both Acual plant and product structure.

I want to create a pivot table shows me all the product structures used at that plant. I want to count each of these as 1 so that when completed I can total how many product structures a plant uses. See second tab.

I am thinking I need to do all this within the pivot table itself since I want the outcome to change as I reconfigure the pivot table.

Is this possible?

Hi

I am attaching two screen shots for a detail explaination.

As you can see the blue lines are Entry lines which have Lines and Grlines under it(as child)

If you see E column which has Journal Entry Number and F column which has User id column.

And within same the Journal Entry Number column there might be different number of users with different user ids.

Hence i have created a Pivot table for the first five columns for this case.

The 2nd screenshot i filtered the grouping level column that has only the Lines and Grlines(No Entry level) and all the Journal Entry Numbers such as MTX-10001158,MTX-10001163 etc and all the User ids that fall under its respective Journal Entry Number.

Here I want to know whether i can get the Distinct or Uniqe Count of the different User ids within the same Journal Entry Number column.

Example in the 1st screenshot that has MTX-10001158 Journal Entry Number has

USER95
USER94
USER95
USER94

in the line and grlines level.

so i want count=2 for User id USER94
and count = 2 for User id USER95 within the same Journal Entry using Pivot table and so on for all the Journal Entry Numbers.

That is Distinct count of different user ids within the same Journal Entry Number using pivot table and

if possible the sum of count of user ids distict count.(example count =2 for user94 and count for user95 is 2 so sum is 2+2=4)

Please help me with this query.

Thank you.

Hi

I am trying to obtain a data value within a pivot table. At the moment the table is based on 2 rows side by side – Origin and Destination with a sum of count - no columns are included.

In the following example I want to be able to obtain the “4500” value.

Example
AUS SYD 4500
BRS 112
MEL 2400
ADL 1800
EUR ITL 500

At the moment I am using this code:


	VB:
	
varMyVar(0) = .GetPivotData(.DataFields(1), .Rowfields(1).Name, _ 
.Rowfields(1).PivotItems(1), .Rowfields(2).Name, .Rowfields(2).PivotItems(1)).Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Currently The rowfields(2).pivotitems(1) method is just a collection of unique values based on field 2 and does not relate to field 1. i.e. ADL, BRS,MEL, ITL, SYD

I had the impression that using this code would enable me to locate any item within a pivot table and obtain its value without having the need to provide specific index which relates the value in the pivot table. In order to obtain the value 4500 I have to explicitly type “SYD” or its position within the array.


	VB:
	
varMyVar(0) = .GetPivotData(.DataFields(1), .Rowfields(1).Name, _ 
.Rowfields(1).PivotItems(1), .Rowfields(2).Name, “SYD”).Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a better way to locate items without knowing its index position?

My spreadsheet (about 24,000 rows) contains order numbers and promotion codes. I need to count the unique orders within each promo code and show just the PromoCode with the totals. The Sub-Total function doesn't seem to allow for "Count Unique records". I tried using an "Array" formula but I need to adjust it for each group and even then the PromoCode doesn't appear on the same line. I would like to avoid using a Pivot Table.

Sample Spreadsheet Data:
ORDER# PROMOCODE
123456 B100
987654 B100
555555 B100
444444 B100
111111 C700
222222 C700
999999 J300
777777 J300
666666 J300

Should Yield:
4 B100
2 C700
3 J300

Can anyone help me figure this out?
Thanks,

Hi!

I have a data source that contains client information with several different
attributes (which results in several rows of data for each client). I
created a pivot table that will allow users to filter and look at the clients
in a variety of different way, using the PAGE area within the layout of the
pivot. What I am looking for is a formula/function within the pivot that
will recount the number of unique clients depending on the filter selected.
Is there a way that I can do this within the pivot as I need to have this
count updated with each change to the filters?

Here is an example, I hope it helps:

City City X
State (All)
Line Line A
Sub Line (All)

Data
Product Industry Clients Sum of Clients
Product 1 Industry A Client A 8 would expect to see 1 client count
Industry A Total 8 would expect to see 1 client count
Industry B Client B 3 would expect to see 1 client count
Client C 3 would expect to see 1 client count
Client D 1 would expect to see 1 client count
Industry B Total 7 would expect to see 3 client count

I've hit a wall on summarizing some GIS data. I've tried searching
google and this group, and while I've found many posts detailing how to
count uniquely within a column, the situation I'm facing is a little
more difficult I believe, or perhaps I don't know the correct
terminology to describe it / search for. I've simplified my actual
data/situation for examples sake:

Suppose column A is "ID_Number" and column B is "Comment" (for example
sake "YES", "NO", or "NA"). I need to sum the number of times a
specific comment, say "YES", appears in column B, but only once per
unique ID_Number. Say ID_Number 1452 has 4 comments: "YES". "NO",
"YES", "NA". It should contribute only one "YES" to my count.

I've reached the limits of my knowledge about how to approach this. So
far I can make 1 more columns, column C. Column C contains a "1" if
this is the first time the ID number has shown up (obviously I must
sort my data by column A, ascending), and a 0 if not. I could run a
pivot table at this point using the "Comment" column and column C, and
get a sum of "YES", however this doesn't quite cover the situation.
There could be an ID_Number that appears, for the first time, with a
comment of "NO". Any subsequent "YES" in the comment column for this
ID, will not be recorded.

Simply because of time constraints, I don't want to have to write a
macro (I've had to learn a bit of VB for Office over the last few days,
mostly macro'ing Pivot Table creation and manipulation). Any idea if
this can be done without resorting to a (complex?) macro.

The general flow of the macro would go (I believe)

1. For each cell in comment column
2. If the comment = "YES"
3. Check if the associated ID_Num occurs in some list
4. If not add it to list
5. Finally sum the list

Any thoughts?

Hey so i've been teaching myself excel and have managed to knock up a nice pivot table but have run into what may be a bit of n00b roadblock.
Basically the data i get would be something like the following columns
UNIQUE ID POSITION TITLE DEPARTMENT LOOKUP CODE GENDER(M/F)

Using these columns I turn the lookup code into POSITION GROUP and create an MALE (1 if gender is M else 0) and FEMALE(1 if gender is F else 0)

So the pivot table columns would be:
DEPARTMENT- overall filter
POSITION GROUP- First row label
POSITION TITLE - Second row label
Count of UNIQUE ID- Value
Sum of MALE- Value
Sum of FEMALE- Value

This seems to work ok and displays the total number of men then the total number of women for each job title within each position group
But my problem is once i have the totals for male and female I need to have a percentage field next to each for percentage male vs female

This is what i really can't get my head around I would think i could create a duplicate column for each and then a percentage of function when displaying it but what do i choose it to be a percentage of in that case???

Any advice would be greatly appreciated!
Feel free to flame if this is a retarded question :P

Hey all,

I had a look around the forum to see if I was able to find my answer, unfortunately no luck.

I have a large list of data(5000+) rows all consisting of various columns, summarized into a pivot table

What I am trying to do is some type of custom grouping, that will group the messages I have with similar message beginnings and count how many of them are contained within that group.

For instance, a small example of what I have now looks like this

Canned error message: unique descriptor
Canned error message: unique descriptor
Canned error message: unique descriptor
Canned error message: unique descriptor
Canned error message: unique descriptor
Canned error message: unique descriptor
Canned error message: unique descriptor

Different canned error message: different unique descriptor
Different canned error message: different unique descriptor
Different canned error message: different unique descriptor
Different canned error message: different unique descriptor
Different canned error message: different unique descriptor

What I'd like to have,

Group of canned error messages | Count of canned error message
Group of different canned message | Count of different canned message

I'm able to do this by hand if I manually select the messages with the same prefix and add in a count function to the values column in my pivot table, however with over 5000 rows I was wondering if there was a more expedient process.

Look forward to hearing back from you!

Hi everyone,

I'm looking for help coming up with a formula that can tell me how many unique organizations there are within a given quarter... This is how my data is organized:

Quarter Organization
2011 - Q1 A
2011 - Q1 B
2011 - Q2 A
2011 - Q2 B
2011 - Q2 C
2011 - Q3 A
2011 - Q3 D

These are the results I need from a formula. Duplicates of companies across quarters are fine (for example, Company A is counted 3 times):

Quarter Number of Companies Represented
2011 - Q1 2
2011 - Q2 3
2011 - Q3 2

Could anyone help me write a formula that would give me the # of organizations represented in each quarter automatically? I can make a pivot table and manually count the number of organizations in each quarter but I know there's a simpler way...

Thank you in advance!

Hi,

first of all I would like to thank everybody involved in this forum answering questions and solving problems.
I have learned a lot from the posts and replies.

I have a problem in excel 2007 with pivot tables.

DATA
The underlying data is a list of items which have been created in the database and the actual date the item was created on.
So basically two fields, date (DD/MM/YYYY) and summary.
At the bottom of this post I listed some demo data

FUNCTIONALITY
I want to present this data in a bar chart, grouped by month, showing for the months where no items where created an empty bar, and to have the posibility to select a date range ...

WHAT HAVE I TRIED
I create the pivot table and set the values to Count of Summary and set the row label to Date.
This creates the pivot table with for every unique date a count of items on this date. Perfect

I want to see the data grouped by month so I right click one of the date fields in the pivot and select "Group ..."
There I select Months & Years and leave the grouping to auto.
This groups the items by month, however months with no items in the underlying data are not displayed.

To display those, I set on the field settings of the Date Row, the option under layout and print : "Show items with no data".
This generates for every month with no underlying items an empty row.

At this moment everything looks ok, but now I want to filter the pivot within a certain date range.
Therefore I select again "Group ..." and change
the "starting at" to eg 01/07/2010 (DD/MM/YYYY format)
& "ending at" field to eg 01/07/2011 (DD/MM/YYYY format)

The pivot table shows a grouping field < 01/07/2010, another field > 01/07/2011, and for 2010 all the months (jan to dec) and for 2011 all the months (jan to dec).

I however would expect that
- jan / feb / mar / apr / may / jun is not shown in the 2010 group
- aug / sep / oct / nov / dec is not shown in the 2011 group.

I have the ability to deselect via a filter the < 01/07201, and also eg 'jan'.
However when I deselect 'jan' via the filter it dissapears from the 2010 group (which is good) but also from the 2011 group (which is not good)

Is this something that is possible with pivot tables?

I control the underlying data as well, so if needed I can change the layout of the datasource.
However a workaround to create for every grouping a dummy empty record in the underlying data is not an option as this would generate over 1 million records.

Demo data

Date Summary
1/01/2010 Item 1
1/03/2010 Item 2
1/05/2010 Item 3
2/01/2010 Item 4
5/05/2011 Item 5
9/07/2011 Item 6
10/12/2009 Item 7
10/12/2010 Item 8
10/12/2011 Item 9
21/01/2010 Item 10
3/01/2010 Item 11
4/01/2010 Item 12
8/01/2010 Item 13
14/01/2010 Item 14
22/01/2010 Item 15

Hopefully everything is clear ...
If extra info in needed, do not hesitate to ask ...

Thx in advance,

Greetings ozgrid community,

I've attached the workbook I'm working on complete with a working word cloud / tag cloud generating macro. It uses chandoo's popular method. The two major tweaks I need to make are:

1) Instead of separating out each individual word I would like to just do counts of the number of times each unique cell contents are in the column. I think I will use a pivot table to accomplish this as that's how I do it manually whenever I need to.

This second thing I want to tweak is to adjust font colors based on the frequency (more frequently occurring descriptions set to Red and then work towards green for the lesser common descriptions).This is where I'm hoping I may be able to find some help, as I think I should be able to accomplish task 1 without too much difficulty.

I tried to attach my workbook for everyone's convenience but couldn't get it to upload correctly so my apologies for the lengthy copy and paste. I've commented the code including where I imagine the color tweak would need to be made or added. Although perhaps this will change if I'm including the entire descriptions rather than pulling each unique word from the descriptions?


	VB:
	
 DoAll() 
     
    Dim CloudData As Range 
     
    On Error Resume Next 
     
     'Asks user to specify which column of data they wish to summarize
    Set CloudData = Application.InputBox("Please select a range with the incident information you wish to summarize.", _ 
    "Specify Incident Information", Selection.Address, , , , , 8) 
     
     'If the user selects nothing then a message box reminds the user what they need to / should have done.
    If CloudData Is Nothing Then 
        MsgBox "Please select a column of information to summarize!" 
         
    Else 
         
         'If the user does select a column this message box tells them what they selected.
         'MsgBox "You selected: " & CloudData.Address(External:=True)
         
         'If the user does select a column a new worksheet titled "Frequency Tables" is added.
        Worksheets.Add().Name = "Frequency Tables" 
         
    End If 
     
    Dim rngCell As Range 
    Dim WordsColumn As Collection 
    Dim vntWord As Variant 
     
    On Error Resume Next 
     
    Set WordsColumn = New Collection 
     
     'Splits the contents of each cell to grab unique words and then counts the number of occurances
    For Each rngCell In CloudData.Cells 
        For Each vntWord In Split(Replace(Replace(Replace(rngCell.Value, """", ""), "]", ""), "[", ""), " ") 
            WordsColumn.Add WordsColumn.Count + 1, vntWord 
             
            Sheets("Frequency Tables").Activate 
             
             'Prints new unique words and updates count (number of occurances) on prior unique words
            With Cells(WordsColumn(vntWord), 1) 
                .Value = vntWord 
                .Offset(0, 1) = .Offset(0, 1) + 1 
            End With 
        Next 
    Next 
     
     
     'Sorts frequency table descending. *Only works with currently written code which places
     'words starting in Cell "A1"
     
     
    With Range("A1", Cells(Rows.Count, 2).End(xlUp)).Resize(, 2) 
        .Sort .Cells(1, 2), xlDescending 
         
    End With 
     
     
    Call CreateCloud 
     
     
End Sub 
 
 
Sub CreateCloud() 
     ' this subroutine creates a tag cloud based on the list format tagname, tag importance
     ' the tag importance can have any value, it will be normalized to a value between 8 and 20
     
     
    On Error Goto tackle_this 
     
     
    Range("A1").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Range(Selection, Selection.End(xlDown)).Select 
     
     'Selection.Name = "FrequencyTable"
     
     
    Dim size As Integer 
     
     
    size = Selection.Count / 2 
     'size = FrequencyTable.Count / 2
     
     
    Dim tags() As String 
    Dim importance() 
     
     
    Redim tags(1 To size) As String 
    Redim importance(1 To size) 
     
     
    Dim minImp As Integer 
    Dim maxImp As Integer 
     
     
    cntr = 1 
    i = 1 
     
     
    For Each cell In Excel.Selection 
         
         
         'If counter / 2 returns a remainder of 1 i.e. it's a word column then print that "tag"
         '
        If cntr Mod 2 = 1 Then 
            taglist = taglist & cell.Value & ", " 
            tags(i) = cell.Value 
             
             'Otherwise (remainder of 0) it must be a frequency count. Set importance(i) to that
             'frequency count and set max and min importance (frequency) accordingly
        Else 
            importance(i) = Val(cell.Value) 
            If importance(i) > maxImp Then 
                maxImp = importance(i) 
            End If 
            If importance(i) < minImp Then 
                minImp = importance(i) 
            End If 
            i = i + 1 
        End If 
        cntr = cntr + 1 
    Next cell 
     
     
     'Paste values in cell G1
     'Range("G1").Select
     
     
     'Ask user to select which cell they would like to place the tag cloud in
    Set CloudCell = Application.InputBox("Please select the cell where you'd like to place the word cloud.", _ 
    "Specify Word Cloud Destination", Selection.Address, , , , , 8) 
    CloudCell.Select 
     
     
     'Sets active cell value to 'taglist' and cell fonts to size 8.
    ActiveCell.Value = taglist 
    ActiveCell.Font.size = 8 
     
     
     'Starting at first character slot within the cell
    strt = 1 
     
     
     'Starting at tag 1 to however many tags are contained in the frequency table
    For i = 1 To size 
         
         
         'With active cell start changing font size of characters. Applies formatting to
         'the appropriate number of characters based on the length of the word (Len(tags(i)).
         '*To Change Color must somehow adjust the .ColorIndex portion
         
         
        With ActiveCell.Characters(Start:=strt, Length:=Len(tags(i))).Font 
            .size = 6 + Math.Round((importance(i) - minImp) / (maxImp - minImp) * 14, 0) 
            .Strikethrough = False 
            .Superscript = False 
            .Subscript = False 
            .OutlineFont = False 
            .Shadow = False 
            .Underline = xlUnderlineStyleNone 
            .ColorIndex = xlAutomatic 
        End With 
        strt = strt + Len(tags(i)) + 2 
    Next i 
     
     
     
     
     
     
    Exit Sub 
tackle_this: 
     ' errors handled here
    MsgBox "You need to select a table so that I can create a tag cloud", vbCritical + vbOKOnly, "Wow, looks like there is an
error!" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks a bunch for any help / advice you may provide. And sorry again about the lengthy code paste (I tried but failed to upload). Happy Holidays!

I have a series of date ranges and I'm trying to find a solution (ideally without pivot tables) to count the unique days within the ranges by month.

For example, for the following ranges:

1/10/2012 - 1/12/2012 (3 total days, 3 unique days in Jan)
1/12/2012 - 1/15/2012 (4 total days, 3 unique days in Jan, as 1/12 was already counted)
1/30/2012 - 2/2/2012 (4 total days, 2 unique days in Jan, as 2/1 and 2/2 would fall under February)

So for January, the total I'd be looking for was 8.

Had a bunch of false starts, but can't seem to wrap my head around this one. Any suggestions to get me going?

Thanks,
Larry

Hi, all,

I have a list of about 300 unique names on sheet1 and a spreadsheet with about 17000 rows, representing one week of account activity, on sheet2. These rows contain multiple occurrences of each name, along with dates, times and minutes of use. On sheet3 I have broken down the range of dates and time frames I am trying to isolate into a number of criteria tables.

I need to determine the activity for each user within a designated time frame. I need a both a count of occurences and a sum of the minutes.

I've been trawling the net all morning and struggling away with DCOUNT, IF, VLOOKUP and a pivot tables, but so far have failed to find a solution. DCOUNT leaves the names out, VLOOKUP and pivot tables leave the time frame out, and IF doesn't seem to work at all.

Any suggestions?

Edited to add, this is the type of thing I am trying to do, which does not work:
=IF('sheet2'!A:A='sheet1'B5, DCOUNT('sheet2'!,12,'sheet3'!$B$3:$E$7), 0)

Good afternoon Excel Forum,

I'm working on complete with a working word cloud / tag cloud generating macro. It uses chandoo's popular method. I've made some changes but still have two tweaks I need to make:

1) Instead of separating out each individual word I would like to just do counts of the number of times each unique cell contents are in the column. I think I will use a pivot table to accomplish this as that's how I do it manually whenever I need to.

2) The second change I want to make is to also adjust font colors in addition to font size based on the frequency (more frequently occurring descriptions set to Red and then work towards green for the lesser common descriptions).This is where I'm hoping I may be able to find some help, as I think I should be able to accomplish task 1 without too much difficulty (although I'd still appreciate some advice on that).

I've commented the code including where I imagine the color tweak would need to be made or added. Although perhaps this will change if I'm including the entire descriptions rather than pulling each unique word from the descriptions?

Sub DoAll()
    
    Dim CloudData As Range
    
    On Error Resume Next
    
    'Asks user to specify which column of data they wish to summarize
    Set CloudData = Application.InputBox("Please select a range with the incident information you wish to
summarize.", _
                                              "Specify Incident Information", Selection.Address, , , , , 8)
    
    'If the user selects nothing then a message box reminds the user what they need to / should have done.
    If CloudData Is Nothing Then
        MsgBox "Please select a column of information to summarize!"
    
    Else
    
        'If the user does select a column this message box tells them what they selected.
        'MsgBox "You selected: " & CloudData.Address(External:=True)
        
        'If the user does select a column a new worksheet titled "Frequency Tables" is added.
        Worksheets.Add().Name = "Frequency Tables"
    
    End If
   
    Dim rngCell As Range
    Dim WordsColumn As Collection
    Dim vntWord As Variant
    
    On Error Resume Next
    
    Set WordsColumn = New Collection
    
    'Splits the contents of each cell to grab unique words and then counts the number of occurances
    For Each rngCell In CloudData.Cells
        For Each vntWord In Split(Replace(Replace(Replace(rngCell.Value, """", ""),
"]", ""), "[", ""), " ")
            WordsColumn.Add WordsColumn.Count + 1, vntWord
            
    Sheets("Frequency Tables").Activate
    
    'Prints new unique words and updates count (number of occurances) on prior unique words
            With Cells(WordsColumn(vntWord), 1)
                .Value = vntWord
                .Offset(0, 1) = .Offset(0, 1) + 1
            End With
        Next
    Next
    
    
'Sorts frequency table descending. *Only works with currently written code which places
'words starting in Cell "A1"

    With Range("A1", Cells(Rows.Count, 2).End(xlUp)).Resize(, 2)
        .Sort .Cells(1, 2), xlDescending
        
    End With

Call CreateCloud

End Sub

Sub CreateCloud()
' this subroutine creates a tag cloud based on the list format tagname, tag importance
' the tag importance can have any value, it will be normalized to a value between 8 and 20

On Error GoTo tackle_this

Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
'Selection.Name = "FrequencyTable"

Dim size As Integer

size = Selection.Count / 2
 'size = FrequencyTable.Count / 2

Dim tags() As String
Dim importance()

ReDim tags(1 To size) As String
ReDim importance(1 To size)

Dim minImp As Integer
Dim maxImp As Integer

cntr = 1
i = 1

For Each cell In Excel.Selection

'If counter / 2 returns a remainder of 1 i.e. it's a word column then print that "tag"
'
    If cntr Mod 2 = 1 Then
        taglist = taglist & cell.Value & ", "
        tags(i) = cell.Value
    
'Otherwise (remainder of 0) it must be a frequency count. Set importance(i) to that
'frequency count and set max and min importance (frequency) accordingly
    Else
        importance(i) = Val(cell.Value)
        If importance(i) > maxImp Then
            maxImp = importance(i)
        End If
        If importance(i) < minImp Then
            minImp = importance(i)
        End If
        i = i + 1
    End If
    cntr = cntr + 1
Next cell

'Paste values in cell G1
'Range("G1").Select

'Ask user to select which cell they would like to place the tag cloud in
Set CloudCell = Application.InputBox("Please select the cell where you'd like to place the word cloud.", _
                                              "Specify Word Cloud Destination", Selection.Address, , , , , 8)
CloudCell.Select

'Sets active cell value to 'taglist' and cell fonts to size 8.
ActiveCell.Value = taglist
ActiveCell.Font.size = 8

'Starting at first character slot within the cell
strt = 1

'Starting at tag 1 to however many tags are contained in the frequency table
For i = 1 To size

'With active cell start changing font size of characters. Applies formatting to
'the appropriate number of characters based on the length of the word (Len(tags(i)).
'*To Change Color must somehow adjust the .ColorIndex portion

    With ActiveCell.Characters(Start:=strt, Length:=Len(tags(i))).Font
        .size = 6 + Math.Round((importance(i) - minImp) / (maxImp - minImp) * 14, 0)
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    strt = strt + Len(tags(i)) + 2
Next i



Exit Sub
tackle_this:
' errors handled here
MsgBox "You need to select a table so that I can create a tag cloud", vbCritical + vbOKOnly, "Wow, looks like
there is an error!"
End Sub
Thanks a bunch in advance for any help / advice you may provide. And sorry again about the lengthy code paste (I tried but failed to upload). Happy Holidays!


No luck finding an answer? You could always try Google.