Required with 2D data structure and Object for A specific Programming Language

[twinbaby]

HI there,

Currently I have a sample data structure of the following type.
My idea is to have something as versatile as Pivotable.

Rating Options
High Yes
Low No
Medium Maybe
Low No
High Maybe
Medium Yes
Low Yes
High Yes
High Yes
Low No
High No
High Maybe

Pivotable Option 1 (Options, Value) (Count by Options):

Row Labels Count of Options
High 6
Maybe 2
No 1
Yes 3
Low 4
No 3
Yes 1
Medium 2
Maybe 1
Yes 1
Grand Total 12

Pivotable Option 2 (Value, Options, ) (Count by Value):

Row Labels Count of Options
Maybe 3
High 2
Medium 1
No 4
High 1
Low 3
Yes 5
High 3
Low 1
Medium 1
Grand Total 12

Option 1:
I try to put both column into a dicitonary, Try getting all the key individually, no problem.Try getting all the pair individually, no problem. I look up the api, found out Key can only set the object. So I pause here.

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object

Item	Sets or returns the value of an item in a Dictionary object.
[B]Key	[/B]Sets a new key value for an existing key value in a Dictionary object.

Option 2:
Create 2 list. Here is what I did. A lot of copy and pasting involved. Can't insert here. Otherwise 403

Option 3:
Create H,M,L object with a collection of Y,N,maybe object. When I create a list H,M,L object add in Y,N,maybe object ok ,but enumerate through Y,N,maybe object failed.


Required assistance specifically for VBA, if not data structure suggestion will be fine.

 

[jackoats.sg]

Must it be done in VBA? Can you accept any other alternatives? I think I can use R / Python to do it.

 

[twinbaby]

Must it be done in VBA? Can you accept any other alternatives? I think I can use R / Python to do it.

if not data structure or psuedo code or easy to understand suggestion will be fine. I can convert to VBA.
Edit Reply

 

[jackoats.sg]

Don't think in terms of Pivot table first.

Can you put up a sample of the data in Excel format? It doesn't have to be real data. It is easier to visualize that way.

 

[twinbaby]

Don't think in terms of Pivot table first.

Can you put up a sample of the data in Excel format? It doesn't have to be real data. It is easier to visualize that way.

Here is the sample data, actually it is on my first quote.

Rating,Options
High,Yes
Low,No
Medium,Maybe
Low,No
High,Maybe
Medium,Yes
Low,Yes
High,Yes
High,Yes
Low,No
High,No
High,Maybe

 

[davidktw]

Pivot table is derived information. It doesn’t has to be persistent. Your data can be kept as a simple 2D array of information. The 2 pivots of information are basically counting of either column 1 or column 2 data and present them as required.

If your data are not changed all the time, you can perform caching for the derived pivot tables information. Once there is any changes to the main data, you can then update the pivot tables when they are accessed. The idea is have something called a dirty bit. The dirty bit is set to true each time the main data table is written. You only update the pivot tables when there is an access and the dirty bit is set. This way you do not need to update the pivot tables each time the main table is changed.

You can encapsulate all these data into a class, and offer the access of either the main table, the pivot tables by which column as criterion as methods functionality.

In any case, your pivot by option or by value seems weird to me, why are some rows repeated with different values? What are you aggregating?

 

[jackoats.sg]

Confusing

If you 'see' your data like this, you'd still get your pivot table:

Sample data
===
High Yes
High Yes
High Yes
High No
High Maybe
High Maybe


Pivot table (aggregate: count)
===
High Yes 3
High No 1
High Maybe 2
High Don't_care 6

 

[twinbaby]

Confusing

If you 'see' your data like this, you'd still get your pivot table:

Sample data
===
High Yes
High Yes
High Yes
High No
High Maybe
High Maybe


Pivot table (aggregate: count)
===
High Yes 3
High No 1
High Maybe 2
High Don't_care 6

Yes that is what I am looking for
High 6-Total of Maybe,No, Yes(sry didn't do the formatting)

Maybe 2
No 1
Yes 3

The thing is I dont want to do via pivot table, I want to dynamically populate via cell individually.

 

[peterchan75]

@ts,
Are you able to put your data into rows(records) and columns(fields) in an Excel Spreadsheet? Each row is unique i.e. some fields or field must be different. If you can, then you can present your data with either pivot table or Excel macro.