Maknib Posted August 5, 2011 Share Posted August 5, 2011 Hi guys, wondering if you can help me. i've been given a task at work in Excel and it is going to take me a long time in excel so i'm wondering if there's a PHP script i can create for this... basically the data i have looks like below on the left is the member numbers and on the right is the year they claimed... i need to find how many years each member has claimed.. so if member 12345 has years 2005 2007 2008 they have claimed 3 years. but if they have 2005 2005 2005 2007 2007 2008 2008 they still have 3 years.. can this be done simple some how? there is about 8000+ cells i need to go through manually counting. 014134 2009 014134 2009 014134 2009 014330 2010 014572 2006 014573 2008 014604 2010 014604 2010 014667 2004 014667 2005 014667 2006 014667 2006 014667 2006 014667 2006 014667 2007 014667 2007 Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 5, 2011 Share Posted August 5, 2011 Wow, thats gotta be rough. I would look into, I don't know how about performing specific excel tasks, but I am pretty sure you can just grab the data with PHP, do the math in php, and put back the data in a excel format. http://phpexcel.codeplex.com/ Quote Link to comment Share on other sites More sharing options...
Maknib Posted August 5, 2011 Author Share Posted August 5, 2011 Wow, thats gotta be rough. I would look into, I don't know how about performing specific excel tasks, but I am pretty sure you can just grab the data with PHP, do the math in php, and put back the data in a excel format. http://phpexcel.codeplex.com/ Thanks i was hoping for something like saving it as a .csv importing into a database and them some sort of query and loop . can't figure the formula.. something so it would make an array maybe with Array['memberNumber] => year and then looping through and counting like.. there's 4 occurances of member 12345 and only 2 unique years so the answer is 2. but i'm probably dreaming and will have to manually count. Quote Link to comment Share on other sites More sharing options...
Maknib Posted August 5, 2011 Author Share Posted August 5, 2011 ok i just used a Unique filter in excel and got myself from 8037 rows to 3800 now it looks like this, so all i need to do is be able to count how many times each member number appears... is that easy and doable? 000088 2007 000088 2008 000088 2009 000115 2009 000115 2010 000350 2011 000482 2004 000482 2005 000482 2006 000482 2007 000495 2010 000821 2010 001789 2009 001911 2006 001911 2009 001911 2011 Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted August 5, 2011 Share Posted August 5, 2011 It sure is, if you've inserted it into a MySQL db already. Did you go ahead and do that? SELECT member, COUNT(member) AS num FROM table GROUP BY(member) ORDER BY member ASC Quote Link to comment Share on other sites More sharing options...
Maknib Posted August 5, 2011 Author Share Posted August 5, 2011 It sure is, if you've inserted it into a MySQL db already. Did you go ahead and do that? SELECT member, COUNT(member) AS num FROM table GROUP BY(member) ORDER BY member ASC i haven't done that yet i need to get someone with admin rights to install WAMP for me. Quote Link to comment Share on other sites More sharing options...
Maknib Posted August 5, 2011 Author Share Posted August 5, 2011 It sure is, if you've inserted it into a MySQL db already. Did you go ahead and do that? SELECT member, COUNT(member) AS num FROM table GROUP BY(member) ORDER BY member ASC i haven't done that yet i need to get someone with admin rights to install WAMP for me. Ok im getting WAMP.. so this sql query will count each member and then tell me how many occurences of that member number there are? so if the data is 123 123 123 123 154 154 154 it will return something like... 123 = 4, 154 = 3 ? or just 4 3 ?? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.