Page 7 • *Official* Excel help thread • Non-Gaming • Software • Eurogamer.net

delbert 27 Oct 2011 12:00:50 2,893 posts
Seen 18 hours ago
Registered 18 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 37,367 posts
Seen 5 hours ago
Registered 15 years ago

Data - Remove Duplicates, then specify your columns.

localnotail 27 Oct 2011 12:14:15 23,079 posts
Seen 3 weeks ago
Registered 13 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

localnotail 27 Oct 2011 12:16:21 23,079 posts
Seen 3 weeks ago
Registered 13 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).

localnotail 27 Oct 2011 12:17:46 23,079 posts
Seen 3 weeks ago
Registered 13 years ago

oooOOOooooOOooo

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

mrpon 27 Oct 2011 12:17:57 37,367 posts
Seen 5 hours ago
Registered 15 years ago

No it totally removes the rows, dude.

delbert 27 Oct 2011 12:24:38 2,893 posts
Seen 18 hours ago
Registered 18 years ago

That feature must be post the dinosaur that is Excel 2003 then?

localnotail 27 Oct 2011 12:47:50 23,079 posts
Seen 3 weeks ago
Registered 13 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.

RyanDS 27 Oct 2011 12:59:35 14,074 posts
Seen 13 hours ago
Registered 13 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 22,150 posts
Seen 8 hours ago
Registered 16 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 19,245 posts
Seen 3 weeks ago
Registered 18 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 :-)

Deleted user 28 October 2011 12:43:28

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 37,367 posts
Seen 5 hours ago
Registered 15 years ago

=SUMIFS(B1:B4,A1:A4,A1)

X201 28 Oct 2011 12:48:52 22,150 posts
Seen 8 hours ago
Registered 16 years ago

SUMIF

Deleted user 28 October 2011 12:49:12

Thanks, i've just discovered sumif and was off to try it - cheers

Deleted user 28 October 2011 12:51:22

sumif ?

mrpon 28 Oct 2011 12:51:52 37,367 posts
Seen 5 hours ago
Registered 15 years ago

Smurf?

Deleted user 28 October 2011 12:59:39

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 22,150 posts
Seen 8 hours ago
Registered 16 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 22,150 posts
Seen 8 hours ago
Registered 16 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 22,150 posts
Seen 8 hours ago
Registered 16 years ago

Reverse order?

Hang on will check.

X201 24 Jan 2012 09:51:15 22,150 posts
Seen 8 hours ago
Registered 16 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 37,367 posts
Seen 5 hours ago
Registered 15 years ago

Yeah or replace the blanks with a single space, that seems to work.

TechnoHippy 24 Jan 2012 09:53:26 19,245 posts
Seen 3 weeks ago
Registered 18 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.

mrpon 24 Jan 2012 09:54:29 37,367 posts
Seen 5 hours ago
Registered 15 years ago

Snooze you lose.

TechnoHippy 24 Jan 2012 09:57:07 19,245 posts
Seen 3 weeks ago
Registered 18 years ago

*Official* Excel help thread • Page 7

Official Excel help thread • Page 7