abcd,10
abcd,11
efgh,7
ijkl,8
ijkl,14

Using the above comma delimited file as an example, how can I add up
the quantities of each item.
So if field 1 is an item and field 2 is a quantity of the item, I need
to produce the following output:
abcd,21
efgh,7
ijkl,22

Show quote Hide quote Create two dynamic arrays, one of type String and one of type Long. Then
read the file line-by-line. Get the item name and loop through the array of
strings. If you don't find the item name, increase the size of both arrays,
add the item name as the next member of the string array and the quantity as
the next member of the numeric array. If you DO find the item name, use the
same array index and add the quantity to the existing value in the numeric
array. Repeat until you run out of data.

That's the pseudo-code. The actual code is left up to you as a learning
experience. If you get stuck, post what you have tried and we'll go from
there.

Hi guys, I've come up with the following code which seems to work, at
least with my limited example I gave:

Private Sub Command1_Click()
Open filIn For Input As #1
Open filOut For Output As #2
100 While Not EOF(1)
  Input #1, fields(0), fields(1)
  If rowCnt = 0 Then
   stkItem = fields(0)
   qty = fields(1)
  End If
  If rowCnt > 0 Then
   If fields(0) = stkItem Then
    qty = qty + fields(1)
    GoTo 100
   Else
    Write #2, stkItem, qty
    stkItem = fields(0)
    qty = fields(1)
    GoTo 100
   End If
  End If
  rowCnt = rowCnt + 1
Wend
Write #2, stkItem, qty
Close #2
Close #1
MsgBox ("Done")
End Sub

Would either of you care to offer some "gotchas" to watch for?
I appreciate your previous posts offering help, thanks very much.
Jeff, I understand the concepts behind your pseudocode but I really
struggled with turning
it into code (I'm a noob, sorry) but I want to learn.

Using hard coded numbers for your file handles is an invitation for
trouble.  FreeFile is designed to avoid contention with already used
file handles.  For example, clicking on that button twice, rapidly enough
to call the second event before the first event is finished could be
a problem....   See FreeFile in VB help.

While the code you posted would work for the example in your
original post, it would not work if the same keys were spread out
in the file:

abcd,10
ijkl,14
abcd,11
efgh,7
ijkl,8


Try an example file like that and see what happens....

LFS

Show quote Hide quote I realize my code is very limited in scope and if anything at all
changes it'll blow up.  Fortunately, I know the input file will always
be sorted so that all the abcd's will be together, all the ijkl's will
be together, etc... because I'm the one producing the input file. What
this is about, and I didn't elaborate on it in my first post because I
didn't want to waste people's time, is we've got to export a list of
materials from an estimating program and import it into an inventory
control program, with the idea of getting control of the inventory
(knowing what's onhand, what needs to be ordered, etc...).  This
particular step is to consolidate the materials list coming out of the
estimating program before we import it into the inventory control, so
that purchase orders for instance will only have one line per stock
item rather than potentially dozens of lines for one stock item.
I see your point though that I really should expand and/or change the
code so that it can handle the data if it's not pre-sorted.  Can I
assume that, in that case, it will handle the data properly whether
it's sorted or not?
As I said in my last post, I understand the concept behind Jeff's idea
about two dynamic arrays but what I'm stuck on at the moment using
that approach is how to expand the array on the fly while keeping the
data that's in it.
As for your suggestion Larry about creating classes and using
collection objects, I honestly don't have a clue.  I'll gladly take
any pointers from you guys about how to proceed though, and thanks for
your help thus far.

Redim Preserve

Mike

It would be better to go with the method you can understand so
that adding new code, or making needed changes will be easier.

But for an example, start a new project and add a Class module
to the project, then paste the code below into their respective
modules.  Once you see it working you may find it easier to
use and comprehend....

HTH
LFS

[ Class1 code ]
Public Key As String
Public Value As Single

Public Sub Assign(Key As String, ByVal Value As Single)
  Me.Key = Key
  Me.Value = Value
End Sub


[ Form1 code ]
Option Explicit
Private List As Collection

Private Sub Form_Load()
Dim item

  Set List = New Collection

  'Fake data
  AddItem "abcd", 10
  AddItem "ijkl", 14
  AddItem "abcd", 11
  AddItem "efgh", 7
  AddItem "ijkl", 8

  For Each item In List
    Debug.Print item.Key, item.Value
  Next

End Sub

Sub AddItem(Key As String, ByVal Value As Single)
Dim item As Class1

  On Error Resume Next
  List(Key).Value = List(Key).Value + Value

  If Err.Number > 0 Then
    Set item = New Class1
    item.Assign Key, Value
    List.Add item, Key
  End If
End Sub

I used to use VB but...
Log file access
DDE Problem
Unknown error 50003
how can i find the number of elements in an array
Can't Launch an app without using a Windows Installer Shortcut
problems with packaging and deploying VB6
List proceedure names all files
Program 'hangs' on password protected screensaver.....
UserControls Blessing and Curse