*Official* Excel help thread Page 8

  • Page

    of 10 First / Last

  • spamdangled 24 Jan 2012 09:59:14 27,421 posts
    Seen 3 hours ago
    Registered 6 years ago
    You could always make a button with some VBA script attached to it that does it.

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

  • X201 24 Jan 2012 10:01:50 15,681 posts
    Seen 5 seconds ago
    Registered 9 years ago
    darkmorgado wrote:
    You could always make a button with some VBA script attached to it that does it.
    We haven't got Greggywocky to that level yet DM.

    But we will...we will... :)

    Edited by X201 at 10:02:06 24-01-2012
  • mrpon 24 Jan 2012 10:04:49 29,393 posts
    Seen 49 minutes ago
    Registered 8 years ago
    Sounds like ole Greggy bigged up his Excel skills to his employer!

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

  • Deleted user 24 January 2012 10:09:59
    Ner vba wasn't a requirement. :)

    Figured it out though. Select the range, go to Home > Editing > Find and Select > Go to special. Click Blanks. OK. Then click in the formula bar, type ="", then Ctrl+Enter. Sort your column. Rows with blank cells in the column will be sorted first :)

    Er, simples..

    Thanks for your help!

    Edited by Greggywocky... at 10:11:01 24-01-2012
  • X201 24 Jan 2012 10:12:13 15,681 posts
    Seen 5 seconds ago
    Registered 9 years ago
    Greggywocky... wrote:
    Ner vba wasn't a requirement. :)

    Figured it out though. Select the range, go to Home > Editing > Find and Select > Go to special. Click Blanks. OK. Then click in the formula bar, type ="", then Ctrl+Enter. Sort your column. Rows with blank cells in the column will be sorted first :)
    Excel 97 used to do all of that with a tickbox :confused:
  • spamdangled 24 Jan 2012 10:16:16 27,421 posts
    Seen 3 hours ago
    Registered 6 years ago
    Excel always prioritises text over numerical values when calculating its sort order. Blanks will always come last.

    The solution is to use something like "N/A" in the empty field instead of simply leaving it blank. Excel will then always sort it so that any cells with "N/A" in are sorted to the top.

    Edit - I see greggywocky has found another way around it. Intersting, wasn't aware of that in the menus. Though to be fair I'm not that used to Office 2010, and I still think my solution is simpler.

    Edited by darkmorgado at 14:23:39 24-01-2012

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

  • Tonka 16 Feb 2012 09:24:11 21,130 posts
    Seen 2 hours ago
    Registered 11 years ago
    How do I make an iterative formula?
    So
    cell D2 = cell D1 + C1 * A1
    cell D3 = cell D2 + C1 * A1 etc etc

    Not sure if iterative is the right word for it...

    But how do I write a formula to target cells basically.

    If you can read this you really need to fiddle with your forum settings.

  • TechnoHippy 16 Feb 2012 09:26:23 14,719 posts
    Seen 1 month ago
    Registered 11 years ago
    =D1 + (C1 * A1)

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

  • TechnoHippy 16 Feb 2012 09:26:50 14,719 posts
    Seen 1 month ago
    Registered 11 years ago
    Type that formula into the cell where you want the formula to be calculated.

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

  • THFourteen 16 Feb 2012 09:43:41 34,184 posts
    Seen 5 hours ago
    Registered 9 years ago
    if you use $ symbols it will keep the cells the same if you cut and paste

    so if you cut and paste =A1+B1 to row 2, you normally get =A2+B2

    but if you wrote =$A$1 + B1 and copied that to row 2, you'd get =$A$1 + B2

    so A and 1 stayed the same, but B1 changed to B2 because you copied it down one row.
  • X201 16 Feb 2012 09:45:29 15,681 posts
    Seen 5 seconds ago
    Registered 9 years ago
    Tonka wrote:
    How do I make an iterative formula?
    So
    cell D2 = cell D1 + C1 * A1
    cell D3 = cell D2 + C1 * A1 etc etc

    Not sure if iterative is the right word for it...

    But how do I write a formula to target cells basically.
    Change the D2 cell to:

    D1 + C$1 * A$1

    then drag it down
  • Tonka 16 Feb 2012 09:48:45 21,130 posts
    Seen 2 hours ago
    Registered 11 years ago
    Great. Thanks for the help guys.

    It worked a treat. As you said, what I thought needed a formula was actually standard.

    Edited by Tonka at 10:00:48 16-02-2012

    If you can read this you really need to fiddle with your forum settings.

  • Deleted user 23 May 2012 15:25:31
    Hi

    Anyone know how I can extract records/rows based on a unique value within that record/row?

    I have a 31 column table, in which the C column contains the client name. Some of client names are repeated, some are not. I want to extract the records that contain just the unique client names to another location, so I can then Sum them.

    Any ideas? The advanced filter options don't seem to work - well, either that or I can't get them working.. :)
  • mrpon 23 May 2012 15:31:04 29,393 posts
    Seen 49 minutes ago
    Registered 8 years ago
    Add a new column and use the function =COUNTIF($C$1:$C$10,C1). Then use the filter to just show the rows equaling 1.

    Edited by mrpon at 15:32:35 23-05-2012

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

  • robthehermit 23 May 2012 15:31:40 4,245 posts
    Seen 5 hours ago
    Registered 10 years ago
    No idea, but here might be a good place to start:

    http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx

    GT: robthehermit

    Velim esset mundus iustus futuis procul et mori.

  • ming 23 May 2012 15:42:02 275 posts
    Seen 5 months ago
    Registered 8 years ago
    If you have Excel 2007 or newer copy column co to a new sheet and the use the remove duplicates function in the data section of the ribbon.

    If you don't have Excel 2003, copy the column to a new sheet, the click data > filter > advanced filter set the new column as the filter range, leave criteria blank and then tick the unique values only and click ok. Once it has filter the list copy it and paste it to a new sheet.

    Edited by ming at 15:42:22 23-05-2012
  • Deleted user 23 May 2012 16:02:18
    =isvalue(find("penis",cell ref))

    Then do a count of true.
  • Deleted user 23 May 2012 16:02:34
    The thing is, and which none of the websites I've trawled seem to address, is that I need all the other 30 columns too - ie. the whole row/record associated with the unique value in C.

    So if c20 is a client called 'BigCo' for example, and 'BigCo' is not mentioned again in C (ie. is the only occurence of 'BigCo' in the whole worksheet), I need to extract all of row20 - the whole 'BigCo' record..

    mrpron, I didn't really understand the references you were using and couldn't get it to work. C1 is a heading called 'Client' for example. Though I presume for $C$1:$C$10 you are intending that I add the range that contains the client names, c2:c290?

    Edited by Greggywocky... at 16:03:44 23-05-2012
  • ming 23 May 2012 16:15:17 275 posts
    Seen 5 months ago
    Registered 8 years ago
    Is the all of the data in your table the same for each company?
  • X201 23 May 2012 16:25:27 15,681 posts
    Seen 5 seconds ago
    Registered 9 years ago
    Can't you do what you need to with subtotals?
  • SYS64738 23 May 2012 16:25:30 1,702 posts
    Seen 8 minutes ago
    Registered 6 years ago
    So if BigCo has two rows with different data, do you want to extract the second record too? If yes then ming's approach is the way to go. Just make sure you select the whole table when using the remove duplicates function in Excel 2007.

    PSN: SYS64738_UK

  • Deleted user 23 May 2012 16:30:54
    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... :)
  • mrpon 23 May 2012 16:32:44 29,393 posts
    Seen 49 minutes ago
    Registered 8 years ago
    Greg, change C1 to refer to the first entry with data ie: C2. Then yes change the range to reflect your range required. Dollars are used to keep the range static when you copy it down.

    Ignore everyone else, mine is the best.

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

  • SYS64738 23 May 2012 16:38:32 1,702 posts
    Seen 8 minutes ago
    Registered 6 years ago
    Following example:

    BigCo - 123 - abc
    BigCo - 123 - abc
    BigCo - 456 - def

    If you use the remove duplicates approach, the second row will be removed but the third one will remain, leaving you with 2 records for BigCo. Or is the data for BigCo the same in all its rows (i.e. 123 - abc)?

    PSN: SYS64738_UK

  • robthehermit 23 May 2012 16:45:34 4,245 posts
    Seen 5 hours ago
    Registered 10 years ago
    Just did a test. Under the data tab, click advanced, then for the list range select column c, check the box which says unique records only. Click ok, job done.

    You can then copy that whole filtered selection into a new workbook.

    GT: robthehermit

    Velim esset mundus iustus futuis procul et mori.

  • Deleted user 23 May 2012 16:46:14
    Ok, mrpron I'll try it again.

    sys - I think you are missing my point maybe. In your example there are 3 BigCo records. If that is the case in my worksheet, I want to ignore them completely.

    eg, if my whole sheet was like this

    BigCo - 123 - abc
    LittleCo - 123 - abc
    BigCo - 456 - def

    I would only want to extract the LittleCo row, as it is unique. The other data in the record is irrelevant. I onlt want the rows where the client name is mentioned ONCE in the worksheet. :)
  • robthehermit 23 May 2012 16:49:55 4,245 posts
    Seen 5 hours ago
    Registered 10 years ago
    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?

    GT: robthehermit

    Velim esset mundus iustus futuis procul et mori.

  • robthehermit 23 May 2012 16:52:03 4,245 posts
    Seen 5 hours ago
    Registered 10 years ago
    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?

    GT: robthehermit

    Velim esset mundus iustus futuis procul et mori.

  • SYS64738 23 May 2012 16:52:21 1,702 posts
    Seen 8 minutes ago
    Registered 6 years ago
    I see. However all three rows are unique records, there are no duplicates in your example, unless you only focus on individual columns and not the table as a whole, but you already stated that you need to include all columns in a row in your extract.

    I'm still not quite sure what you're trying to achieve as you would completely ignore some data (in above example all records of BigCo)...

    /confused

    PSN: SYS64738_UK

  • mrpon 23 May 2012 16:55:44 29,393 posts
    Seen 49 minutes ago
    Registered 8 years ago
    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.

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

  • Page

    of 10 First / Last

Log in or register to reply