Allignement of Stars and Data in rows

[Maggernus]

Hi all,

I'm new to excel and have a problem with a rather large data set I can't really solve. My data is divided into many groups. I want to shift all values to a single row. I've tried to illustrate i underneath. The top table is what I have and the bottom table is what I want.

I hope somebody has the time and knowhow to help me with this problem. Thanks a lot in advance!!!




1Tap 2Tap 3Tap 4Tap 5Tap 6Tap 7Tap 8Tap 9Tap 10Tap 11Tap 12Tap 13Tap 14Tap 15Tap 16Tap 17Tap 18Tap
Pentanone 43 86
Heptane 45
Hexane 50 3 12 89
Pentane 78 2
Sulfone 1 5
Cyclopentane 93 95 52 1 5 7
1-Heptene 7


1Tap 2Tap 3Tap 4Tap 5Tap 6Tap 7Tap 8Tap 9Tap 10Tap 11Tap 12Tap 13Tap 14Tap 15Tap 16Tap 17Tap 18Tap
Pentanone
Heptane
Hexane
Pentane
Sulfone
Cyclopentane 43 86 50 3 1 5 45 93 7 95 52 78 12 89 1 5 7 2
1-Heptene

[Special-K]

Difficult to interpet the output like that

Attach a sample spreadsheet, remove any sensitive data.

[Maggernus]

Sure. Hope it works. Thanks

The top table is what I have and the bottom table is what I want.

[ChemistB]

Assuming there's only 1 number in each column above, you could simply (in B17 copied right) use

=SUM(B$2:B$8)

Or if you want to get fancy

=INDEX(B$2:B$8, MATCH(9^99, B$2:B$8)) (this returns the last number it finds in that column)
Not sure why you are brining all the numbers onto the cyclpentane line?