Hi Guys,

I am a excel n00b so, please be gentle!!

I have a caolumn with a list of values of cars like:

Ford
Chrysler
Ford
Toyota
Mazda
Toyota
Ford
Ford

I want to create a new sheet that lists all possible values and for
each value the number of times it appears in the list.

In this case it would be:

Ford 4
Toyota 2
Chrysler 1

etc etc etc

Could someone please help on how to do it?

The cell range is about C4-C26000 and has about 100 different possible
values so its quite large.

Thanks in advance,

-Al

Let list be in A1:A25
In B1 enter the text Ford
Enter other makes below that
In C1 type =COUNTIF(A1:A25,B1)
Copy this down the column by dragging its fill handle - Little solid square
in right bottom cornet when cell is selected
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

<BigAl.NZ@gmail.com> wrote in message
news:221373fb-cdfb-4eb2-a798-78ec9ca90f1f@x1g2000prh.googlegroups.com...
> Hi Guys,
>
> I am a excel n00b so, please be gentle!!
>
> I have a caolumn with a list of values of cars like:
>
> Ford
> Chrysler
> Ford
> Toyota
> Mazda
> Toyota
> Ford
> Ford
>
> I want to create a new sheet that lists all possible values and for
> each value the number of times it appears in the list.
>
> In this case it would be:
>
> Ford 4
> Toyota 2
> Chrysler 1
>
> etc etc etc
>
> Could someone please help on how to do it?
>
> The cell range is about C4-C26000 and has about 100 different possible
> values so its quite large.
>
> Thanks in advance,
>
> -Al

To get percentage change C1 to =COUNTIF(......)/COUNTA(A1:A25) and format as
%
cheers
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

<BigAl.NZ@gmail.com> wrote in message
news:221373fb-cdfb-4eb2-a798-78ec9ca90f1f@x1g2000prh.googlegroups.com...
> Hi Guys,
>
> I am a excel n00b so, please be gentle!!
>
> I have a caolumn with a list of values of cars like:
>
> Ford
> Chrysler
> Ford
> Toyota
> Mazda
> Toyota
> Ford
> Ford
>
> I want to create a new sheet that lists all possible values and for
> each value the number of times it appears in the list.
>
> In this case it would be:
>
> Ford 4
> Toyota 2
> Chrysler 1
>
> etc etc etc
>
> Could someone please help on how to do it?
>
> The cell range is about C4-C26000 and has about 100 different possible
> values so its quite large.
>
> Thanks in advance,
>
> -Al

On 26 Maj, 22:48, BigAl...@gmail.com wrote:
> Hi Guys,
>
> I am a excel n00b so, please be gentle!!
>
> I have a caolumn with a list of values of cars like:
>
> Ford
> Chrysler
> Ford
> Toyota
> Mazda
> Toyota
> Ford
> Ford
>
> I want to create a new sheet that lists all possible values and for
> each value the number of times it appears in the list.
>
> In this case it would be:
>
> Ford 4
> Toyota 2
> Chrysler 1
>
> etc etc etc
>
> Could someone please help on how to do it?
>
> The cell range is about C4-C26000 and has about 100 different possible
> values so its quite large.
>
> Thanks in advance,
>
> -Al

Hi Al

First, select column C, then goto Data > Filter > Advanced Filter >
Check "Unique records only > OK

Copy the filtered range to the desired sheet.

If you have the original data in sheet1 and use sheet2 to count number
of appearances, with "Toyota, Ford etc." in A2 and down, enter this
formula in B2:

=countif(Sheet1!$C$4:$C$26000,A2)

Copy the down as needed.

Regards,
Per

On May 27, 8:57*am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Let list be in A1:A25
> In B1 enter the text Ford
> Enter other makes below that
> In C1 type =COUNTIF(A1:A25,B1)
> Copy this down the column by dragging its fill handle - *Little solid square
> in right bottom cornet when cell is selected
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> <BigAl...@gmail.com> wrote in message
>
> news:221373fb-cdfb-4eb2-a798-78ec9ca90f1f@x1g2000prh.googlegroups.com...
>
>
>
> > Hi Guys,
>
> > I am a excel n00b so, please be gentle!!
>
> > I have a caolumn with a list of values of cars like:
>
> > Ford
> > Chrysler
> > Ford
> > Toyota
> > Mazda
> > Toyota
> > Ford
> > Ford
>
> > I want to create a new sheet that lists all possible values and for
> > each value the number of times it appears in the list.
>
> > In this case it would be:
>
> > Ford 4
> > Toyota 2
> > Chrysler 1
>
> > etc etc etc
>
> > Could someone please help on how to do it?
>
> > The cell range is about C4-C26000 and has about 100 different possible
> > values so its quite large.
>
> > Thanks in advance,
>
> > -Al- Hide quoted text -
>
> - Show quoted text -

There is probably over 100 different possible values, thats quite a
bit of typing.....

Please use pivot table to get the summar of the list.