Numbers pivot table combines all transactions with the same date!

Question

Numbers pivot table combines all transactions with the same date!

Hi all.

The application is a Numbers table that keeps track of stock market transactions: Buys and Sells. One line per transaction.

Then, I want to use a pivot table to analyze and summarize the result of each position (a position consists of one (or more, but usually one) Buy and one or several Sells).

However, when I select Ticker and Date as "Rows", Numbers only lists each date once, not once per actual trade.

In the example table, NVDA had two sells on 2024-01-05, but these are summarized and listed only once. This is not the result I expected nor wanted. I would like to see each "trade" listed in the pivot table, even if there are several with the same date for the same ticker...

What am I doing wrong??? Thanks!

Mac mini (M1, 2020)

Posted on Jan 18, 2024 6:57 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,550 points

Jan 19, 2024 12:45 AM in response to skdjfhskfjshf

One solution might be to add a column for a "string date" to your data table and drag that instead into the Pivot Table, something like this:

The formula in D2, filled down:

=A2&REPT(" ",ROW())

Use ; instead of , in the formula if your region use , as the decimal separator.

The idea is to make each date slightly different by appending something invisible to the end that changes with each row.

If you don't have formulas in your data table that will be disturbed by sorting you might also consider using the Categories feature:

SG

Reply