I have a large Excel file that contains some data in it horizontally, that I need changed to vertically. Not as simple as I just said, so I will try to explain. The original file has a unique p21lot number in column A, then a letter symbol that represents a chemical in column B, then a number in column C which is the actual number of the chemical in column B. Column D would have the next chemical abbreviation, Column E would have the actual number for the chemical in column D, etc., etc. Some p21 lot numbers have a couple of chemicals, while others may have 16. In my sample, you may notice that it appears that the chemicals are always in the same location.....C is the first one, MN is the second, etc. but this is NOT the case. So using a pivot table was not a solution for me. What I need to do with this information is have column A display the p21 lot number, column b show the chemical name and column c show the actual result. So there would be multiple rows with the same p21 lot number based on the number of chemicals.
I'd like to know if someone knows of an easy way to do this. I have 5000 lot numbers that need this done and doing it by hand is not only going to be time consuming but I fear I would make errors.
I have attached a sample. One tab has current data and the other tab shows what I need it to look like.