Applying fn to entire rows if a column fills a conditional, and displaying sequentially?

[mischifous]

Hi guys,

I would like to thank whoever gives this a go ; I know that you guys do this just because you are nice people. So thank you.

I would like to be able to do the following:

The above black text is how my data is structured. This data constantly sees rows inserted and deleted, and they are not in any particular order. I would like to be able to somehow, aggregate rows that have the same "Underlier" (or column B). In the above, groups of rows 3 and 7, and rows 4 and 6, are combined to form single row for AYX and AMZN, respectively, as shown in the blue text (rows 13 and 14). That is request #1 , and is the more important one.

Secondarily, it would be great if whichever method this is best accomplished by, would also make sure that whatever output format, the rows are displayed consecutively, as showed by the blue text. =

Sounds like it would be easy but I haven't had any success. I'll need to use the INDEX() function, right? Any help appreciated, tyvm

 

[sandy666]

Try PowerQuery, Group by first and second column and Sum other two


[TABLE="class: head"]
<tbody>[TR]
[TD="bgcolor: #5B9BD5"] Symbol
[/TD]
[TD="bgcolor: #5B9BD5"]Underlier
[/TD]
[TD="bgcolor: #5B9BD5"]Cost
[/TD]
[TD="bgcolor: #5B9BD5"]Val
[/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"]Symbol
[/TD]
[TD="bgcolor: #70AD47"]Underlier
[/TD]
[TD="bgcolor: #70AD47"]Cost
[/TD]
[TD="bgcolor: #70AD47"]Val
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]CRSP us equity[/TD]
[TD="bgcolor: #DDEBF7"]CRSP[/TD]
[TD="bgcolor: #DDEBF7"]

1768​

[/TD]
[TD="bgcolor: #DDEBF7"]

2209​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]CRSP us equity[/TD]
[TD="bgcolor: #E2EFDA"]CRSP[/TD]
[TD="bgcolor: #E2EFDA"]

1768​

[/TD]
[TD="bgcolor: #E2EFDA"]

2209​

[/TD]
[/TR]
[TR]
[TD]AYX us equity[/TD]
[TD]AYX[/TD]
[TD]

4863​

[/TD]
[TD]

6754​

[/TD]
[TD][/TD]
[TD]AYX us equity[/TD]
[TD]AYX[/TD]
[TD]

12342​

[/TD]
[TD]

16696​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]AMZN us equity[/TD]
[TD="bgcolor: #DDEBF7"]AMZN[/TD]
[TD="bgcolor: #DDEBF7"]

9129​

[/TD]
[TD="bgcolor: #DDEBF7"]

9086​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]AMZN us equity[/TD]
[TD="bgcolor: #E2EFDA"]AMZN[/TD]
[TD="bgcolor: #E2EFDA"]

14399​

[/TD]
[TD="bgcolor: #E2EFDA"]

18172​

[/TD]
[/TR]
[TR]
[TD]NOW us equity[/TD]
[TD]NOW[/TD]
[TD]

1512​

[/TD]
[TD]

3127​

[/TD]
[TD][/TD]
[TD]NOW us equity[/TD]
[TD]NOW[/TD]
[TD]

1512​

[/TD]
[TD]

3127​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]AMZN us equity[/TD]
[TD="bgcolor: #DDEBF7"]AMZN[/TD]
[TD="bgcolor: #DDEBF7"]

5270​

[/TD]
[TD="bgcolor: #DDEBF7"]

9086​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]GE us equity[/TD]
[TD="bgcolor: #E2EFDA"]GE[/TD]
[TD="bgcolor: #E2EFDA"]

2360​

[/TD]
[TD="bgcolor: #E2EFDA"]

1045​

[/TD]
[/TR]
[TR]
[TD]AYX us equity[/TD]
[TD]AYX[/TD]
[TD]

2359​

[/TD]
[TD]

3188​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]GE us equity[/TD]
[TD="bgcolor: #DDEBF7"]GE[/TD]
[TD="bgcolor: #DDEBF7"]

2360​

[/TD]
[TD="bgcolor: #DDEBF7"]

1045​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AYX us equity[/TD]
[TD]AYX[/TD]
[TD]

5120​

[/TD]
[TD]

6754​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

I forgot to add M code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Symbol", type text}, {"Underlier", type text}, {"Cost", Int64.Type}, {"Val", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Symbol", "Underlier"}, {{"Cost", each List.Sum([Cost]), type number}, {"Val", each List.Sum([Val]), type number}})
in
    #"Grouped Rows"[/SIZE]

 

[Peter_SSs]

A formula approach for you to consider too.
F2 and G2 copied down as far as the column A data.
H2 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Symbol	Underlier	Cost	Val		Symbol	Underlier	Cost	Val
2	CRSP us equity	CRSP	1768	2209		CRSP us equity	CRSP	1768	2209
3	AYX us equity	AYX	4863	6754		AYX us equity	AYX	12342	16696
4	AMZN us equity	AMZN	9129	9086		AMZN us equity	AMZN	14399	18172
5	NOW us equity	NOW	1512	3127		NOW us equity	NOW	1512	3127
6	AMZN us equity	AMZN	5270	9086		GE us equity	GE	2360	1045
7	AYX us equity	AYX	2359	3188
8	GE us equity	GE	2360	1045
9	AYX us equity	AYX	5120	6754
10
Combine data