Appending values by matching row values

[madjmckinney]

Hi all,

I have attached a photo of my filtered data. In the photo, record 1 and 2 have the same "ST_CASE" value, so I am trying to append the different "DRUGACTQTY" values to new columns while deleting the duplicate "ST_CASE" values. Is there any function that will do this quickly? Thank you!

 

[stataltgee]

@madjmckinney  your question was not clear. Do u want to change the same values to something like 2 different numbers? Pls elaborate more about the requirement.

[madjmckinney]

Apologies. I attached two more snips showing more clearly what I want to do. I would like to use the "DRUGRESNAME" value to create a new column, and place the "DRUGACTQTY" values into those columns for each "ST_CASE" value. Instead of having multiple rows for one "ST_CASE" number, I would like the data to have one row for each case, and multiple columns to show the drug quantity for each specific drug in each case.

[Pilsner · Accepted solution]

Hello @madjmckinney 

I think you can achieve what you're after using the crosstab tool. 

If you set the Drug name column as your header, then the quantity column as the values, you get  the following result: 

You can then use the select and sort tools to arrange the rows and columns as desired. 

I've attached the workflow below to try and assist further. 

Please let me know if you have any questions.

Regards - Pilsner

[madjmckinney]

Thank you! I used crosstab followed by the summarize tool and achieved exactly what I needed. I appreciate the help!

[Pilsner]

Excellent! Glad I could help.