countif, unknown numbers to count

[Jennifre]

Thanks to everyone who has been so helpful to my incredibly dorky and inexperienced questions!

I have yet another one:

I'm hoping to countif down one column, and what I'd ideally like returned is both what is being counted, as well as how many of each it counts.
For example:

D2:D1364 has many values, so I'd like a count of which values are there + how many of each, ideally next to each other, like this --

column E (#s):
1111
1112
1121

column F (counts):
301
25
103

Also, if / when I sort this, do I need to exclude these totals from the sort; i.e., will sorting all of the data mess up this count?

Thank you all, again!!

 

[mancemonster]

Something like...

=COUNTIF(D:D,D1)&"-"&D1

??

Not sure what the exact question is... Can you post some sample data?

Regards

Roger

 

[Jennifre]

Thanks Roger... I don't know how to post data, though I'm not sure how to make my question more clear. Um...

I need a count of the values in one column. Ideally, I'd like to see the values that populate the column in one column, with their totals in the next column so they are next to each other.

Is that more clear?

IGNORE THIS (below) IF MY CLARIFICATION ABOVE CLARIFIES MY QUESTION.
I tried your formula and saw how that can give me a count of a certain value in one cell, down that column, though I need the results to list the values in that column as well as give me a count of them. (I tried expanding the cell to the cell range, though it still returned only the starter cell's value + count.)

Thanks....

 

[jasonb75]

So if I get that right, look at column D, make an ascending list of unique entries in column E, then count them in column F.

I'm sure it can be done with formula, but think it would be easier with VBA if you can use it.

Please test on a COPY of your workbook to prevent data loss.

Sub test()
With ActiveSheet
    .Range("D2", Range("D2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "E2"), Unique:=True
    .Names("Extract").Delete
    With .Sort
        .SortFields.Add Key:=Range("E2"), SortOn:=xlSortOnValues, Order:=xlAscending
        .SetRange Range("E2", Range("E2").End(xlDown))
        .Header = xlNo
        .Apply
    End With
    .Range("E2", Range("E2").End(xlDown)).Offset(, 1).FormulaR1C1 = "=COUNTIF(C4:C4,RC[-1])"
End With
End Sub

 

[Jennifre]

Jason, thank you. Yes, that's exactly what I'm hoping for.

I will try to investigate what your suggestion is and how to use it... thanks

 

[jasonb75]

In excel, with the sheet holding the data selected.

Alt + F11 to open the VBA editor.

Select insert, then module in the menus.

Copy the code in my reply, then paste into the VBA editor.

Press F5.

NB: E2 needs to be empty before you press F5 or the code will fail.