Showing each value from a list

**Richmate** · 04-14-2010, 07:18 AM

Hi

Im looking for a formula which will allow me to show all different values from one list and display thenm into another list. I would also like the number of times each value appeared in the original list to show next to the new list.

Any help would be appreciated.

Thanks in advance

Rich

**teylyn** · 04-14-2010, 07:53 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Can you show the original list, the "different values" list and the count you describe? Please mock up an example manually and explain the reasoning that would get to those results.

**Richmate** · 04-14-2010, 08:38 AM

OK, thanks for the advice.

I have attached a mock spreadsheet of what I require.

I will try and explain again more clearly.

Say I have a list of 20 values. Theses 20 values are made up of 13 original values. I need excel to create a list of these 13 original values and give me a count of the time each value appears in the list.

Hope that makes sense.

Many thanks

Rich

**Richmate** · 04-14-2010, 11:46 AM

I have noticed that what Im trying to achieve is the same as what you can do on advanced filter (move unique to other column) but I need it to update automatically without the need of auto filiter.

Many thanks

Rich

**teylyn** · 04-14-2010, 06:50 PM

Hi Rich,

applied to your sample data, this formula in F7

=IF(ISTEXT(INDEX(C7:C15,MATCH(0,COUNTIF($F$6:F6,C7:C15),0))),INDEX(C7:C15,MATCH(0,COUNTIF($F$6:F6,C7:C15),0)),0)
This is an array formula and must be confirmed with Ctrl-Shift-Enter. Then copy down to F15.

This formula in G7, copy down:
=IF(F7<>0,COUNTIF(C7:C15,F7),"")