Create a summary list

[Av8tordude]

Can anyone help me with a VBA code that will take this list and create a summary list in column W-Y. Each item should have a subtotal like in the example in the next table. The Ticker and Name are in column A & B and the Amounts are in column P. The output should be place in in C.

Greatly appreciate i anyone could offer your assistance. Thank you kindly.


[TABLE="width: 409"]
<tbody>[TR]
[TD]Ticker[/TD]
[TD] Name[/TD]
[TD]Amount[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 409"]
<tbody>[TR]
[TD]BA[/TD]
[TD]The Boeing Company[/TD]
[TD] $(617.90)[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD] $(118.67)[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD] $ 131.34[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD]$(238.07)[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]The Boeing Company[/TD]
[TD] $(374.06)[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]The Boeing Company[/TD]
[TD] $(101.09)[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]The Boeing Company[/TD]
[TD] $ 36.92[/TD]
[/TR]
[TR]
[TD]SPY[/TD]
[TD]SPDR S&P 500 ETF[/TD]
[TD] $ 304.10[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD] $(538.29)[/TD]
[/TR]
[TR]
[TD]SPY[/TD]
[TD]SPDR S&P 500 ETF[/TD]
[TD] $ 274.10[/TD]
[/TR]
[TR]
[TD]SQ[/TD]
[TD]Square[/TD]
[TD] $ 466.71[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD] $ 87.77[/TD]
[/TR]
[TR]
[TD]IBM[/TD]
[TD]International Business Machines Corporation[/TD]
[TD] $ 262.10[/TD]
[/TR]
[TR]
[TD]NVDA[/TD]
[TD]NVIDIA Corporation[/TD]
[TD] $ 708.93[/TD]
[/TR]
[TR]
[TD]SPY[/TD]
[TD]SPDR S&P 500 ETF[/TD]
[TD] $ 286.69[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]The Boeing Company[/TD]
[TD] $ 933.90[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 409"]
<tbody>[TR]
[TD]icker[/TD]
[TD]Name[/TD]
[TD]Total[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 409"]
<tbody>[TR]
[TD]AAPL[/TD]
[TD]Apple Inc.[/TD]
[TD]$($657.92)[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]The Boeing Company[/TD]
[TD]$(122.23)[/TD]
[/TR]
[TR]
[TD]IBM[/TD]
[TD]International Business Machines Corporation[/TD]
[TD]$262.10[/TD]
[/TR]
[TR]
[TD]NVDA[/TD]
[TD]NVIDIA Corporation[/TD]
[TD]$708.93[/TD]
[/TR]
[TR]
[TD]SPY[/TD]
[TD]SPDR S&P 500 ETF[/TD]
[TD]$(864.89)[/TD]
[/TR]
[TR]
[TD]SQ[/TD]
[TD]Square[/TD]
[TD]$466.71[/TD]
[/TR]
</tbody>[/TABLE]

 

[Av8tordude]

Excellent...Thank you Peter!...Cheers

 

[Peter_SSs]

No problem. Glad to help.

 

[Av8tordude]

How can I change this code to use COUNTIF. I want to count the number of instances of each value and put the results in column 22 ("V")

[COLOR=#333333]Columns(3).Formula = "=SUMIF(" & rData.Columns(1).Address & ",W1," & rData.Columns(3).Address & ")"[/COLOR]

 

[Peter_SSs]

Try

"=COUNTIF(" & rData.Columns(1).Address & ",W1)"

 

[Av8tordude]

Thank you Peter. I'm not at my computer now however will this be inserted in column V?

 

[Peter_SSs]

Thank you Peter. I'm not at my computer now however will this be inserted in column V?

I don't know because I'm not sure where everything else is and you just quoted 1 line of code so I just altered the formula part as best I could guess.