1

The first column is the project number and the columns after that are the applications that are impacted by each project (quick example made up). My question I want to solve is how many projects are in a application?

The problem is I can't count it by a specific text string because the data is not all consistent. If I put a wild card for a specific string, it will count those apps who may contain that text or letters. There is not a uniqueness in the naming convention. Also, the data set is too large to manually convert every instance of an application to be the exact same. Replace all won't be able to replace all the different instances of how an app is spelled, cap differences, or # of spaces without manually changing the values to be consistent. For example, pink white is the same as Pink White, pink White, Pink white, etc.

enter image description here

Please help or ideas to help minimize manually clean up.

David Duponchel
  • 3,959
  • 3
  • 28
  • 36
song16
  • 11
  • 1
  • You are probably going to need a Fuzzy Lookup addin to deal with the differences in spelling. – Scott Craner Jan 26 '17 at 19:42
  • Maybe do these: 1. Replace spaces between words, 2. Trim white spaces, and 3. Change the whole column to lower cases (See this example: http://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells) – ian0411 Jan 26 '17 at 20:25
  • What is the difference between the applications `green`, `black` and `green black` ? Are you saying that `green black` is just `green` and `black` but they got lumped in the same column? – Robin Mackenzie Jan 27 '17 at 05:31
  • Green, black, and green black are all different. I was trying to show that I couldn't do a count on green because it would count green black or do a wild card because it would give me a wrong count. – song16 Jan 30 '17 at 14:49
  • Why not use countifs? – PWiggin Nov 10 '17 at 05:28

0 Answers0