Advice, Can I use pivot table or must I use Vba?

I wish to take data in its line by line format to a single line where possible. I have a list of antibiotic resistance results to specific antibiotics and currently they are a single line per antibiotic even though its the same date and person etc. I have tried using a pivot table which appears to nearly work except instead of showing the sensitivity result is shows a number. I have enclosed an example (anonymised).

Can a pivot table do what I need (if so how) or should I look to try to code a solution? My dataset is over 10,000 lines.

Thanks

Sorry realised this is wrong part of forum.

Hi,

Is this the sort of layout you're after?

I am now aware I cannot use a pivot table for this, so VBA it is , sorry Richard typing at time of response. I have been informed I cannot use a pivot table although the format in my example is the result I am after with the S's or R's where the pivot table showed the count as shown in new attachment

What exactly are you trying to show with your data? Perhaps mock up a simple example so that we can see the end goal.

I would be surprised if a PT couldn't be arranged to show you what you want. I'm not clear whether you are wanting to count any of the fields.

Also just noticed I missed the results column of the previous upload. It's corrected here.

A pivot table is a great tool to summarize and organize data, but since you don't have "numerical" data to summarize, it will not really do you too much good. A pivot only works effectively if you add a field to the Data (or Values) Drop Area. As you noted, dropping the Results field in these switches your data to values!!?

If you sort all six columns, and then delete complete record duplicates (duplicates across all six columns per record), you will basically get a view as per your need.
Having got Excel 2003, you won't have access to a great feature called Remove Duplicates. However this can be done in a different way using formulas or code.

How does my analysis sound? If you believe it can work, let me know if you need assistance on the sorting and removing of duplicates...

See my last attachment for an idea of the end result required. Thankyou

Problem solved Access Crosstab does the job thanks.