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
"Rominall" wrote:
> 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?
|
Similar Threads
