adding rows during copy???

[Guest]

Okay, don't know if this is possible. Don't know how much of this you'll need
but I'm trying to make this the short story.

I have a text file that I currently copy into a master workbook. I have if
statements to compare 4 identifiers (col. a-d). If all 4 match then it
copies the new stuff into the remaining weeks. The users would like to
combine some stuff and only show a sub-total for one of the identifiers. Can
I total these up during the copy process?

SAMPLE RAW DATA
Jon Doe.....A1....B1....C1....Wk1...Wk2...etc
Jon Doe.....A1....B1....C2....Wk1...Wk2...etc
Jon Doe.....A1....B1....C3....Wk1...Wk2...etc
Jon Doe.....A1....B1....C4....Wk1...Wk2...etc
Jon Doe.....A1....B2....C1....Wk1...Wk2...etc
Jon Doe.....A1....B2....C2....Wk1...Wk2...etc

Now I only need the sub-total for all B1s, I still need the details for B2s.

EXAMPLE CLEANED DATA
Jon Doe.....A1....B1....C....SumWk1...SumWk2...etc
Jon Doe.....A1....B2....C1....Wk1...Wk2...etc.
Jon Doe.....A1....B2....C2....Wk1...Wk2...etc.

Someone suggested using an array but I'm not very familiar with them.

Am I stuck copying to the master and then doing a sub-total?

 

[Guest]

Yes it is possible to do during the read operation, but it is not
recommended. the problem is when you find a B1 then you have to search the
remaining part of the file for the matching data in columns A-D. Then you
have to rewind the open file and get back to the same line that you started
with.

Using Arrays basically means to read the data into memory before pasting the
file into the spreadsheet. Again for large files you end up using a lot of
memory in the computer.

The best way is to create a temporary spreadsheet to read the data into.
then extract the data from the temporary spreadsheet to the master worksheet
and then clear the temporary worksheet.

Using the temporary will make debugging the code easier than the other two
approaches (rewinding file, or using arrarys).

I would use a cleaver trick in extracting the lines from the temporary sheet
by using an extra column. Every time you extract a line from the temporary
sheet add a one to the new column. You algorithm would look something like
this


for each row in temporary

if B1 row to master sheet

else B2
if not 1 in new column
get sutotals from present row
for current row to last row
If columns A-D match
Add values to subtotals
enter 1 in new column
end if
Next row
end if
end if
next row