Multiple Records problem

[Prufrock]

I have data that exports out of a Govt database into Excel. The problem is that it exports a complete record for each person for each time they have a vaccination in a set of 3. So if they have had Vaccination 1 and 2 you get two duplicate row records except for the Vaccination column. This includes everything duplicated address etc. I need to build a mail list of those who have had only one vac, or 1 and 2. I have tried all sorts of ways using filtering, autofiltering etc. Can any one help.
e.g.
First Name Last Name Vaccinations
John Bell 1
John Bell 2
John Bell 3
Mary Smith 1
Mary Smith 2

In the end I just want one record for each of those who have not had shot 3 and another list which shows each of those who have not had 2 or 3.

I cannot change the database and have to work with how I get it in Excel.

 

[Gavona]

Pivot table maybe. If you need to distinguish vaccination 1 from vaccination 2 then have vaccinations as a column field. Have Count of Vaccinations in the data area.





Gavin

 

[macropod]

Hi Prufrock,

Assuming your present data set is in columns A-C, starting at row 1, if you put the following formula in D2 and copy down as far as needed, you'll be able to use that column to filter according to the number of vaccinations each client has had.

You also mention that this is for a mail list. If this is for a mailmerge document in Word, you'll need to test column D's vaccination counts with an IF field to determine which letter to send and combine that with a SKIPIF field to go to the next record if the content of Column D is blank.

Cheers

[MS MVP - Word]

 

[macropod]

Hi Prufrock,

I forgot to mention it's an array formula - so you'll have to use Ctrl-Shift-Enter when you input it.

Cheers

[MS MVP - Word]

 

[Prufrock]

Thanks for your assistance but I think you missed putting the formula in?

 

[macropod]

Doh!
=IF(AND(A2<>A1,B2<>B1),SUM(IF((A$1:A$6=A2)*(B$1:B$6=B2),1,)),"")

Cheers

[MS MVP - Word]

 

[Prufrock]

Thanks for that worked like a charm and saved a lot of wasted time. Array formulas are very cool but take a while to work out.

Thanks Again.