I need help on creating a summary table in Excel using a complex data that I have

Question

I need help on creating a quick summary table that can quickly show any of the following case:

1. Shows the collective number of units sold per model per location, no need to show which store.
2. Shows which stores are offering the same model per location.

Below is just a sample of the data I'm working on but the file I'm actually working on has more than 10,000 entries.

I'd really appreciate if somebody could help me on this.

Store	Goods Type	Model	Location 1	Location 2	Location 3	Location 4
ABC	Laptop	Lenovo	3	85		34
ABC	Laptop	Lenovo 2	6	12	31	2
DEF	Laptop	Lenovo 2	4	57	42	12
ABC	Laptop	Asus			21	7
DEF	Speakers	JBL		1		1
DEF	Speakers	Bose	3
DEF	Smart Watch	Garmin			2
ABC	Smart Watch	Garmin	12		2
GHI	Printer	HP		2		3
GHI	Printer	Canon			4
ABC	Scanner	Canon	3		2	4
GHI	Laptop	Asus		4	10	8
DEF	Laptop	Asus	19			13

Answer 1

Re: sales information

My interpretation...

Assumption is that no sales means that model is not carried.

'---
Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

Answer 2

Re: sales information

My interpretation...

Assumption is that no sales means that model is not carried.

'---
Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

You're a genius! Thank you so much!

Answer 3

Paul S.M.

You are welcome.

Answer 4

Hi,

Your data is not well structured. Using the Query Editor, use the "Unpivot Other Columns" feature to transform this data into a 5 column structure. You should then be able to answer your questions with a Pivot Table.