Any Excel experts?

Vejovis

I have been tasked with making a spreadsheet, but I've only used the most basic functions in Excel.

I need to create a list and have Excel count up the amount of times the same thing is repeated.

For example, if the list was

aaabbbbbbcccccdd

It would add up how many As, how many Bs, etc then produce a graph showing the results.

The list of different items could run into hundreds of unique items.

If this is possible, could you please explain in an idiot's guide?

Thanks.

Neil_Jones

Have a look at this:
https://exceljet.net/formula/count-specific-characters-in-a-cell

Then to generate graphs automatically:
http://excelribbon.tips.net/T007887_Automatically_Creating_Charts_for_Individual_Rows_in_a_Data_Table.html

happyandcontented

https://www.youtube.com/watch?v=CLEYppjUWFI

A Youtube clip may help.

Vejovis

Neil_Jones wrote: »

Have a look at this:
https://exceljet.net/formula/count-specific-characters-in-a-cell

Then to generate graphs automatically:
http://excelribbon.tips.net/T007887_Automatically_Creating_Charts_for_Individual_Rows_in_a_Data_Table.html

With that, it looks like you'd have to put in the item you're looking for every time, so you'd have lots of cells with formulas.

I was hoping that there was a way to automatically add matching results. For example, if there was a list of dates of birth, it could pick out and count any dates of birth that matched.

Vejovis

happyandcontented wrote: »

https://www.youtube.com/watch?v=CLEYppjUWFI

A Youtube clip may help.

I'm using countif for my other columns, but this column has an indefinite number of possibilities. So countif wouldn't work.

[Deleted User]

A distribution you mean?

Frequency will create a distribution for you:

Formulas >More Functions >Statistical >Frequency.

bluesnake

What you want are pivot tables. Make a backup of the workbook first. Sometimes it is easier creating an extra column containing 1 for each row, just to SUM things up and make counting of items easier

https://www.youtube.com/watch?v=peNTp5fuKFg
https://www.youtube.com/watch?v=9NUjHBNWe9M

Excel does not have an indefinite number of cells. For bigger you have to think 'database'

paddyrg

64-bit Excel handles a HUGE number of rows, however it's not the right tool for more than a few thousand. Rather, this is trivial in a true database, and it'll be far better optimised to perform the counts within a single SQL statement.

If this is a one-off project, use Excel, but if this needs to be robust and regular, even Access will be far more capable.