Excel 2003 List with Frequency Count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

blazingbadger - 15 May 2007 17:24 GMT

I'm trying to create a list of alphabetized text items with frequency
count in the adjacent column.

I want to be able to add new entries to the list and then have Excel
compare the new entry to the existing entries and either add the item
if it doesn't exist or update the count next to the existing entry if
already exists.

I've done a lot of searching and haven't found a way to do this,
although it seems like a pretty standard case.

Any ideas?

Thank you!

Reply to this Message

Joel - 15 May 2007 19:50 GMT

Not sure where your inputs are coming from. I just used a variable NewWord
tto test the macro. try this

Sub alphabetize()

NewWord = "ccc"

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To Lastrow

If StrComp(NewWord, Cells(RowCount, "A")) = 0 Then
Cells(RowCount, "B") = Cells(RowCount, "B") + 1
Exit Sub
End If

If StrComp(NewWord, Cells(RowCount, "A")) < 0 Then
Cells(RowCount, "A").EntireRow.Insert Shift:=xlDown
Cells(RowCount, "A") = NewWord
Cells(RowCount, "B") = 1
Exit Sub
End If

Next

Cells(Lastrow + 1, "A") = NewWord
Cells(Lastrow + 1, "B") = 1

End Sub

> I'm trying to create a list of alphabetized text items with frequency
> count in the adjacent column.
[quoted text clipped - 10 lines]
>
> Thank you!

Reply to this Message

blazingbadger - 16 May 2007 21:54 GMT

I actually started with a basic spread sheet and then started playing
around with Microsoft's List tool I assumed list (maybe "designated"
list is a better term) would have more functionality.

What is the best way to enter my text data? Is there a way to create
a box at the top that I can type a piece of text into that will then
be compared and added/count updated to the list? Is some serious
VBAing the only way to do this? A little pop up text box would be
handy. Maybe I'm taking this too far, I don't have the time to jump
down a black hole after the perfect spreadsheet :)

Reply to this Message

Joel - 17 May 2007 03:34 GMT

A listbox is very simple in VBA if you want to play around with forms. The
esies way is with data validation on a spreadsheet. If you type the numbers
1 -1 0 in the cells A1:A10.

Then click on cell C1. Selectect data Menu - Validation. In the Allowbox
select LIST. Then press the spreadsheet box to the right side of the SOURCE
box. Select cells A1:A10. The press the CLOSE box (below the X). then
press OK in the Data Validation box. Now on the preadsheet click on cell C!
and the list box opens.

> I actually started with a basic spread sheet and then started playing
> around with Microsoft's List tool I assumed list (maybe "designated"
[quoted text clipped - 6 lines]
> handy. Maybe I'm taking this too far, I don't have the time to jump
> down a black hole after the perfect spreadsheet :)

Reply to this Message