Hi everybody,
I have a very large list, about 500,000 rows with two columns, that I would like to transform into a heat map. Column A contains numbers from 1-500,000: not continuous (i.e. with gaps), unique, and ordered from small to large. Column B contains values associated with those numbers - also numbers, ranging from about 1550 to about 1750. What I would like to do is create a heat map to show where higher values in column B are concentrated in the numbered series.
I would like to create a pivot table where:
- Column A (row header) brings together the numbers in batches of 10,000 (1-9,999, 10,000-19,999, ...)
- Row 1 (header) draws from column B to create batches of 10 (1550-1559, 1560-1569, ...)
- The data from column B are counted and put in the correct column
- A heat map shows where higher concentrations can be found
I'm including an example below, I hope I'm being clear - and I hope it's possible to do this in Excel...
Many thanks in advance!
Steven
SOURCE
1 1602
2 1611
3 1605
4 1630
5 1632
6 1621
8 1630
9 1632
10 1636
PIVOT
1600-1609 1610-1619 1629-1629 1630-1639
1-4 2 1 0 1
5-10 0 0 2 2
Bookmarks