If I have a list of text values in a excel and I want to count the number of same items eg?

Question

Col1
---------
Blue Dog
Green Dog
Red Dog
Blue Dog
Blue Dog
Green Dog

Col2 & 3 (I want to show)
Blue Dog 3
Green Dog 2
Red Dog 1

Can column 2 and 3 be calculated via a generic Excel formula given the data in column 1 that is not fixed and will grow over time ?

Answer 1

Dear Joey Answers,



Yes, the second and third columns (i.e., columns B and C) can be calculated using Excel formulae to show what you want. By using an array formula in column B, you don't need to use a pivot table or VBA.



You didn't say that you wanted your summary in columns B and C to be sorted based on the counts of occurrences of the items listed in column A, but that can also be done with a couple extra steps (again using standard Excel formulae). I'll first show the simpler, unsorted summary of unique items from column A, and then I'll describe how to extend these calculations to display this summary sorted by the count of each type.



For notation, I'll list each cell in brackets, such as [A1], followed by the contents of that cell. That should enable you to reproduce the model, since I don't know a better way to present it in this forum. After entering the entire model, you may want to adjust some cell widths to fully see any text or numbers.



Start by labeling the first three columns, as follows.



[A1] List Items

[B1] Unsorted Types

[C1] Unsorted Counts





Fill in as many items of text in the remainder of column A as you like. For now, I'll just repeat your example. You can modify them or add to this list later, of course.



[A2] Blue Dog

[A3] Green Dog

[A4] Red Dog

[A5] Blue Dog

[A6] Blue Dog

[A7] Green Dog





Now type the following formula. When you have finished, don't press "enter" immediately. Instead, hold down both "control" and "shift," then press "enter" before letting go of those other two keys. If done properly, Excel will understand the contents of the cell to be an array formula, and you should see that it has placed braces (i.e., "{" and "}") around it. If you accidentally press "enter" prematurely, just go back to the cell and click on the formula as you would to modify it, and then press "control," "shift," and "enter" together. (Note, on some keyboards, "control" may appear as "ctrl" or "ctl" only.) Also notice that "A$2:A$100" only covers up to row 100, so you should change 100 everywhere to be the largest row number that you desire for your data (in Excel 2003, I think this number can go up to around 65,000).



[B2] =INDEX(A$2:A$100, MATCH(0, IF(ISBLANK(A$2:A$100), "", COUNTIF(B$1:$B1, A$2:A$100)), 0))



After using "control" + "shift" + "enter" you should see braces have been added.



[B2] {=INDEX(A$2:A$100, MATCH(0, IF(ISBLANK(A$2:A$100), "", COUNTIF(B$1:$B1, A$2:A$100)), 0))}





At this point, you should also see "Blue Dog" appear in in B2. Copy the formula here into the rest of column B (e.g., into B3:B100 in my example). Column B should appear as below.



[B1] Unsorted Types

[B2] Blue Dog

[B3] Green Dog

[B4] Blue Dog

[B5] #N/A

. . .

[B100] #N/A





For column C, enter the formula shown for [C2], then copy it into the rest of column C as you did with the formula for [B2] in column B.



[C2] =COUNTIF(A$2:A$100, B2)





Column C should now appear with the appropriate counts corresponding to the types summarized in column B, as seen here.



[C1] Unsorted Counts

[C2] 3

[C3] 2

[C4] 1

[C5] 0

. . .

[C100] 0





You now have basically what you want, but can clean up the appearance in various ways. For example, you could change the formula in B2 to blank-out the cell instead of displaying "#N/A" by expanding the formula with a wrapper, as shown here. Don't forget to use "control" + "shift" + "enter" if you modify the formula. After updating B2, you would again copy it into the rest of column B to update those cells the same way.



[B2] =IF(ISNA(INDEX(A$2:A$100, MATCH(0, IF(ISBLANK(A$2:A$100), "", COUNTIF(B$1:$B1, A$2:A$100)), 0))), "", INDEX(A$2:A$100, MATCH(0, IF(ISBLANK(A$2:A$100), "", COUNTIF(B$1:$B1, A$2:A$100)), 0)))





Similarly, column C2 could then be modified with a wrapper, and then copied into the rest of column C.



[C2] =IF(B2="", "", COUNTIF(A$2:A$100, B2))





At this point you should have the behavior that you described. If you wish to have the summaries ordered by their counts, it's useful to create a work area in other columns or on another sheet.







EDIT:

Unfortunately, Yahoo!Answers has imposed a severe length restriction on answers during the past seven months when I was not answering questions. My answer was slightly more than 5000 characters, including spaces, but that is now deemed excessive and rejected from submission. I had to chop the answer off to get any of it to post, with the main part still shown above, but I had to leave out the additional portion showing dynamic sorting. Sorry that I wasn't able to give my complete answer.



The basic overview of doing the sort is here.



First create a unique index from the unsorted counts. This is easy; just add 1 divided by the row number of each unsorted count. Then use the RANK function to get a full ordering of all of these unique indexes. From that, an application of the INDIRECT function properly orders the summaries dynamically.

Answer 2

Edit:



You cannot use a formula to generate a concise, no spaces, easily readable,'unique list' of the entries in column A. You can, however, use the Countif function to count the occurrences of each item in column A.



If you wish to create a dynamic, sorted, unique list of the items in column A and return the number of occurrences of each, you would have to use a VBA event handler.



The following is an example of this event handler. Copy the following code to the clipboard:



Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, LastRowA, LastRowB

LastRowA = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

Application.EnableEvents = False

Columns("B:C").ClearContents

For i = 1 To LastRowA

If Application.CountIf(Range("B:B"), Cells(i, "A")) = 0 Then

Cells(i, "B").Offset(1, 0).Value = Cells(i, "A").Value

End If

Next

Columns("B:B").Select

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

LastRowB = Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRowB

Cells(i, "C").Value = Application. CountIf(Range("A:A"), Cells(i, "B"))

Next i

Range("B1").Value = "Entry"

Range("C1").Value = "Occurrences"

Range("B1:C1").HorizontalAlignment = xlCenter

Range("B1").Select

Columns("B:C").AutoFit

Application.EnableEvents = True

End Sub



Select the appropriate worksheet and right click the sheet tab.



Select 'View Code'.



Paste the code into the sheet module editing area to the right.



Close the VBE and return to the worksheet.



Upon the next worksheet_change event, the unique, sorted, list will be returned in column B, with the occurrences of each in column C.



If you add, for example, 'Orange Dog' in column A, a revised list will be returned. This will evaluate all 65,536 rows in column A for Excel 2003, and prior, and all 1,000,000 + rows for Excel 2007/2010.

Answer 3

If you use the SUM function, you'll always have to define the range. If you use SUBTOTAL instead "=SUBTOTAL(9,B2:BXX)", you'll only sum up what you show on the screen. If you do an autofilter by column A to show only the common values in A whose column B data you want to SUBTOTAL, the only values in B that will add up will be those belonging to the same value in Column A. No need to sort. Alternatively, you could use the Subtotals function. First select column A&B at the top, go to Data --> Sort, choose Ascending by Column A. Then go to Data --> Subtotals, then select "At each change in (Column A's name)", "Use function Sum", and "Add subtotal to (Column B's name). You'll end up with Pumpkin TOTAL 78 Orange TOTAL 94

Answer 4

Use a pivot table. Refreshing the data will update any changes.



It is possible to create a unique list with a formula, but you are bound to have blanks, as the new list will have to be the same number of rows as the one to the left to "grow". The formula is:-

=IF(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1 ),A1&COUNTIF(A:A,A1),"")

fill this down the list.