*Official* Excel help thread Page 9

  • Page

    of 10 First / Last

  • Deleted user 23 May 2012 17:02:46
    robthehermit wrote:
    Greggywocky... wrote:
    No - if 'BigCo' has more than one row/record in the worksheet, I want to ignore it completely. I want to extract rows for only those companies that have ONE entry in the 300 rows of companies I have... :)
    That's a whole different kettle of fish. So if bigco has 2 entries, and smallco and medco have 1 each, you want a filter that only returns smallco and medco?
    Correct! :)

    I know that every record will be unique. As I said, (I think) I want to extract records based on a unique value within the record, not a unique record itself.

    Basically I have 300, 31 column rows with a Client name in C. (The data in the other 30 columns is irrelevant as far as my query is concerned, other than that it must also be extracted.) I want to extract (rather than filter in place) all rows with Clients that are only mentioned ONCE in the worksheet, such as smallco and medco mentioned above.

    I know, it's proving quite tricky so far... thanks for the answers though :)
  • Deleted user 23 May 2012 17:03:55
    mrpon wrote:
    robthehermit wrote:
    Perhaps there is a formula that will count instances of each name and drop that count in another column which you can then filter for values of 1?
    Yes, imagine if someone had posted such a thing.
    Heh - that filters them in place though, I need them extracting...
  • robthehermit 23 May 2012 17:03:56 4,039 posts
    Seen 9 minutes ago
    Registered 10 years ago
    For a quick and easy way to identify the duplicated names, have you thought about conditional formatting? Select column C > Conditional Formatting > Highlight Cell Rules > Duplicate Values.

    GT: robthehermit

    Velim esset mundus iustus futuis procul et mori.

  • Deleted user 23 May 2012 17:05:50
    robthehermit wrote:
    For a quick and easy way to identify the duplicated names, have you thought about conditional formatting? Select column C > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
    Yeh I can do it with conditional formatting if I add a fill colour or something to unique values in C, then filter by the fill colour. Problem is it's filtered in place...
  • robthehermit 23 May 2012 17:07:37 4,039 posts
    Seen 9 minutes ago
    Registered 10 years ago
    You can then use the standard filter to sort by cell colour. The unhighlighted unique references will all be at the top for an easy copy/paste.

    GT: robthehermit

    Velim esset mundus iustus futuis procul et mori.

  • mrpon 23 May 2012 17:08:25 28,774 posts
    Seen 9 seconds ago
    Registered 8 years ago
    Greggywocky... wrote:
    mrpon wrote:
    robthehermit wrote:
    Perhaps there is a formula that will count instances of each name and drop that count in another column which you can then filter for values of 1?
    Yes, imagine if someone had posted such a thing.
    Heh - that filters them in place though, I need them extracting...
    Right. That formula will tell you how many instances of company name. You therefore just filter on 1 and copy and paste the results?

    Give yourself 5 or gig, you're worth it.

  • Deleted user 23 May 2012 17:21:00
    Thanks chaps, I'm thinking that may have to do for now, though I'm sure there's a more elegant way. I was sure a criteria-based advanced filter (you know, the one where you copy the headings plus one row to another part of the spreadsheet then apply criteria and filter it) would work, but no joy so far...

    Anyway, thanks for the help. I should probably leave you to get on with your lives now. ;)
  • spamdangled 23 May 2012 17:25:46 27,276 posts
    Seen 35 minutes ago
    Registered 5 years ago
    Greggywocky... wrote:
    robthehermit wrote:
    Greggywocky... wrote:
    No - if 'BigCo' has more than one row/record in the worksheet, I want to ignore it completely. I want to extract rows for only those companies that have ONE entry in the 300 rows of companies I have... :)
    That's a whole different kettle of fish. So if bigco has 2 entries, and smallco and medco have 1 each, you want a filter that only returns smallco and medco?
    Correct! :)

    I know that every record will be unique. As I said, (I think) I want to extract records based on a unique value within the record, not a unique record itself.

    Basically I have 300, 31 column rows with a Client name in C. (The data in the other 30 columns is irrelevant as far as my query is concerned, other than that it must also be extracted.) I want to extract (rather than filter in place) all rows with Clients that are only mentioned ONCE in the worksheet, such as smallco and medco mentioned above.

    I know, it's proving quite tricky so far... thanks for the answers though :)
    Hmmmmm.

    I've had to do something vaguely similar to this in the past.

    On your main worksheet, you want a column with a formula in that counts all instances of the company name in a range (something like =COUNTIF(range,"company name", but with the company name linking to the cell).

    Then, on a secondary worksheet you will want a formula in each box that copies the corresponding cell value of the previous sheet, but only after checking that the value in that row's countif cell is 1. So, something like =IF(Sheet1!$Z$2=1,Sheet1!a2,"") would be in A2 of your second sheet. Copy that across every cell in sheet 2, then set up a filter on sheet 2 so that blank cells are filtered out.

    Failing that, you could start getting technical with things like VLOOKUP or you could start playing around in VBA.

    3DS: 4055-2781-2855 Xbox: spamdangled PSN: dark_morgan Wii U: Spamdangle Steam: spamdangled

  • robthehermit 23 May 2012 17:35:18 4,039 posts
    Seen 9 minutes ago
    Registered 10 years ago
    Or he could copy and paste. Much easier.

    GT: robthehermit

    Velim esset mundus iustus futuis procul et mori.

  • mrpon 23 May 2012 17:37:34 28,774 posts
    Seen 9 seconds ago
    Registered 8 years ago
    Jesus.

    Give yourself 5 or gig, you're worth it.

  • SYS64738 23 May 2012 18:23:17 1,654 posts
    Seen 18 hours ago
    Registered 5 years ago
    mrpon wrote:
    Jesus.
    :D

    PSN: SYS64738_UK

  • Mr_Sleep 30 May 2012 15:26:54 16,967 posts
    Seen 3 minutes ago
    Registered 12 years ago
    Right, so I am trying to reorganise our folder structure in work and so I am trying to get a list of all the folders that currently exist. I've managed this but now I want an easy way to look at that data and mark it up.

    The way that has occurred to me is to use a pivot table to act as a folder tree, then mark up the folders we want to keep in red and then conditionally format the top level folder to show that there are items lower in the list that want to be kept. Does that make sense?

    The best way I can think to do it is use an easy macro to mark things up in red and then conditionally format the top level folder so that it displays as red but unfortunately I don't know the best way to find that sort of information from a cell.

    So, anyone know how I can use a formula to give an output of what colour a cell or font is marked as?

    You are a factory of sadness.

  • X201 30 May 2012 15:30:47 15,274 posts
    Seen 21 minutes ago
    Registered 8 years ago
    Mr_Sleep wrote:

    So, anyone know how I can use a formula to give an output of what colour a cell or font is marked as?
    I've got a macro that does that somewhere.
  • Mr_Sleep 30 May 2012 15:34:09 16,967 posts
    Seen 3 minutes ago
    Registered 12 years ago
    I was worried that it might be a macro type solution. My macro skills are not what they once were.

    You are a factory of sadness.

  • mrpon 30 May 2012 15:42:12 28,774 posts
    Seen 9 seconds ago
    Registered 8 years ago
    Sounds menial. Is not easier to just delete the unwanted folders in situ?

    Give yourself 5 or gig, you're worth it.

  • Mr_Sleep 30 May 2012 15:44:38 16,967 posts
    Seen 3 minutes ago
    Registered 12 years ago
    That would only be easier if was in charge of all the data and was aware of exactly what was needed. Unfortunately I am going to have to guess a lot of this and then throw it past people above me. I'd delete half this crap if it was my way :-D

    You are a factory of sadness.

  • X201 30 May 2012 15:51:04 15,274 posts
    Seen 21 minutes ago
    Registered 8 years ago
    Mr_Sleep wrote:
    That would only be easier if was in charge of all the data and was aware of exactly what was needed. Unfortunately I am going to have to guess a lot of this and then throw it past people above me. I'd delete half this crap if it was my way :-D



    Just move all of it to a different file location.

    Send out an email at 4:50 on Friday afternoon telling people you're going to move the files.

    Then in the following weeks, wait and move some files back if anyone complains.

    Wait 3 months and archive or delete the rest.

    Edited by X201 at 15:52:27 30-05-2012
  • Mr_Sleep 30 May 2012 16:01:15 16,967 posts
    Seen 3 minutes ago
    Registered 12 years ago
    That's the intention after this is done.

    You are a factory of sadness.

  • X201 30 May 2012 16:05:30 15,274 posts
    Seen 21 minutes ago
    Registered 8 years ago
    Have a look at the CELL function. You may be able to use a formatting trick instead of colour.

    Edited by X201 at 16:08:12 30-05-2012
  • Mr_Sleep 30 May 2012 16:15:22 16,967 posts
    Seen 3 minutes ago
    Registered 12 years ago
    Cheers, I'll have a look.

    You are a factory of sadness.

  • w00t 6 Jun 2012 11:17:25 11,079 posts
    Seen 3 days ago
    Registered 10 years ago
    Dammit!

    Somehow, the little drag box in the bottom right corner of a highlighted cell has vanished :( (if you know what I mean)

    help

    The day charity died - NEVER FORGET

    (the mic was OK in the end)

  • spamdangled 6 Jun 2012 11:20:58 27,276 posts
    Seen 35 minutes ago
    Registered 5 years ago
    Have you locked the worksheet by accident?

    3DS: 4055-2781-2855 Xbox: spamdangled PSN: dark_morgan Wii U: Spamdangle Steam: spamdangled

  • w00t 6 Jun 2012 11:21:11 11,079 posts
    Seen 3 days ago
    Registered 10 years ago
    nvm, found it hidden away in options

    fecking thing disabling itself >:(

    The day charity died - NEVER FORGET

    (the mic was OK in the end)

  • Deleted user 18 July 2012 09:00:58
    Quick question. Let's say I have a value in a cell, 291 for example. I want to convert that cell to say "100-300" instead. If the cell contained the number 7, I would want it to say "1-10" instead, and so on. Is there a formula to do this? Thanks!
  • X201 18 Jul 2012 09:11:04 15,274 posts
    Seen 21 minutes ago
    Registered 8 years ago
    Depending on how many bands you need you can do it with an IF statement.


    Or if you need a lot of bands you could do it with a combination of ROUNDUP and a lookup table. It depends on what bands you need.

    Edited by X201 at 09:13:47 18-07-2012
  • TechnoHippy 18 Jul 2012 09:15:34 14,708 posts
    Seen 17 hours ago
    Registered 11 years ago
    @Greggywocky...

    I don't think you can do that with a forumula as entering a literal value would overwrit the formaula. If you can put the new value in a different cell (so have acolumn for original values and a column for new values) then you can use:

    =IF(A1 = 291, "100-300", A1)

    The A1 = 291 can be any logical test, so A1 < 200 if you want tocatch values under 200.

    If it has to be done in the same cell then you'll probably need a macro.

    My books, contests, reviews and author interviews on my blog

  • mrpon 18 Jul 2012 09:19:09 28,774 posts
    Seen 9 seconds ago
    Registered 8 years ago
    Yeah, nested IFs could work for you as mentioned above. Something like this:

    =IF( B1<11,"1-10",IF( B1<100,"10-100","Not Listed" ) )

    Edited by mrpon at 09:19:49 18-07-2012

    Give yourself 5 or gig, you're worth it.

  • TechnoHippy 18 Jul 2012 09:34:27 14,708 posts
    Seen 17 hours ago
    Registered 11 years ago
    You wouldn't be able to do that in the same cell though would you?

    My books, contests, reviews and author interviews on my blog

  • mrpon 18 Jul 2012 09:44:57 28,774 posts
    Seen 9 seconds ago
    Registered 8 years ago
    Don't think so, but another hidden column could work for him.

    Give yourself 5 or gig, you're worth it.

  • mrpon 18 Jul 2012 09:45:06 28,774 posts
    Seen 9 seconds ago
    Registered 8 years ago
    Post deleted

    Give yourself 5 or gig, you're worth it.

  • Page

    of 10 First / Last

Log in or register to reply