Go Back   English Forum Switzerland > Off-Topic > Off-Topic > General off-topic  
Reply
 
Thread Tools Display Modes
  #1  
Old 23.10.2012, 23:46
Trikk's Avatar
Member
 
Join Date: Oct 2009
Location: Zürich
Posts: 109
Groaned at 0 Times in 0 Posts
Thanked 28 Times in 26 Posts
Trikk has earned some respectTrikk has earned some respect
Help with Excel

Hi there!

I used to know Excel well enough, but as I haven`t used it for several years now, I have forgotten most of the "formulas".

Now somebody has asked me for help and I`m not sure how would be best to filter the tables (doing it manually seems to be easiest, but way too many rows...

I would be grateful when somebody could point me into right direction.

In the File, there are people names in the first row and in other rows alot of other values plus the year of the most recent data collected.

Now I would need to filter this table so that there would be per person the most recent information.

For example:
1. Megan - 2583321321321 - 2010
2. Megan - 6546545454646 - 2012
3. Harry - 65456456466 - 2011
4. Peter - 5465454654545 - 2012
5. Peter - 65645545465 - 2011
6. Mary - 656456454545 - 2011
7. Mary - 6545646545454 - 2010
8. Laura - 6564546545 - 2009

The filtered data would need to look like:
1. Megan - 6546545454646 - 2012
2. Harry - 65456456466 - 2011
3. Peter - 5465454654545 - 2012
4. Mary - 656456454545 - 2011
5. Laura - 6564546545 - 2009

It should be probably something with the logic of IF not 2012 then 2011, if not 2011 and 2012, then 2010 etc. But I can`t figure it out

Thanks in advance!
Reply With Quote
  #2  
Old 23.10.2012, 23:52
adrianlondon's Avatar
Forum Legend
 
Join Date: Nov 2009
Location: Basel
Posts: 9,131
Groaned at 170 Times in 153 Posts
Thanked 25,643 Times in 6,892 Posts
adrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond repute
Re: Help with Excel

Or, easier (I don't know Excel formulas but can apply rough programming logic to things anyway) ... sort by year then remove duplicate named rows.
Reply With Quote
  #3  
Old 23.10.2012, 23:54
Trikk's Avatar
Member
 
Join Date: Oct 2009
Location: Zürich
Posts: 109
Groaned at 0 Times in 0 Posts
Thanked 28 Times in 26 Posts
Trikk has earned some respectTrikk has earned some respect
Re: Help with Excel

Thanks. That`s my last option as the table has way too many rows...
Reply With Quote
  #4  
Old 23.10.2012, 23:56
adrianlondon's Avatar
Forum Legend
 
Join Date: Nov 2009
Location: Basel
Posts: 9,131
Groaned at 170 Times in 153 Posts
Thanked 25,643 Times in 6,892 Posts
adrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond repute
Re: Help with Excel

Quote:
View Post
Thanks. That`s my last option as the table has way too many rows...
I meant ... you could write a formula to do that though, right? Just click the column to sort by year. Then stick a formula in the "name" box to somehow delete the row if the "name" is the same as the text in the box above.
Reply With Quote
  #5  
Old 24.10.2012, 00:03
Trikk's Avatar
Member
 
Join Date: Oct 2009
Location: Zürich
Posts: 109
Groaned at 0 Times in 0 Posts
Thanked 28 Times in 26 Posts
Trikk has earned some respectTrikk has earned some respect
Re: Help with Excel

But if I sort it by year it will look like:

2. Megan - 6546545454646 - 2012
4. Peter - 5465454654545 - 2012
3. Harry - 65456456466 - 2011
5. Peter - 65645545465 - 2011
6. Mary - 656456454545 - 2011
1. Megan - 2583321321321 - 2010
7. Mary - 6545646545454 - 2010
8. Laura - 6564546545 - 2009

Then I can`t just delete off the one "above"? If there is a formula for that then it would be great, but I`m way too rusty for remembering it
Reply With Quote
  #6  
Old 24.10.2012, 00:25
FrankZappa's Avatar
Forum Veteran
 
Join Date: Feb 2008
Location: France, near Geneva
Posts: 865
Groaned at 8 Times in 7 Posts
Thanked 2,777 Times in 728 Posts
FrankZappa has a reputation beyond reputeFrankZappa has a reputation beyond reputeFrankZappa has a reputation beyond reputeFrankZappa has a reputation beyond reputeFrankZappa has a reputation beyond reputeFrankZappa has a reputation beyond repute
Re: Help with Excel

1. Get a list of all the different people, using a copy of all the data and then Data and Unique.
2. Sort the full list by year
3. Split the list into separate years.
4. Look for each person in your unique person list in the list of the most recent year. The forumula =VLOOKUP(UniquePersonName, $LIST,2) returns the wanted number found in the 2nd column of the YearList.
5. If the name is not found you'll get #NA. For the remaining unfound numbers look in the 2nd most recent year
And so on....

There may well be more elegant solutions .
Reply With Quote
  #7  
Old 24.10.2012, 00:37
Trikk's Avatar
Member
 
Join Date: Oct 2009
Location: Zürich
Posts: 109
Groaned at 0 Times in 0 Posts
Thanked 28 Times in 26 Posts
Trikk has earned some respectTrikk has earned some respect
Re: Help with Excel

Thanks, will try tomorrow if nothing "more elegant" shows up (too tired already), but it sounds already better than manual filtering
Reply With Quote
  #8  
Old 24.10.2012, 00:39
adrianlondon's Avatar
Forum Legend
 
Join Date: Nov 2009
Location: Basel
Posts: 9,131
Groaned at 170 Times in 153 Posts
Thanked 25,643 Times in 6,892 Posts
adrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond repute
Re: Help with Excel

Quote:
View Post
But if I sort it by year it will look like:

2. Megan - 6546545454646 - 2012
4. Peter - 5465454654545 - 2012
3. Harry - 65456456466 - 2011
5. Peter - 65645545465 - 2011
6. Mary - 656456454545 - 2011
1. Megan - 2583321321321 - 2010
7. Mary - 6545646545454 - 2010
8. Laura - 6564546545 - 2009

Then I can`t just delete off the one "above"? If there is a formula for that then it would be great, but I`m way too rusty for remembering it
Well,I meant sort by name and year No formula needed, just click on the columns (all 3 of them) and click the custom sort. Then select columns A and C.
Reply With Quote
  #9  
Old 24.10.2012, 00:55
mirfield's Avatar
Moddy Wellies
 
Join Date: Apr 2007
Location: North Yorkshire
Posts: 8,717
Groaned at 53 Times in 47 Posts
Thanked 9,886 Times in 3,634 Posts
mirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond reputemirfield has a reputation beyond repute
Re: Help with Excel

I don't have excel to hand and I'm typing using an iPad, so this might need adjusting.


Assuming name is in column a and you have 1 row header

Sort by year (descending)

In first data row add formula in a new column
=if(countif(a2,a$2:a2)=1,"First","")

Copy the formula down to the bottom
So, for example, formula on row 999 is
=if(countif(a999,a$2:a999)=1,"First","")

What this should do is count the instances of the name in that row and above. If it is 1 then this is the first instance and "First" will be the result.

I can't remember the exact syntax of countif, but with the explanation, it shouldn't take much figuring out.

You could even be flash and use indirect to get the current row instead of relying on the copy to increment the row, but I definitely don't remember that syntax at bedtime.
__________________
Nothing of value kept in this post overnight.
Reply With Quote
  #10  
Old 24.10.2012, 01:27
Tilia's Avatar
Forum Legend
 
Join Date: Nov 2007
Location: ZH
Posts: 2,746
Groaned at 75 Times in 42 Posts
Thanked 2,649 Times in 1,194 Posts
Tilia has a reputation beyond reputeTilia has a reputation beyond reputeTilia has a reputation beyond reputeTilia has a reputation beyond reputeTilia has a reputation beyond reputeTilia has a reputation beyond repute
Re: Help with Excel

Create a pivot table and set number value to max on year?
Reply With Quote
  #11  
Old 24.10.2012, 01:47
Gordon Comstock's Avatar
Senior Member
 
Join Date: May 2008
Location: Geneva
Posts: 373
Groaned at 0 Times in 0 Posts
Thanked 611 Times in 252 Posts
Gordon Comstock has an excellent reputationGordon Comstock has an excellent reputationGordon Comstock has an excellent reputationGordon Comstock has an excellent reputation
Re: Help with Excel

If you first sort the data by name and then date, such that your data looks something like this* (numbers left in to show, the difference)

1. Megan - 2583321321321 - 2010
2. Megan - 6546545454646 - 2012
3. Harry - 65456456466 - 2011
5. Peter - 65645545465 - 2011
4. Peter - 5465454654545 - 2012
7. Mary - 6545646545454 - 2010
6. Mary - 656456454545 - 2011
8. Laura - 6564546545 - 2009

all you have to do is search for a change in name, ie see whether the next name in the list is the same.

So assuming the names are in column A, and we're starting at row 1, you need another column with the formula =if(a1<>a2,a1,""), in the topmost cell of the new column. Then drag the formula down.

1. Megan - 2583321321321 - 2010 -
2. Megan - 6546545454646 - 2012 - Megan
3. Harry - 65456456466 - 2011 - Harry
5. Peter - 65645545465 - 2011 -
4. Peter - 5465454654545 - 2012 - Peter
7. Mary - 6545646545454 - 2010 -
6. Mary - 656456454545 - 2011 - Mary
8. Laura - 6564546545 - 2009 - Laura

You can then copy and paste special using values the column you've just created. Then you can simply sort on that column.

*I realise that in the first table above I've been too lazy to actually sort by name and year (that would be Harry, Laura....Peter) but you get the idea.

Last edited by Gordon Comstock; 24.10.2012 at 01:50. Reason: Footnote ref
Reply With Quote
  #12  
Old 24.10.2012, 19:47
Trikk's Avatar
Member
 
Join Date: Oct 2009
Location: Zürich
Posts: 109
Groaned at 0 Times in 0 Posts
Thanked 28 Times in 26 Posts
Trikk has earned some respectTrikk has earned some respect
Re: Help with Excel

Quote:
View Post
Create a pivot table and set number value to max on year?
Any ideas why the Pivot Table tab is not in my Excel 2010 Starter? I googled and found out where it should be, but it simply isn`t there!
Reply With Quote
  #13  
Old 24.10.2012, 19:56
adrianlondon's Avatar
Forum Legend
 
Join Date: Nov 2009
Location: Basel
Posts: 9,131
Groaned at 170 Times in 153 Posts
Thanked 25,643 Times in 6,892 Posts
adrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond reputeadrianlondon has a reputation beyond repute
Re: Help with Excel

Just do it the easy was (sort by name and year then formula to remove duplicates). I onlysay that because pivot tables are like black magic to me. How big's the file? Emailable?
Reply With Quote
  #14  
Old 24.10.2012, 20:23
Newbie 1st class
 
Join Date: Sep 2010
Location: basel
Posts: 25
Groaned at 0 Times in 0 Posts
Thanked 7 Times in 6 Posts
mantri has no particular reputation at present
Re: Help with Excel

Use filter and then advanced filter...no need to go into pivot tables. First sort the year in descending order using normal filter. And then use the advanced filter on the column "Name" as shown in the file enclosed. hardly took 5 min.
Attached Files
File Type: zip Advanced Filter.zip (6.4 KB, 41 views)
Reply With Quote
  #15  
Old 24.10.2012, 20:52
Trikk's Avatar
Member
 
Join Date: Oct 2009
Location: Zürich
Posts: 109
Groaned at 0 Times in 0 Posts
Thanked 28 Times in 26 Posts
Trikk has earned some respectTrikk has earned some respect
Re: Help with Excel

Thank you all, managed to solve the problem!

Special thanks to Gordon for helping me via e-mail! genius of simplicity!
Reply With Quote
Reply

Tags
excel




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Microsoft Excel Cashboy Other/general 7 13.01.2010 10:39
Need help with bike, I offer help with cars and computers! emilian Transportation/driving 19 01.07.2008 00:43


All times are GMT +2. The time now is 15:13.


Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO 3.1.0