*Official* Excel help thread Page 7

  • Page

    of 10 First / Last

  • delbert 27 Oct 2011 12:00:50 2,889 posts
    Seen 2 days ago
    Registered 11 years ago
    localnotail wrote:
    Dear Gods of Excel, please heed my call for help.

    I have a spreadsheet with names and emails in (among other things) that stores campaign supporter details.

    I need to weed out duplicates as sometimes people apply several times and I can only count them as one supporter, but sometimes I get people applying as couples and families who have the same email address, and they count as individual supporters, so I can't just delete them.

    I know how to use e.g. =COUNTIF(a:a,a2)>1 with a colour format to highlight duplicates, but is there a way to get Excel to use both columns to compare these values so it will only pick up duplicates where both the name and the email are the same?

    Thanks for any help,

    /offers virtual jaffa cakes.

    P.S. I do realise that I coud just look for duplicate emails and then cross-check the names, but I just wondered if excel could do it all for me to save time and effort
    Maybe not the quickest way but if you use 2 new columns, in the first concatenate the 2 columns you want (e.g. A2&B2) then sort by this column, in the next column you are going to check if the cell above is a duplicate using an if (e.g. =if(C2=C1,"Duplicate","Fine") this will then let you filter that column to see all the duplicates and act as you need..

    edit: excuse the smilie its meant to be the end of the brackets?!

    Edited by delbert at 12:02:02 27-10-2011
  • mrpon 27 Oct 2011 12:13:13 28,893 posts
    Seen 8 hours ago
    Registered 8 years ago
    Data - Remove Duplicates, then specify your columns.

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

  • localnotail 27 Oct 2011 12:14:15 23,093 posts
    Seen 7 months ago
    Registered 6 years ago
    Ah ok, thanks delbert. I'll give that a try.

    Following my second coffee I had a thought that I might actually end up just highlighting the duplicates in the email coloumn with the supereasy conditional formatting button and then sorting them to the bottom and then highlighting duplicates in the name column and seeing if that makes it easier to spot them. I guess I was just being very lazy and yearning for magic over effort. The two extra columns version might be swifter though, cheers.

    Just writing it all down helped to make this simpler. I have no-one to discuss such things with in my office, it's all I can get them to use tab to move through spreadsheets.

    Edited by localnotail at 12:21:29 27-10-2011

    A strange game. The only winning move is not to play.

  • localnotail 27 Oct 2011 12:16:21 23,093 posts
    Seen 7 months ago
    Registered 6 years ago
    mrpon wrote:
    Data - Remove Duplicates, then specify your columns.
    I'll have a look, but I didn't realise you do that to cross-reference the values in each column. Will it not just take out the duplicates in each column (which isn't what I'm after).

    A strange game. The only winning move is not to play.

  • localnotail 27 Oct 2011 12:17:46 23,093 posts
    Seen 7 months ago
    Registered 6 years ago
    oooOOOooooOOooo

    Nope, that works. Mad. Coool, thanks mrpon.

    A strange game. The only winning move is not to play.

  • mrpon 27 Oct 2011 12:17:57 28,893 posts
    Seen 8 hours ago
    Registered 8 years ago
    No it totally removes the rows, dude.

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

  • delbert 27 Oct 2011 12:24:38 2,889 posts
    Seen 2 days ago
    Registered 11 years ago
    That feature must be post the dinosaur that is Excel 2003 then?
  • localnotail 27 Oct 2011 12:47:50 23,093 posts
    Seen 7 months ago
    Registered 6 years ago
    I guess so, I have Excel 2007 on my office PC.

    Windows XP mind. But that's ok, perfectly functional. I'd seen the Data - remove duplicates thing but just assumed it would take out every row that had a duplicate in, rather than cross-referencing the columns. But I've just tested it on some made up records it just now and it only took out the genuine repeat. Magic.

    A strange game. The only winning move is not to play.

  • RyanDS 27 Oct 2011 12:59:35 9,355 posts
    Seen 7 hours ago
    Registered 6 years ago
    Is there anyway to make Office 2010 usable? It is the worst iteration of office so far, and has reduced user functionality significantly.

    Everything it big fucking buttons and 17 layers of menus to get to the function you want, as opposed to the simple menu system of the older offices. (Not to mention that idiotic ribbon taking up ridiculous amounts of screen space.)

    As an example I was working on some ms queries, and what used to take 3 key presses to change now requires a mouse and no less than 6 different sub menus.

    Is there a skin or anything that can be applied to revert it?
  • X201 27 Oct 2011 13:10:46 15,370 posts
    Seen 7 hours ago
    Registered 9 years ago
    Office 2010 is a nightmare. I'm competent with Excel, Outlook et al, its my job to be. But 2010 is a bloody nightmare. I know what I need to do, but the bloody menus and ribbons etc turn a 30 second task into a bloody nightmare.

    We've taken the plunge at work and have almost entirely dropped MS Office in favour of Open Office. We just went for it and threw the users into it, we even re-labelled the Open Office programs Calc, Writer etc as Word and Excel. We hardly had a murmur from them. Only thing that cropped up was default document format, but that was soon solved.

    Saved us a fortune too. :)

    Edited by X201 at 13:11:54 27-10-2011
  • TechnoHippy 27 Oct 2011 13:23:34 14,714 posts
    Seen 7 hours ago
    Registered 11 years ago
    They tried that where I worked and after a day with OpenOffice I told them I would quite if they didn't give me Office back. Although in fairness this was a while ago, I'm sure OpenOrifice has improved since then.

    Still I love my Excel and VBA combo :-)

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

  • RabidChild 28 Oct 2011 12:43:28 2,292 posts
    Seen 11 hours ago
    Registered 9 years ago
    Can anyone please lend a hand with an excel conundrum I have? I'm sure there is a fairly straightforward way of doing this, but i'm a struggling exceltard.

    I have the following data in columns:

    Column A contains 'component ID' in the form of text. There are many replicates within the list.

    Column B contains the stock level in the form of a number.

    I want column C to contain the total number of stock for each component ID (not the number of times it appears in the list, like with countif, but the sum of the stock for each component). That means if a component is in the list twice, the entry in column C should be identical:

    Item A - 25 - 50
    Item B - 12 - 12
    Item A - 25 - 50
    Item C - 0 - 0

    Does anyone know which formula I can use in column C?

    Thanks!
  • mrpon 28 Oct 2011 12:48:15 28,893 posts
    Seen 8 hours ago
    Registered 8 years ago
    =SUMIFS(B1:B4,A1:A4,A1)

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

  • X201 28 Oct 2011 12:48:52 15,370 posts
    Seen 7 hours ago
    Registered 9 years ago
    SUMIF
  • RabidChild 28 Oct 2011 12:49:12 2,292 posts
    Seen 11 hours ago
    Registered 9 years ago
    Thanks, i've just discovered sumif and was off to try it - cheers :)
  • crashVoodoo 28 Oct 2011 12:51:22 3,905 posts
    Seen 4 hours ago
    Registered 13 years ago
    sumif ?

    a Nexus a day keeps the apple away

  • mrpon 28 Oct 2011 12:51:52 28,893 posts
    Seen 8 hours ago
    Registered 8 years ago
    Smurf?

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

  • RabidChild 28 Oct 2011 12:59:39 2,292 posts
    Seen 11 hours ago
    Registered 9 years ago
    Smurf did the job, thanks all!
  • Deleted user 6 January 2012 08:46:49
    I'm looking at this formula I've been given. I understand most of it, as it's conditional logic, cell lookup, and is also referencing other worksheets.

    =IF(LEFT(K24,6)="ELBSBI",SUMIF('Non_ESP Bills_ESP Cards'!W:W,'GL ELITE'!A24&'GL ELITE'!J24,'Non_ESP Bills_ESP Cards'!S:S)+SUMIF('ESP Bills_ESP Cards'!W:W,'GL ELITE'!A24&'GL ELITE'!J24,'ESP Bills_ESP Cards'!S:S),"")

    Just can't recall what the colons indicate between the column letters, eg W:W, S:S - does anyone know?

    Thanks
  • X201 6 Jan 2012 08:50:36 15,370 posts
    Seen 7 hours ago
    Registered 9 years ago
    The entire column
  • Deleted user 6 January 2012 09:10:38
    Ah yes, thanks. So what exactly is this part saying then:

    SUMIF('Non_ESP Bills_ESP Cards'!W:W,'GL ELITE'!A24&'GL ELITE'!J24,'Non_ESP Bills_ESP Cards'!S:S)

    I'm not sure how the condition here (which I presume is 'Non_ESP Bills_ESP Cards'!W:W, relates to the true and false statements.. any help?
  • X201 6 Jan 2012 09:17:28 15,370 posts
    Seen 7 hours ago
    Registered 9 years ago
    Sum the cells in Column S if the corresponding cell in column W equals 'GL ELITE'!A24&'GL ELITE'!J24


    'GL ELITE'!A24&'GL ELITE'!J24 refers to the contents of cells A24 and J24 on a sheet called 'GL ELITE'

    Basically its a concatenation, if A24 = "Joe" and J24 ="Bloggs"

    A24&J24 = Joe Bloggs


    The whole formula is a text matching exercise -

    eg row 3 would be..

    If cell W3 matches "Joe Bloggs" then sum cell S3

    Edited by X201 at 09:33:32 06-01-2012
  • Deleted user 6 January 2012 09:34:50
    Brilliant. Thanks very much! :)
  • Deleted user 24 January 2012 09:13:57
    Morning. Does anyone know how I can sort a spreadsheet to include the blank cells, so they appear at the top. The column is a date column, but any sort I try to do ignores the blank cells and just sorts the dates, leaving the blanks at the bottom. This worked fine in 2003 but can't seem to do it on 2010. I'm worried I'm missing something blindingly obvious..!

    Thanks.
  • X201 24 Jan 2012 09:47:59 15,370 posts
    Seen 7 hours ago
    Registered 9 years ago
    Reverse order?

    Hang on will check.
  • X201 24 Jan 2012 09:51:15 15,370 posts
    Seen 7 hours ago
    Registered 9 years ago
    Can't see it at the moment but a grubby get around may be to put

    =ISBLANK(E5)

    in the column next to it and use that in the sort criteria.
  • mrpon 24 Jan 2012 09:51:46 28,893 posts
    Seen 8 hours ago
    Registered 8 years ago
    Yeah or replace the blanks with a single space, that seems to work.

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

  • TechnoHippy 24 Jan 2012 09:53:26 14,714 posts
    Seen 7 hours ago
    Registered 11 years ago
    What order are you sorting? If it's newest to oldest then you can put a space in the empty cells and that would put the empty cells at the top.

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

  • mrpon 24 Jan 2012 09:54:29 28,893 posts
    Seen 8 hours ago
    Registered 8 years ago
    Snooze you lose.

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

  • TechnoHippy 24 Jan 2012 09:57:07 14,714 posts
    Seen 7 hours ago
    Registered 11 years ago
    :-)

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

  • Page

    of 10 First / Last

Log in or register to reply