How manipulate data export in rows to become in columns

[lucky245]

I have an excel spreadsheet (example attached) which is populated by lab equipment. The key field is the specimen number and specimen type. Currently for every antibiotic there is a row with its result. My output is 3 complete worksheets so a lot of data. I need to create a database with one row per specimen number/type to include whether it is S/R/or I for each type of antibiotic. The example probably makes more sense. but I'm assuming that 65000 rows * 3 sheet can then be reduced to around 18,000 rows.

Any help or pointers appreciated. I do this run every week so spend hours reformatting by hand.


thanks

 

[DavidAtWork]

Your current format sheet should represent what you would see in a database table and is correct, your required output sheet can be reproduced from a table using a crosstab query by setting the result as The Value, antobiotic would be your Column Value and specimen no, specimen type and source would be set as Row Values

David

 

[namliam]

I understand what you are trying to do, the question is... SHOULD you do it?

I think that you would be better off "simply" importing the data as is and create a crosstab query to show you the results you want to have. That way if there comes additional data you can simply add it and run again.

Edit: David types faster

 

[lucky245]

You both gave very valid answers and I was just being stupid I couldn't get my result field to diplay the sensitivity but just a number. Might have helped if I had used "First" rather than Sum or count.

Thank you

I always try to make things more complicated than they are.

 

[JHB]

Import the data as they are and run a cross tab query on it.
Attached is a small example, open the database and run the only query in it.
Edit: I see, you have already got answers!