Combining data from multiple rows into one row: with complications
So I have this massive dataset of basically drug effectiveness data. The data is confidential, but it looks kind of like my BEFORE image, except that I have a list of about 1000 items, each with one or multiple lot numbers. Each lot number is tested one or multiple times (exp1, exp2, etc), in duplicate each time (exp1.2, exp1.2).
I need to combine all the data for each item name onto one line (like the AFTER image), forgetting about the lot numbers, but it's essential to keep the experimental duplicates together. I've been doing it manually (not too often), but it's incredibly tedious and I'd much rather automate it with a formula if possible. I have made sure there are enough columns for the data to spill into, but the total number of experiments does sometimes go over 10.
I'm usually good with complex formulas (I play a bit of competitive Excel), but I'm a little lost on where to start with this puzzle, so I figured I'd ask here while I also start tinkering with it.
[link] [comments]
Want to read more?
Check out the full article on the original site