Array of zeroes and ones = automatically a binarymap

[Jaymond Flurrie]

Like I've brought up couple of times here already (thanks for everyone helping me!), I have a huge array. That array has about 100 000 x 80 elements and it is denoted (excluding the header-column and header-row) by zeroes and ones ie. it's a binary map but if I have a 8 million cell range (that array comes from querying an external workbook), is that a binarymap or are those strings or what are those? If that is not a binarymap already (I would guess it's not), what's the smartest way to save as much memory as possible here? Can I do some SQL query which says that "yeah, looks like strings there, but just treat that as a binarymap"? Any other smart ways to improve performance?

 

[Ruddles]

By array, do you mean an array in VBA or a range of worksheet cells?

If you mean worksheet cells, then those zeroes and ones are stored as numbers rather than single bits. To save worksheet space you could perhaps turn each set of eight or sixteen cells into a single 8- or 16-bit number or even turn each row of the array into a character string with 80 'bits' in it - assuming that the data would still be useable in that format.

Do a few experiments with a small sample of your data to work out the effect that would have on file size and then ask yourself what additional processing you'd need to do if your data was held in that format. You might find it easier to keep the data in its current formsat and just use the extra memory.

What's the concern? Is it RAM or disk space? In what way are you trying to 'save' it?

 

[Jaymond Flurrie]

By array, do you mean an array in VBA or a range of worksheet cells?

If you mean worksheet cells, then those zeroes and ones are stored as numbers rather than single bits. To save worksheet space you could perhaps turn each set of eight or sixteen cells into a single 8- or 16-bit number or even turn each row of the array into a character string with 80 'bits' in it - assuming that the data would still be useable in that format.

Do a few experiments with a small sample of your data to work out the effect that would have on file size and then ask yourself what additional processing you'd need to do if your data was held in that format. You might find it easier to keep the data in its current formsat and just use the extra memory.

What's the concern? Is it RAM or disk space? In what way are you trying to 'save' it?

Mostly it is about a huge loading time and someone of group SQL/AdoDB/VBA/Excel not liking about that much data - runs out of memory. In the near future the DB will be transferred into Access and most likely to SQL Server after that. But whether that will ease the load, that's a different thing.

 

[shg]

Further to Ruddles suggestion,

=SUMPRODUCT(A1:AN1, 2^(39 - COLUMN(A1:AN1) + COLUMN(A1)))

converts the 40 binary values in the range to a single number in the range 0 to 1,099,511,627,775

=SUMPRODUCT(AO1:CB1, 2^(39 - COLUMN(AO1:CB1) + COLUMN(AO1)))

... does the same for the next 40 columns. That's a 40X compression.

 

[Jaymond Flurrie]

Further to Ruddles suggestion,

=SUMPRODUCT(A1:AN1,2^(39 - COLUMN(A1:AN1) + COLUMN(A1)))

converts the 40 binary values in the ranges to a single number in the range 0 to 1,099,511,627,775

=SUMPRODUCT(AO1:CB1,2^(39 - COLUMN(AO1:CB1) + COLUMN(AO1)))

... does the same for the next 40 columns. That's a 40X compression.

That's pretty darn effective, thanks!