Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Advanced autofilter

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Evening all,

    I've had problems with this time after time trying to copy unique values to a new location on the same page

    is there a limit to how many rows it will look at ?

    my particular problem occurs on a range of data 8 columns wide by approx 3,000 rows deep - it seems to not bring back the last 800 unique records and I can't understand why

    many thanks
    Chris



  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris not to sound off but you are copying the selection ?


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think so, yeah, thanks Jack....

    select the range, select copy to new location, pick unique values and go

    it brings back loads, but not all - maybe missing around 800 of wht I'd expect....

    I only have problems on this single data range, same last month too (VAT returns!)

    some rows are column-filled, ie all 6 columns have entries, whilst some rows have only 4 or 5 column entries, if that mkes a difference...

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris not the best but easiest you filter so you MUST be able to sort and find say Chris in Col A and copy down as needed, and re sort back as was, excl often messes about here.

    or your need VBA to *find* copy row and paste to XXX .

    Notice find.... i am for ever shouting to guys find, worth invertigating
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks for keeping this open Jack... much appreciated !

    In the end, I just ditched the method and used a pivot table in its own sheet instead.

    ta
    Chris

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-20 04:59, Chris Davison wrote:
    thanks for keeping this open Jack... much appreciated !

    In the end, I just ditched the method and used a pivot table in its own sheet instead.

    ta
    Chris
    [img]/board/images/smiles/icon_smile.gif[/img]
    Just curious: Is the cell from where you activate Advanced Filter in the largest sized column in your data area, if there are any size differences between columns of interest at all?

    Aladin

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hmmmm

    I think I was just in any random cell when I started the autofilter Have I wrongly assumed it didn't matter ?

    also, the columns all ended on row 5,000 but some of them had gaps in various cells (ie processing errors where the inputter had forgotten to put the account code, or another row where the tax code had been forgotten etc etc)

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-20 05:23, Chris Davison wrote:
    hmmmm

    I think I was just in any random cell when I started the autofilter [img]/board/images/smiles/icon_eek.gif[/img] Have I wrongly assumed it didn't matter ?

    also, the columns all ended on row 5,000 but some of them had gaps in various cells (ie processing errors where the inputter had forgotten to put the account code, or another row where the tax code had been forgotten etc etc)
    I thought so .

    I'd suggest creating a dynamic name range based on a most complete (preferably, numeric type of) column, and use that name as List range in Advanced Filter.

    Aladin

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 13:59, Chris Davison wrote:
    Evening all,

    I've had problems with this time after time trying to copy unique values to a new location on the same page

    is there a limit to how many rows it will look at ?

    my particular problem occurs on a range of data 8 columns wide by approx 3,000 rows deep - it seems to not bring back the last 800 unique records and I can't understand why

    many thanks
    Chris


    Chris, as I see it you have 1 of 2 problems depending on what you mean by "it seems to not bring back the last 800 unique records".

    1. If you mean that these values are missing from the AutoFilter dropdown list, please understand that this list will only contain the 1st 1,000 unique values.

    2. If you mean that these values are missing from your extract, you may have a hidden empty row in your data list. You can check for this row by selecting a single cell as before and choosing the Edit | Go To... Special menu command, picking the "Current region" option, and pressing the Shift+Tab key combination. The row beneath the active cell is empty. That row is the demarcation of your list.

    [ This Message was edited by: Mark W. on 2002-04-20 13:31 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •