Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts

    reducing the number of copy operations during an update

    Hi

    In the sample Branch file attached:

    I have a [Master] sheet and two Dept sheets that share the same column heading structure.
    I have a [code] column in column [F] on each sheet.

    The goal is to update cells on each Dept sheet with corresponding entries from the Master sheet for specified columns, where the code matches.
    I wish to copy the cells, not just the values (in the real data there may be cell comments, formulas etc which I wish to preserve).
    (I have coloured some of the cells to make testing easier).

    On the sample [Master] sheet:
    The [code] column contains 3000 unique entries.
    Columns I,M,N,R,S,T,U contain data that I wish to copy to the Dept sheets, for rows where the code in column [F] matches.

    The columns to be updated on each Dept sheet will always be the same for a particular Branch file, but could be different and not necessarily adjacent to each other.
    For the purposes of this example, I have an Array which specifies which column numbers on the Dept sheets are to be 'updated' from the [Master] sheet.

    I could simply use a vba routine that, on each Dept sheet, loops down the code entries in Column [F] looking for a corresponding Code match in the [Master] sheet, and, if found,
    copy the required cells from the matching row from the [Master] sheet.
    NOTE: All other columns on the Dept sheet must be left 'as is'.

    So, for the 500 records on sheet DeptA, if each code was found on the [Master] sheet, this would involve
    500x7 = 3,500 cell copy operations (for 7 columns to be updated).

    For DeptB, 540 records, this could involve
    540x7 = 3,780 cell copy operations.

    My intention is to speed up this process, and reduce the number of copy operations to just 7 operations per each Dept,
    i.e. instead of copying on a per-cell basis, to align and copy each of the 7 update columns on a per-single-column-block basis.
    (In the real data, there may be 75 columns in the data blocks, and 20 columns to 'update').

    To do this, processing each Dept sheet at a time, I can use a new column labelled Sort, with a formula on the [Master] sheet to match the codes on the specified Dept sheet.
    This formula could return the row number for the matching code.
    I could then extract those matching records (together with their 'Sort' row numbers) to a temp sheet.

    On the Dept sheet, I could use a formula to cross-ref and find which record row numbers on the Dept sheet are NOT found on this temp sheet.
    (For example, the Dept sheet may contain new records with Codes not found on the Master sheet).
    I could then 'append' these 'missing row numbers' to the bottom of the 'sort' column.
    If I then sort the records on the temp sheet by this 'Sort' column, I will now have a block of data which now 'aligns' i.e. matches the sequence of records on the Dept sheet.
    So I can then copy the 7 required colums in 7 copy operations instead of many thousands.

    Unless you think there is a faster, better way of doing this??????

    zeddy
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 Posts
    I know you want/need an Excel solution/opinion. However, I found this interesting in a general sense. I use a programming language called APL and copied the cell data into APL, found the heading data that was needed for each department. In total, the entire process took less than 2 seconds to process. There's an interface in APL with Excel.
    Anyway, just my nickel's worth. Sometime give APL a gander...quite interesting.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    How about (temporarily) rearranging the columns to make the ones of interest all together

    Then sorting to combine the code of the ones you are interested in copying (this may require a temp column to keep existing rows separate from new ones)

    Copy them over as a groups

    Resort to original order (if desired)

    Rearrange columns to original placement

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi

    ..ah but would APL restore cell comments, formulas, formatting, conditional formats etc that may be 'included' in the Master source cells. It's not just the cell 'values' that need to be restored, otherwise we could simply load the data into Excel arrays and process the array data values.
    But 2 seconds to process is an excellent result!

    zeddy

    zeddy

  5. #5
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi Steve

    By (temporarily) rearranging the columns of interest together, I could conceivably reduce the copy operations for each record from 7 to 1 i.e. for DeptB reduce from 3,780 operations to 540x1.
    Or, if you mean group the columns together and align the rows, then reduce the copy operation to 1 only, and then re-sort the columns back to the original.
    But I would settle for 7 copy operations per Dept at this stage.

    zeddy

  6. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi Steve

    I've had another look at the real data, and rearranging columns would involve complicated steps because there are potentially 'merged' cells spanning across columns in the top-section area of the Dept sheets.

    But I liked the idea.

    zeddy

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Could you eliminate the merged cells and change to center across selection instead?

    My suggestion was 1 copy operation per code by rearranging the columns.

    What is the reason for only copying particular columns? If you sort you could copy 1x per code all the rows with all the columns as one group. Or if they must be blank, you could clear their contents...

    Steve

  8. #8
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi Steve

    The merged cells are part of pre-existing design requirement. Not my idea.
    The reason for copying only particular columns is that these columns relate to user-entered data.
    The Master sheet could relate to say, prior week data.

    The Dept sheets in the posted example file would have new values imported into all columns except those that are 'user entered' columns - the latter 'user-entered' columns would be initially blank on the Dept sheets, but all other columns would contain current data (which I have left as blank because the actual data is irrelevant for the purpose of the example).

    So the object is to 'return' any previously recorded user-entered' data (which is saved on the Master sheet) to the latest Dept sheet where there is a match for a Code in column [F]

    zeddy

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,477
    Thanks
    211
    Thanked 849 Times in 781 Posts
    Zeddy,

    The more I read in this thread the more I hear the voice in my head saying Database Application! I'm just sayin'...
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 Posts
    Zeddy, I use APL where there are array problems that normally would loop. APL rarely loops and is an array processing language.
    Then, I use Excel for whatever formatting is required.

  11. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi RG

    ..ah but could you use cell comments, formulas, formatting, conditional formats etc that may be 'included' in the Master source individual cells in the 'database' records?

    You could of course have a 'comments' field in the database, but then that's making provision for every record. You would also need to make provision for a corresponding comments field for every column in the Excel record row. Excel allows you to add comments 'on-the-fly' to each and/or any of the cells in the 'record row' on a record-by-record basis, plus don't get me started on the formatting options..
    ..well let's just say that this Excel is a 'front end' and is 'fed' from a database so it is 'working together' with databases.

    zeddy

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,477
    Thanks
    211
    Thanked 849 Times in 781 Posts
    Zeddy,

    With a well designed DB you could get most of that. However, I think the best thing you would get from going to a DB would be a review of the actual informational requirements. Maybe I'm missing something here but comments placed at random on any cell in thousands of records seems like a data/information management nightmare to me. Of course as always YMMV!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. #13
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi RG

    It's not that comments may be placed in thousands of records. That would be nuts.
    It's that Excel allows you to place an important note anywhere, explaining why a cell contains a particular abnormal value for example.
    So Excel is flexible to allow a note to be added to any 'field' of a data record.
    This can be very important in multi-user Planning applications, for example.
    Cell comments can also allow different users (Managers) of the spreadsheet to understand why contents are as they are, particularly for 'unexpected' values.
    Excel also allows you to have dynamic conditional formatting using icon sets with cell values.

    But of course, most large scale Excel spreadsheets rely heavily on robust databases that hold the relevant data for Excel's top-class analysis and forecasting capabilities.

    I am sure that the sample task I have described in this thread could benefit perhaps from an ADO feature that would allow manipulation of records to be saved to a recordset, merged, selected and re-organised.
    A SELECT *this* FROM *that* WHERE *I want this* AND INNER JOIN *blah blah blah* comes to mind.
    But for other reasons not described here, my requirement is to crunch this data in Excel.
    I agree a DB could certainly do a lot of what is required here, but to get 'most of that' doesn't nail it if the owner wants 'all of that'. Even if you tell them.

    zeddy

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I think you debating a couple of things. One is about the form of the data storage and the other is about data analysis and reporting. I think the data should be stored in such a way to make sense which includes ways to analyze it (and excel has many built-in features to do that).

    RG's remarks about thousands of comments, you seem to agree with, claiming it would be nuts, but you also like the idea since excel allows it. The problem you are facing, to me, seems poor design to have a master and copies of parts of the master, that need to be updated. Better to have the master as the best set and then use the features (filtering, pivots, etc) to get the data subset you want viewed or analyzed at run time and do away with trying to keep multiple datasets and subsets "synced".

    I understand (believe me, it is the same in most companies) that sometimes management makes odd decisions, but there are times, where you need to convince them that there is a better way. It is not just about ease of entry but also must include data validation, data integrity, etc.

    Off the soapbox, for now.

    Steve

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,477
    Thanks
    211
    Thanked 849 Times in 781 Posts
    Zeddy,

    I agree a DB could certainly do a lot of what is required here, but to get 'most of that' doesn't nail it if the owner wants 'all of that'. Even if you tell them.
    I couldn't agree more! What I used to do in this situation is explain the effort (as pointed out by Steve), read COST, of getting it all. I was always amazed how the requirements changed when $ are factored into the equation. I'll join Steve in getting off my soapbox now also.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Page 1 of 2 12 LastLast

Posting Permissions

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