Excel AutoFilter Help Needed 1

[Charagrin]

Please help.

I have a large amount of data that I am attempting to sort into 3 groups using the autofilter. The data results are answers to five questions rating 1 to 4. 1 and 2 are negative results while 3 and 4 are positive. The 3 groups I require are :-

A. Any negative results that are only in questions 1, 2 and 3.
B. Any negative results that are only in questions 4 and 5.
C. Any negative results that spread between 1, 2, 3 and 4, 5.

The data looks like this.

Q1 Q2 Q3 Q4 Q5

3 2 2 3 3
3 3 1 3 3
3 3 2 3 3
3 3 3 1 1
3 3 3 1 1
3 3 3 2 3
4 4 2 2 2
3 3 2 2 3
1 2 1 1 4


I am finding the results for A by choosing on the autofilter for questions 4 and 5 (is greater than or equal to 3).
I'm doing the same for B but against questions 1, 2 and 3.

After creating each list I copy them into there own worksheet.

What I want to do is to be able to list all the results for C, which is the remainder of results once A and B have been removed.

I would be very grateful if someone could point out how to do this. The annoying thing for me is that I did it a few days ago, only I can't remember how. I am sure it was just using the autofilter. I kept the results from when I did it but not the method.

Please help before I go insane.

 

[SkipVought]

Hi,

This COULD be accomplished quite easily IF your data were NORMAILZED.

Column A: The responder (Row)
Column B: The question
Column C: The response

NORMALIZE Your Table using the PivotTable Wizard faq68-5287 describes HOW to NORMALIZE your data.

BTW, it took me about 15 seconds to normailze your data using the FAQ procedure above.

Once normalized, you can use a variety of Data Analysis techniques. I'd suggest using Data/Get External Data to query your new table using the criteria that you outlined above.

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[SkipVought]

Here are the 3 queries

SELECT 
  Row
, Question
, Answer

FROM `D:\My Documents\Stuff\saveastest`.`Sheet3$` 

WHERE Answer>=3
  AND Question In ('Q4','Q5')

---------------------------------------------

SELECT 
  Row
, Question
, Answer

FROM `D:\My Documents\Stuff\saveastest`.`Sheet3$` 

WHERE Answer>=3
  AND Question Not In ('Q4','Q5')

---------------------------------------

SELECT 
  Row
, Question
, Answer

FROM `D:\My Documents\Stuff\saveastest`.`Sheet3$` 

WHERE Answer<3

All told, I spent about 3 minutes starting with your data, normalizing it, designing 3 queries using Data/Get External Data.


Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[Charagrin]

Cheers, I'll have a good look at this tonight.