Results 1 to 8 of 8
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Collections of Collections (Excel2000)

    Hi all,
    Attached is a sample file of what I'm trying to accomplish. My data set is a collection of information about hardware cards (serial number, slots, type, model #) and I need to do processing on this data based on several criteria.

    The first pass needs to group this data based on all cards in the same chassis (chassis #). The data is sorted by chassis # so that's easy but I was thinking I need to collect all rows that belong to the same chassis together. Then after I have all the data for the same chassis, I can process each one individually (that's when I apply my business rules).

    Example: (chassis ID, serial #, model #)
    AA, 123,A
    AA, 455,B
    AA, 192, A
    BB, 189, A
    BB, 145, B

    I need to collect all data for chassis AA then all data for chassis BB. The number of cards for a given chassis is not fixed.

    How can I go thru this data and group it for later processing? The rules for how to process the data are complicated so I won't go into that. I can deal with the rules, but I can't figure out how to get an array (or collection as I was using) where element #1 is all the AA rows, and element 2 is all the BB rows,etc. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Can you do collecions of collections? I don't see why not. I created a class that represented the data and it works fine to gather all individual chassis data, but fails when I try to store the individual collection into a larger collection.

    In essense I want to be able to do:
    BigCollection.SmallCollection(2) = the BB chassis rows

    Any other ideas on how to process this data?

    I bow <img src=/S/bow.gif border=0 alt=bow width=15 height=15> to the bigger brains...
    Deb <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Collections of Collections (Excel2000)

    Do you have Access? If so, you could link the Excel data set in Access. You might be able to do everything you want with queries there, without any programming.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collections of Collections (Excel2000)

    Yes, I have Access but have never linked it with Excel. I don't know if my end user (just one person at this time) has Access. Rats! I was hoping some clever brain could come up with an algorithm to parse this data into groups so I can then use my code against those groups (hence my use of collections).

    The data set changes each month so this is something that needs to be done on a reglar basis.

    I am wiling to use Access for this but I've never used it for anything other than playing around so don't know where to start. I can certainly make a table that fits the data and play with queries I guess. The business rules are quite complicated and I can't think of an SQL statement that would work whereas I can deal with the business rules in VBA. These are the 'rules' that are used to determine when a card goes from un-used to used state.

    The whole object here is to compare a set of data from the customer with data from our sales dept. The customer is charged for the cards after they put them into service so these two data sets are compared as the cards are shipped and then later put into service. When the customer actually uses the cards, then they're charged for them. Over time, the output will be 0 when all purchased cards are in service.

    If I try this app in Access, I guess I can post it to The Lounge again seeking help.

    Thnx, Deb <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Collections of Collections (Excel2000)

    Hi Deb,
    Is there a reason you can't simply process the array as it stands? In other words simply process the array with your business rules according to what the value in column 1 of each row is.
    Alternatively you could define a Card datatype, with various properties - serial#, chassis# or whatever) and then go through your array of data adding items to a Cards collection. When you're processing with your business rules you would simply check the objCard.Chassis to see what # it was.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collections of Collections (Excel2000)

    Great minds!! Yes actually that's what I've already done. I created a class that is just a data type which mimics the fields in each row (chass ID, serial #, slot #, etc) (I'm used to C++ so I tend to use classes).

    I then go through the rows and store all data of the same chassis ID in the same collection. The collection has 'n' elements of my data type (CardData I call it), one for each row that has same Chassis ID.

    The business rules I've avoid explaining so far but it's not as simple as just looking in one row. After I recognize a row as one I care about, then I need to find a corresponding row. So I need to look either forward or backward from where I currently am in the data set. That's why I first stored the rows in a collection so I can move around that collection on the fly searching for this other row.

    For all rows that match my business rules, I end up copying them to a new worksheet for more processing (this is just the first intermediate step). I have a flag in my class CardData called doCopy = true/false that I set so that later I know if that's a row to be copied.

    Yes, it's complicated and I'm struggling with how to set it up. It's one of those things that I can look at data with my own eyeballs and see which rows need to be copied, but try writing code to match what your brain has easily figured out is not always easy.

    Here's a condensed version of the business rules (assumes I have a set of rows already stored in my collection):
    1. if CardType = 'service' then go thru the other rows of data and look for CardType = 'backup'. If this backup card is in the correct slot, then copy all the rows where CardType = service (there can be many of these). Depending on the card model, one backup card can handle 1+ service cards.

    There are two different business rules depending on the card model but I just showed you for one model.

    You see what I meant that I have to search rows before and after the 'found' row (the row where CardType = 'service').

    I'm sure this is much more complicated when I describe it in words (actually it's tricky even when I see the real data). Usually if I can write out the rules in psuedo code I can translate to real code,
    I but this one is tricky.

    Ah, well I gotta keep thinkin' .... I'll ask a co-worker to look at it and see if he can see something I can't.
    Deb <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Collections of Collections (Excel2000)

    Why aren't you doing the testing in your class? There's nothing to stop you from adding methods like "Match" to your class and hiding the ugly details of applying the rules in there.
    Charlotte

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collections of Collections (Excel2000)

    Yes, that's what I do, that's not the problem though. I just wanted to store all the data (each row) in one collection. But each element of this collection is itself a collection and I couldn't get the syntax to work right.

    Since I need to look forward and backward through the data to apply the business rules, I wanted to store it like this so it's easier to loop through.

    Actually I ended up using formulas (in the RC notation) to select the rows I need to worry about. Then I wrote code that copied those rows to another sheet for further processing. I was trying too do everything with code and that was the tricky part.

    Thnx everyone!
    Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Collections of Collections (Excel2000)

    Does this help?

    <pre>Option Explicit

    Private Sub PackRat()
    Dim colArray(3, 2) As Collection
    Dim lngColumn As Long
    Dim lngIndex As Long
    Dim lngRow As Long

    For lngRow = 0 To 3
    For lngColumn = 0 To 2
    Set colArray(lngRow, lngColumn) = New Collection
    With colArray(lngRow, lngColumn)
    .Add "First"
    .Add "Scond"
    Debug.Print "Collection(" & Format$(lngRow) & "," & Format$(lngColumn) & _
    ") has " & .Count & " members. The members are:"
    For lngIndex = 1 To .Count
    Debug.Print vbTab & .Item(lngIndex)
    Next lngIndex
    End With
    Next lngColumn
    Next lngRow
    End Sub
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •