Pivot table Relationships

Hello All,

I'm new to the forum. My excel skills are probably ok but I'm still very much learning.

Just started working for a new company and trying to help create a useful pivot table from two very large and useless spreadsheets.

Ill try and keep this short. the company is a medical company so we have a spreadsheet which contains information regarding every procedure where this new product has been used. we have lots of information including the device serial number and also other information like hospital, type of procedure and then comments. Then I have a second spreadsheet that has complaints we have had with the product or something that relates to the product. on this spreadsheet we have the device serial number and then a few other columns of information regarding the hospital and comments on the issues. Both spreadsheets need simplifying so that there isn't so much free text and more uniform input so its easier to report on.

to make it clear we have around 60 units and each unit has its own serial number.

So knowing that both spreadsheets have the device serial number I thought I could put them both on the same workbook as different sheets and create them as tables so I could then create a pivot and create a relationship using the serial numbers. However I'm having the following error:

"both selected columns contain duplicate values. at least one of the columns selected must contain only unique values to create a relationship between tables"

I'm really not sure how to fix this issue. both spreadsheets will have the serial numbers repeated multiple times. on the procedures sheet the product will be used for lots of different procedures on patients and on the complaints sheet we could have had multiple issues while using the same product on different days etc. I'm thinking that this is probably why I have this issue.

ultimately I'm trying to report on each serial number and how many procedures have been done with this serial number and work out how many complaints we have had for that product.

fingers crossed I have explained this properly and that there is a fix. currently I can do this manually as this is a new product which have only a few sales and used only a few times but as time goes on we could be selling hundreds of these products and each one could be used multiple times a day so the work I do now will really help for the future.

thanks in advance.

dan

Welcome to the forum, Dan. It would help a lot to attach sample spreadsheets with non-sensitive data. We don't need everything, but we do need enough data to illustrate the point. Also mocking up or describing what you want the results to look like will also go a long way to our understanding of the requirements.

Depending I what is provided, this may or may not be a relevant question, but how do you get the data onto the sheets? Is it entered manually, imported or copied and pasted in?

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

There is a bit of “apples and oranges” here. We have a many-to-many relationship, For example, the Complaints data has 10 complaints for CU 101 and the Procedure data has 27 procedures.

There is also a bit of mismatch in our date formats. Rows 2 and 4 have an actual date for Date Completed. I’m showing January 11, 2017 and July 21, 2016 respectively. These seem to be correct based on the date closed. Most of the other dates are actually strings and they are in dd/mm/yyyy format which I will have to work around with my American settings. .

I know what is causing this: Excel is trying to “help me out” by determining what kind of data is in the cells by looking at the first several rows. When you import this data with your settings, it should come over as all actual dates. So I will convert all to actual dates and work from there.

Now to figure out how to make sense of the data. The dates will come into play.

As I see it, there a procedure occurs on a certain data, say June 14. If an issue occurs, it is written up and entered into the database, say on June 16. It is eventually closed, say on October 12. The problem is tying the June 14 procedure with the June 16 write-up.

One metric I can come up with is how many times the procedure was opened after it had been closed. This would be semi-independent of the procedures performed. I saw semi because the more you do a procedure, the more likely you are to find something wrong with it.

I’ll have to process this through the gray matter to see if I can come up with any meaningful metrics. Any insights on comparing dates and procedures you have are welcome.

One thing however: I do see you are using tables – bravo! That makes life a lot easier.

I came up with this weird metric. It joins data from the Complaints Data on the Procedure data sheet.

I added three helper columns:

Openings: these are the number of complaints for the CU that were opened after the Procedure Date. Naturally, this would be a declining number.

Proc After: This is the number of procedures for the CU processed after the procedure in the same row. Naturally, this is also declining.

Ratio: this is Openings / Proc After.

I don’t know how valid this metric is. It does take into account causality (number of complaints opened after a procedure) and opportunity (number of the same procedure done after the openings). It’s about the only thing I can think of since there is no way that I can see of linking a complaint report to a previous procedure.

I put this information into a pivot table and grouped the dates by month and year. I inserted a couple of slicers to make the navigation easier.

Don’t worry about the (US) in the name of the spreadsheets. It just means when I look at it, the dates are mm/dd/yyyy format. When you look at it, the format should be dd/mm/yyyy.