vba - What is the best structure to store grouped rows of data in VB -

i have data structure:

category  product  group  requiredamount stockamount 

sample data

a     1   10  5   b   1   10  10   c   2   20  10   d   3   30  40 b   e   1   10  15 b   f   2   20  20 

i want store in collection of sort, while being able to:

• access category , product.
• keep items order entered, not sorted alphabetically.
• change amounts.

it handle around 100 items.

i used dictionary categories, holding collection of 1-dimensional arrays, product key.

the main problem changing amounts, changing items in collection requires adding , removing them, changes items order.

any thoughts?

this creates 2 field recordset (you can save disk). make yours' 6 fields , include line number field can use sort original order. can sort , filter how want.

set rs = createobject("adodb.recordset") rs     .fields.append "sortkey", 4      .fields.append "txt", 201, 5000      .open     until inp.atendofstream         lne = inp.readline         .addnew         .fields("sortkey").value = csng(sortkey)         .fields("txt").value = lne         .update     loop               .sort = "sortkey asc"               .filter = "sortkey > 5"      while not .eof         outp.writeline .fields("txt").value         .movenext     loop end