Formatting & Counting Data

[alexcams]

Hello Excel Wizards!

Wondering if you can help me... I have to compile a monthly report at work, based on information inputted by customers.

As an example, customers are asked "What fruit would you like us to sell more of?" They can then type in any answer they wish and I get the data spat out into an excel file as a long list - as follows:

Apples
aples
ORanges
0ranges
Banas
Bananas
Pears
pears
PeArS
Aples

and so on and so forth. At the moment, I have to first go down the whole list of 1,500 entries, retyping them into a readable format and deleting any that make no sense. Then, I have to go down and count how many there are of each, so for example the above list would end up as:

Apples 3
Pears 3
Oranges 2
Bananas 2

So the end result I get is the most requested fruit.

Seems pretty simple but when it's 1,500 entries it takes a good hour or so to go down and retype them, and then go down and count them and then retype them with the total number next to one of them, deleting the duplicates.

Is there ANYWAY that you can save my life and give me some magical formula / solution to make the list count itself? is there even a magical way to make it format all the guests entries into one format?

You would save my life and a LOT of my time...

Thanks in advance!

[Pete_UK]

Rather than allowing your customers to type what they want, why not present them with a list of fruits to choose from using a drop-down data validation list, and then they can only choose from that list (make it long enough to accommodate all fruits).

Hope this helps.

Pete

[alexcams]

Hi Pete!

Unfortunately it's not fruits they're choosing but famous people, fictional characters etc - there's thousands of possible entries so we can't do that

Any other ideas?

[oeldere]

First sort the data from A to Z.

It will make it a lot easier to change the input.

Addionial you could copy / paste the desired text in the cells.

[newdoverman]

Break-down your form into the different areas of your store then create a data validation for each of the areas limiting the entries to what you list. This will eliminate misspelled words and crazy entries.

[alexcams]

Thanks for the advice but unfortunately I'm unable to alter the way the data is collected

I've managed to discover the wonder of a pivot table which has worked wonders for counting the amount of each entry (AMAZING!) but I'm still having to go through and retype the entries for format them correctly wish there was a simple way!