Richard Riffle
New Member
Hello everyone. I've been a user of Chandoo.org for a while now but this is my first post. I understand the great service offered by the members of this organization and I'm hoping I'll benefit by your collective knowledge. Here's my problem...
I have a spreadsheet that has 40,000+ rows and 167 columns containing 6,500,000+ data values. Each row of data is a record for a specific time interval with the first column of data containing a counter for each time interval.
Of the 166 other columns of data:
I used the approach outlined in Excel to the Next Level by Mastering Multiple Occurrences to create a column containing every counter value (the time interval from the first column) corresponding with each possible value of a Boolean or enumerated data column. Shown in the attached spreadsheet.
Rather than every counter value for each Boolean or enumerated data column – all 40,000+ of them per column – I’d like to have just the counter values for the changes in the data. Shown in the attached spreadsheet.
I'd like to do something similar with the real data using a change in value greater than a certain percentage of full range of the data. Shown in the attached spreadsheet.
Conceptually for the Boolean data, the counter information for a row should only be extracted if the exclusive OR of the data value in the same row and the data value of the row preceding it is TRUE. I suspect the Boolean case will be extensible to the enumerated and real data cases...
Ideally, there would be a macro that would process the data in the source spreadsheet file and place the data in a target spreadsheet file, either in columns in a single worksheet or in separate worksheets for each data column.
Any assistance would be greatly appreciated.
I have a spreadsheet that has 40,000+ rows and 167 columns containing 6,500,000+ data values. Each row of data is a record for a specific time interval with the first column of data containing a counter for each time interval.
Of the 166 other columns of data:
- 81 columns contain Boolean data (represented by 0 = False, 1 = True)
- 5 columns contain integer values representing enumerated data (represented by 0 = EnumeratedValue1, …, N = EnumeratedValueN and the maximum value of N is11)
- 78 columns containing real numbers
- 2 columns containing integer values)
I used the approach outlined in Excel to the Next Level by Mastering Multiple Occurrences to create a column containing every counter value (the time interval from the first column) corresponding with each possible value of a Boolean or enumerated data column. Shown in the attached spreadsheet.
Rather than every counter value for each Boolean or enumerated data column – all 40,000+ of them per column – I’d like to have just the counter values for the changes in the data. Shown in the attached spreadsheet.
I'd like to do something similar with the real data using a change in value greater than a certain percentage of full range of the data. Shown in the attached spreadsheet.
Conceptually for the Boolean data, the counter information for a row should only be extracted if the exclusive OR of the data value in the same row and the data value of the row preceding it is TRUE. I suspect the Boolean case will be extensible to the enumerated and real data cases...
Ideally, there would be a macro that would process the data in the source spreadsheet file and place the data in a target spreadsheet file, either in columns in a single worksheet or in separate worksheets for each data column.
Any assistance would be greatly appreciated.
