I want a value to appear differently in new cell. (pic inside)

Untitled.jpg

From sheet1. There are 2 sets of data with 2 types I want them to appear in sheet2 like in the picture.
In sheet2,column A shows Types and column B shows Sum of each type.
And most important is to begin a new row when there are more data types in sheet1, like Data 1 c and Data 2 d, so it adds more rows for Type c and Type d.

Can it be done and can you teach me please? Thank you

Edit: The type of data indicates by last letter in Sheet1 column A(a,b,c,d..). Don't know if this helps.
Edit2: Typo

PS. I think I did post in the wrong forum before so I didn't get any reply there.

This is simple, using Power Query. Format your data in Sheet1 as a Table with Column Headers "Data" and "Value", then use:

Please Login or Register  to view this content.

Not sure where to put Power Query code? See https://excel.solutions/2017/11/powe...te-code-video/

Your profile states you are using Excel 2010 - Power Query is a free addin - see https://www.excelcampus.com/install-power-query/

Originally Posted by Olly

This is simple, using Power Query. Format your data in Sheet1 as a Table with Column Headers "Data" and "Value", then use:

Please Login or Register  to view this content.

Not sure where to put Power Query code? See https://excel.solutions/2017/11/powe...te-code-video/

Your profile states you are using Excel 2010 - Power Query is a free addin - see https://www.excelcampus.com/install-power-query/

Problem found. I have to make 2 tables for "Data"(Table1) and "Value"(Table2) because they're seperated columns so the current code doesn't work. Seems like it couldn't find "Value" in the table, I guess.
I've been trying to make 2 Tables in to 1 and add more source for table in Power Query, but it turned out that I was dumber than I thought.

Edit1.2: May I know if a prefix could be added in "Data Type" column, Sheet2? Like 'product type a'.
Edit2.2: Some types of "Data" actually contain a number in suffix and a letter in the name in stead of number like this "Data 1 2a" , Data 2 3b, Data A 2a , Data B 3b". Is this a part of the problem?

I am not sure what you are asking me? If you're still stuck, then please attach your workbook, so I can see what you're trying.