Hi all,
Working on FDA adverse events database and have several Gig of excel files (from ASCII) that I am trying to sort and combine.
Basically Column A has the identifier and Column B the adverse event. Each identifier can have multiple adverse events.
I wish to convert the data such that there is only 1 identifier and each adverse event is listed across the columns.
Attached is a sample from the file that I am working with.
Thanks in advance!
BTW - please do not be scared off by my username... Have used it for years - previously a pharmacist that dispensed considerable amounts for various conditions.
Hi,
If I understand you correctly, then, based on your attachment, this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in cell C2 and copied down until you start to see blanks will give you a list of identifiers with duplicates removed:
=IFERROR(INDEX($A$2:$A$100,SMALL(IF(FREQUENCY(MATCH($A$2:$A$100,$A$2:$A$100,0),ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1),ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1),ROWS($1:1))),"")
Then, this formula in cell D2 and copied down and to the right, again until you start to see blanks and again array-entered:
=IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$C2,ROW($A$2:$A$100)-MIN(ROW($A$2:$A$100))+1),COLUMNS($A:A))),"")
P.S. I wasn't scared off by your username (though thanks for the clarification!)
Regards
Try the attached.
Thank you to both responders. The solutions work just as a require and saved me a great deal of time - there is no way I could have come up with these solutions. Also thanks for the very prompt replies!
I am a retired person,trying to learn excel.These kind of equations frighten me no end.what are you? some kind of Einstein?
@abkundargi
They really aren't that frightening, you know
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks