Creating a Word Cloud-like visualization using data in PowerPivot?

Curious if there's anything I can do with data currently in a Field that looks like this:


Basically, this was repeating data that was concatenated into one field and delimited by ";"

I have a table that I'm passing back into PowerPivot that I can use to "clean" the field; however, I'm not entirely sure how I would clean it. All I'm trying to accomplish is a visual representation of how prevalent "AAA" is when compared to "BBB" across all of the records.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
do you mean prevalent in terms  of length?

you can use text to column with use of ; as delimiter and then use transpose to change it from horizontal to vertical and then use =REPT("I",LEN(TRIM(A1)))
-PolakAuthor Commented:
Negative, for example:
Record 1: AAA; BBB; DDD
Record 2: AAA; CCCC; EEE-EE
Record 3: AAA; FFF; BBB

Data AAA is the most prevalent across all records, I'm thinking maybe there's a way to clean that so that it can be represented visually?
ProfessorJimJamMicrosoft Excel ExpertCommented:
i still do not understand it. can you perhaps illustrate in a workbook, example of what the current data is like and then in another sheet the desired solution. so that it would be easy for me to understand the solution.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

-PolakAuthor Commented:
Sure, see the attached. Let me know if you have any questions.
ProfessorJimJamMicrosoft Excel ExpertCommented:
please find attached solution with formula.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Neato. REPT is a new one for me, thanks!

This is more a matter of opinion, but how would you represent the "prevalence" of a partner. Essentially, I really dislike word clouds, but they do seem  most appropriate because..... I'm not really trying to give the impression that "AAA's" real magnitude is greater than "CCCC".

For example, AAA may appear 100 times but only partner with you by a a measure of 1.5 hours each time; where as BBB may appear 10 times but partner with you by a measure 15 hours each time. There is no data on the measure of hours by partner. Ergo, I feel like if I were to visualize our solution in a bar chart or something similar it would imply that AAA is more important than CCC where in reality they are equal. I think word clouds eliminate SOME of that assumption, do you agree?
ProfessorJimJamMicrosoft Excel ExpertCommented:

i am sorry, i do not understand the measurement and comparison you are trying to achieve. my expertise in this forum is in Microsoft Excel. i do not understand any other topic other than what can be achieved with Excel.

i only could provide solution with formula and VBA based on illustration of what is current data and what is desired transformation required, which in your case i have provided.

from your statement above, i kinda confuse this with statistics which not sure what exactly is required.

do you mean that if AAA is seen once then it should be disregarded in the following rows?

of course if you show that table in a bar chart, AAA will peak and show higher than any other. becuase currently the count is 3 makes it more dominant among others.
-PolakAuthor Commented:
You solution is fantastic, how I visualize what results is a matter opinion. Just bouncing around Ideas thanks for your quick work on this one.
ProfessorJimJamMicrosoft Excel ExpertCommented:
thank you Polak for your the feedback and nice words.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.