Group by row value and sum a large number of columns

[KOrt]

Hi - I have a large dataset that contains duplicate values (lipids) and several hundred columns. I would like to group by each lipid and then sum the area columns (each one is a unique sample ID). The challenge is that there are about 600 separate columns. Any ideas on the best way to approach this? I'm attaching a shortened Excel file to show what the data looks like.

[atcodedog05]

Hi @KOrt 

Can you provide the expected output?

[KOrt]

Does this help clarify?

[atcodedog05 · Accepted solution]

Hi @KOrt 

Here is how you can do it.

Workflow:

1. Using transpose i am converting required columns to rows.

2. Using crosstab while converting it to table i am summing up.

Hope this helps : )

[KOrt · Accepted solution]

Thanks - I'll give that a shot!

[atcodedog05]

Happy to help : ) @KOrt 

Cheers and have a nice day!