Aligning data in different rows to corresponding column

Happy New Year forum members. I am new to the forum, so I apologize if I put my question under the wrong subforum. I hope someone can help me with my query.

I have a large dataset (over 10,000 isolates {rows}) that I extracted from an online database.
Several of the resulting columns have data that is comma delimited (see column B in the included sample data file) with lists of different sizes (the data is antibiotic susceptibility profiles if it makes it easier to understand).
I separated this data into the next columns using DATA --> Text to Columns. Because the data that was separated contained lists with different antibiotics in the profile for each sample, I want to organize it so the same antibiotics for each row are aligned under column headers with the same name. I manually went through the data to add column headers that corresponded to all the antibiotics utilized in the data (see row 1, columns C through BM. FYI- This includes the antibiotic names from the whole dataset, not just the sample data set you have).

I have manually rearranged the data in rows 2 and 3 for Isolate 1 & 2 to show how I would like the data to look with the susceptibility data for each antibiotic available for an isolate aligned under the Antibiotic name (I plan to remove the antibiotic name from the data cells using the replace function leaving just the letter after the = once the data is properly aligned; e.g. penicillin=R will just say R). Rows 4 through 22 have been separated, but have not been reorganized/aligned. Is there a way to automatically align the data for each isolate into the corresponding column?

I often take the brute force approach to data organization and manually move things, but this data set is so big I thought I should find out if there is a more efficient and less error-prone way to do this. Thank you in advance for any help you can provide me.

Sincerely,
Michele

Hi Glenn.
Yes I am on a Mac at home (where I am now because of a snow day); however, my work computer is a PC.

Michele

There are 4 suffixes- R, S, capital I, and ND.

I do not think it does, Glenn. If I put =TEXTJOIN into a cell it does not recognize it.

The work computer is Microsoft Office 2019, I think.

Prior to alignment, is it possible to query all the data to combine cells that have more than one phenotype for the same antibiotic within the same isolate? Have it read ampicillin-sulbactam=R,S instead of ampicillin-sulbactam=R,ampicillin-sulbactam=S. Once again I was thinking to do this manually, but if it is possible to do this automatically, that would be awesome. I know that happens in quite a few places in the large dataset.

I would think 3 should be the maximum number of phenotypes an antibiotic could have (resistant, sensitive, and intermediate). ND (not determined) would be the same as not being listed, so I am not sure why it was included in the online database in the first place. ND entries are not useful, and I plan to eliminate those. I was thinking to combine the data for multiple phenotypes of a single antibiotic, so I could tell which isolates had multiple phenotypes at a glance once I remove the antibiotic names in the data. Ultimately, I am comparing these phenotypic results to the genotypic results from the same isolates.

That would be okay. I can always do some additional "cleaning" of the data once it has all been arranged in a more usable format.

I won't be able to do anything with the data until tomorrow anyway since I will need to work computer to do this with. I greatly appreciate the help you are giving me!!

Hi Glenn.
I am at my work computer and it is running Excel 2016, but this still seems to work. I will try it on the larger data set and see how it does. I will let you know.

I would love to know what is happening at each step so I might be able to use a similar function in the future for a similar problem (if it is not too hard to explain in text).

Thanks!

Hi Glenn.
So a couple of things...
With the other columns that are in the large data set, I am afraid I may run out of column spaces having multiple columns per antibiotic (I found 3 other phenotype identifiers and several more antibiotics which I have added to the sheet 2 list). I have started going through the larger data set to combine the antibiotic entries that have multiple phenotypes (ampicillin-sulbactam=R/S instead of ampicillin-sulbactam=R,ampicillin-sulbactam=S). This would no longer require multiple columns per anitbiotic, right? So in the data squares instead of X, can we have it display the text after the "=" ? How would this change both of the functions you wrote previously (hopefully easy fix)?

Okay, so maybe this will make life easier. I just downloaded Microsoft 365 on my MAC from my institution site (I work at a University). Now how do I do it to get one antibiotic per column?

It was free. My MAC works better for some genomic analysis work I do. Maybe I just need 2 computers.
It looks as if it is cloud-based, so I should be able to access it from my work PC when I get back into the building (quarantining for possible Covid contact).

How would you do this using O365 to get one antibiotic per column?

Congratulations!! I am no where near 200... well done!!

Good morning Glenn.
I came into my work computer this morning to try out the O365 from there. When I open the file from your O365 post, I see the results, but as soon as I click on any cell, all the phenotype data cells disappear (all I see is the comma delimited cells in Column B and the column headers in row 1), so I can't mess around with it to see what it is doing. Also, it will open in Excel 2016 and appears the way I was hoping it would but it opens in protected view; as soon as I enabling editing, the values in the phenotype cells disappears
Attachment 762249
I was able to copy and paste the cells in protected view to a new Excel file and the cells remained as the should. So if I get it to work for the full data set, I should be able to capture the results to be opened in a more easy-for-me-to-access version of Excel.

While I am here, I thought I would try this on the full data set. When I went to the full data set and copied the appropriate formula into the cells for the antibiotic names (=IFERROR(INDEX(Antibiotic,COLUMNS($W$1:W1)),"")) and for the first couple of isolates (after changing =IF(C$1="","",IFERROR(TEXTJOIN(", ",,SUBSTITUTE(FILTERXML("<A><B>"&SUBSTITUTE($B2,",","</B><B>")&"</B></A>","//B[contains(., '"&C$1&"="&"')]"),C$1&"=","")),"")) to =IF(W$1="","",IFERROR(TEXTJOIN(", ",,SUBSTITUTE(FILTERXML("<A><B>"&SUBSTITUTE($V2,",","</B><B>")&"</B></A>","//B[contains(., '"&W$1&"="&"')]"),W$1&"=","")),"")) so they match with the proper cell source (the comma deliminated list column is column V in the full data set and the antibiotic names headers would start in W1)); nothing happened. I have the antibiotic names in sheet 2, but I do not see sheet 2 called up in the formula for the column headers. What am I missing???

I am sorry, but I am not understanding this. I do not see anything happen when I press CTRL+F3
I looked up creating and using named ranges so I could hopefully understand this. Following those directions, I highlighted the antibiotic names in sheet 2, went to the Formula Tab to define the name, but I do not see a box for that.
Leave it to a computer program to make you feel stupid...

I had to set the range in Excel 2016 because the web version does not allow it.

Now my O365 file has the column names but no phenotype data.
The "heavy lifting" formula does not seem to be working (correcting it for the location of the comma delimited list).
I am attaching a copy to the file I was using in O365 with the first 20 or so rows of the full data set. I added a amikacin value to the first isolate to see if anything happens.
Do you see anything wrong (I do not have the formula in the full array, only the first column)?

I don't understand why it works for you but doesn't work for me... maybe this is not actually Office365. I am unable to set ranges in this Web version of Excel, but I was able to do that part in Excel 2016. I saved the file with the range set, then opened that file in the web version of O365 to do the rest.

I appreciate your help. I am sorry to be taking so much of your time. I guess I need to figure out why my Office365 is not functioning properly.

I am at home now, but when I launch O365, under FILE--> INFO, one of the options says "Open in desktop app, Use the full functionality of the Excel desktop app" so the problem is likely as AliGW indicated. I just got off the phone with our IT support, and I should have the functionality to install the desktop version of O365 and not just the online version. I will do that tomorrow on the PC, if our predicted snow is not too bad overnight.

Good morning Glenn (and forum).
After some technical issues with Office 365, I was able to run the function/formula successfully on that column of data; as well as a similar column for detected AMR genotypes by changing the the source cell for where the comma separate list was found and the output cells, as well as the named range for the list of genotypes for the column headers. I sincerely appreciate the help you provided me.

Now that it worked, if you have the time, I would love to know what is happening at each step so I might be able to use a similar function in the future for a similar problem (if it is not too hard to explain in text).

Once again, thank you so much for your help. Tonight, I will lift my glass to toast you!

Michele

Good afternoon.
I have come across a problem with the way this handled the data. Many of the gene names they used contain a prime (') or double prime (") and I think this caused a problem because none of the computed cells contain data when they should.
I adapted the formula you provided for the phenotype/AST data to do the genotype data, but the names the program was looking for were a lot simpler for the phenotypes.
In the attached file (a subset of the data), I have put some of the source data of concern in red type in column R and the corresponding cells showing the lack of data highlighted in yellow (cells BR3, CZ11, CZ12, CZ23, FR2, FR6, FR7, FR14).
You can see in columns where there are not any ' or " in the gene name, the program was able to perform as expected and reteurn the word COMPLETE.

Is this fixable? If so, how do I fix the formula?

Thanks in advance for your help!
Michele

Thanks Glenn.
This introduces a different problem for the data set. Like the phenotype data, there can be several possibilities after the equals sign (=)... COMPLETE, PARTIAL, MISTRANSLATION, POINT, HMM, PARTIAL_END_OF_CONTIG (instead of S, I, R etc). So instead of returning Cmplt, I need it to return the text after the =. Is it possible to modify the original formula you gave me with the * ? I tried to put in an * where I thought it would go by comparing the 2 formulas but was not successful in returning only what is after the =, but did return the entire text in the comma separated list between the commas such as aac(6*)-29a=COMPLETE in column BS3.

Original:
=IF(U$1="","",IFERROR(TEXTJOIN(",",,SUBSTITUTE(FILTERXML("<A><B>"&SUBSTITUTE($R2,",","</B><B>")&"</B></A>","//B[contains(., '"&U$1&"="&"')]"),U$1&"=","")),""))

Where I thought the * would go:
=IF(U$1="","",IFERROR(TEXTJOIN(",",,SUBSTITUTE(FILTERXML("<A><B>"&SUBSTITUTE(SUBSTITUTE($R2,"'","*"),",","</B><B>")&"</B></A>","//B[contains(., '"&SUBSTITUTE(U$1,"'","*")&"="&"')]"),U$1&"=","")),""))

This is working... almost.

I added the 2 validation columns in my real data set to look for any additional genotypes absent from the gene list and I got a spill error for many of the rows. Looking at column R, I noticed that there are 2 possible values for a genotype where ever this spill error occurred (aadA1=COMPLETE,aadA1=PARTIAL_END_OF_CONTIG in this case). In addition, the values returned for the gene in question returns a spill error rather than putting both values in the cell (such as COMPLETE,PARTIAL_END_OF_CONTIG) which is how the results were before I realized the problem with the ' .

I copied one of the cells in column R3 from my real data set into your Excel sheet. The corresponding output for this gene would be in cell EK3.

Thank you! This is great! I appreciate the validation (before/after check) with the conditional formatting. I put this into the real data sheet and have found some other genotype names that were missing from the list. The formatting easily shows where the discrepancies are and then I can check the genotype list. I wish my computer had more processing power as the file is so big and any small change and it seems to recalculate everything again ( and I have only done the isolates for one organism so far).