Excel headache

  dougal720 14:51 06 Sep 06
Locked

I am quite an experienced user of excel and i was wondering if the following senario is possible in excel.

I have a very long list of numbers and all I want to do is just extract a simple list of the numbers that appear in that range. Obviously you can view the values that occur at some point in the list by filtering or by creating pivot tables. The only problem here is that I am then limited with how I can reference these elsewhere in my spreadsheet.

As an example, if my list was as below…..

90.9
100
50
20
50
20
50
20
100
100
50
100
100
50
100
90

How can I get a column of numbers that just lists what values appear in that list i.e:

90.9
100
50
20
90

Any help would be really useful.

Cheers

  VoG II 14:58 06 Sep 06

Probably several ways...

Make sure your list has a column heading. Select the list including the heading, Data > Filter > Advanced filter, tick copy to another location, insert the location and tick Unique records only.

  dougal720 15:05 06 Sep 06

Thanks for that

It does work although i am looking for something that automatically updates without having to manually do the filter as this will sit as part of the mechanics of an even bigger spreadsheet.

  VoG II 15:24 06 Sep 06

How about UniqueValues in MOREFUNC? click here

  VoG II 17:43 06 Sep 06

Now I'm at home where MOREFUNC is installed I can be more specific. Say your list is in A1:A50 but might grow to say A100.

In another column select sufficient rows to house all of the unique numbers. Then enter the formula

=UNIQUEVALUES(A1:A100)

and press CTRL + Shift + Enter (not just Enter) to enter this as an array formula. It will then appear in the formula bar as

{=UNIQUEVALUES(A1:A100)}

If you then add more unique values to your original list they will be added to the list of filtered values.

I also found a VBA solution by Erok Van Geit click here page 2, final post.

  VoG II 09:13 17 Sep 06

Did this help at all?

  VoG II 21:23 18 Sep 06

????

  mammak 21:42 18 Sep 06

Me thinks you frightend the pants of that poor bloke lol

  mammak 21:44 18 Sep 06

even Frightened maybye :-)

  VoG II 21:51 18 Sep 06

I do not understand your comment.

  mammak 22:03 18 Sep 06

bit of light hearted banter nothing to get uptight about just thought he hadnt answered your posts so mabye the Excel stuff had made him run a mile as it would thicko me sorry

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…