Is there a way to have pivot table always auto sum two different items which are name differently but actually are the same.
Sorry in advance for the confusion in the topic.
If i have a product in shop one call ABC and another shop 2 call ABCD.
But they are actually the same product just different name due to it from different backend sources.
If on the same day, Shop 1 sold 4 Qty of ABC and Shop 2 sold 6 QTY of ABCD.
I understand i will need power query to able to combine the data of 2 excel sheet but
What function should I use in a pivot table to show total auto Sum ABC and ABCD on that day.
Would be very helpful if you uploaded a sample data file (no pictures please) showing your existing data and a mocked up results of what you expect.
If you want Excel to sum ABC and ABCD together, then you could use Power Query to replace ABCD with ABC so Excel knows they are the same. Alternatively, you could group items ABC and ABCD together. Select these items in the PivotTable (hold CTRL to select), then right-click > Group.
