Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am working in MS Excel 2007 with data regarding a series of forum posts. Each post is represented by a row of data that includes a unique ID for each post, the unique ID of the user that posted it, the unique ID of the discussion as well as several columns of descriptive variables. One of these descriptive variables is whether or not the post supports the discussion (is a pro or a con). There are several thousand users and several thousand posts.

1. I want to find the instances where users have made more than one post in the same discussion. (Probably a few dozen instances)

2. I want the instances in which users have posted both a "pro" and a "con". (Probably a handful)

Question number one is the important one. The second will probably be easy once I've figured out #1 since the instances are few.

Alternatively, how could I accomplish this with Access (which I am much less familiar with)?

share|improve this question
    
What have you tried? –  CharlieRB Nov 14 '12 at 16:56
    
I figured Access might be the best tool for this and gave that a try. I am however an absolute beginner in Access, so I soon decided to focus more on Excel. In Excel, I am trying to set it up in pivot tables, which admittedly I have little experience with. But I guess you can't go through life without pivot tables, so learning it is worth the effort. I'm getting closer, but still haven't figured it out. –  Stoney Nov 14 '12 at 17:27
add comment

1 Answer

up vote 2 down vote accepted

This is easier in Access than in Excel.

In Access, import your data from Excel using the import Wizard. Once your data is in a table, create a new query, go to SQL view, and use a query like the following to find users posting more than once in a single discussion:

SELECT DiscussionID, UserID, Count(PostID) as Number_of_Posts FROM Sheet1
GROUP BY DiscussionID, UserID
HAVING Count(UserID)>1;

When executed, this will return in table form the DiscussionID, the UserID, and the number of posts by that user in the discussion for each who has more than one post. Of course, you'll have to change the table and field names to match your database.

To find the users who have posted both pro and con in a single discussion, you can use the following query:

SELECT DiscussionID, UserID, SUM(Number_of_Posts) as Posts
FROM (SELECT DiscussionID, UserID, Pro_Con, Count(PostID) as Number_of_Posts FROM Sheet1
      GROUP BY DiscussionID, UserID, Pro_Con)
GROUP BY DiscussionID, UserID
HAVING Count(UserID)=2;

This is a bit of a hack on my part; I'm sure there has to be a more efficient query to do the job, but nonetheless, this will work.

share|improve this answer
    
Thank you! It all worked and you nearly guessed the names of the fields correctly. –  Stoney Nov 14 '12 at 19:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.