Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Sorting issue

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 Posts

    Sorting issue

    I have a large body of data on Sheet1 (500 rows). For now, about 200 are blank.

    On Sheet2, I use a formula in column A to select the rows of Sheet1, col H, and remove duplicates. (row 1 is a heading row)
    Col A in sheet2 corresponds to col H in sheet1.
    =IF(COUNTIF(Sheet1!$H$2:Sheet1!H2,Sheet1!H2)=1,She et1!H2,"") [filled down]

    There are formulas in each row of Sheet2 that do calculations based on identifying the person in column A, matching that person's unique entry with data in Sheet1 (does a consolidation).

    If I sort Sheet1 over a column other than A, then sort Sheet2 over it's column A (and filter out the blanks), the unique formula changes significantly. I want a user to be able to enter new data in Sheet1 and have that consolidated in Sheet2 with the new sorting based on col A. It seems to me that the unique formula now won't work.

    Any thoughts?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi

    It would be easier to work with a sample file.
    Can you attach one?

    zeddy

  4. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 Posts
    Here's an illustration of what I'm trying to do.

    Thanks, in advance.

    Kevin
    Attached Files Attached Files
    Last edited by kweaver; 2013-04-01 at 11:59. Reason: sample file attached.

  5. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi

    Perhaps it would be easier to use a different method.
    see attached file.

    This sample allows you to add data as required on [sheet1]
    On [Sheet2], a [Refresh Data] button (with macro assigned) will extract unique names from column [H] of sheet1, and then update formulas as specified on sheet2 (in row2).
    You can sort as you like on both sheets, on any column.

    Perhaps you could adapt this to suit your needs?

    Or give us your sample file to work with.

    zeddy
    Attached Files Attached Files

  6. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 Posts
    AH. Let me look at this macro. Thanks.

    Zeddy, I'm getting an error on this line of the macro and then Excel 2010 crashes.

    Sheets("AllocateHours").[a1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=[a1], Unique:=True

    "AllocateHours" is Sheet1

    Since I have formulas on Sheet2 that related to column A on Sheet2, I don't think I want to delete all the rows on Sheet2 below the header row...do I?

    [a1].CurrentRegion.Offset(2).EntireRow.Delete 'delete all rows beneath row 2

    I think I need those calculations in columns B to the last column, but reset the names in col A.

    Kevin
    Last edited by kweaver; 2013-04-01 at 12:21.

  7. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi

    Advanced filter requires any entries in the filter output range to be exact copies of the filter source range.
    So check that the entry in cell [a1] on the output sheet matches your column [H] on sheet [AllocateHours]
    Better still, place this formula in cell [A1] on the output sheet..
    =AllocateHours!H1
    ..assuming it is column [H] that has the names.

    zeddy

  8. #7
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    so for example, if your source sheet has PERSON in cell [H1], then cell [A1] on the output sheet must also be PERSON

    zeddy

  9. #8
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Re: "Since I have formulas on Sheet2 that related to column A on Sheet2, I don't think I want to delete all the rows on Sheet2 below the header row...do I?2

    The deletes start from row 3.
    (The offset(2) means go down two rows from [a1] so that means from row 3

    zeddy

  10. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 Posts
    The first row in both sheets has column headings.

    On Sheet 2, ByResponsiblePerson , the columns run to BV.

    I'm getting an error on this line of the macro:

    [b2:bv2].Copy Range(temp) 'copy existing formulas in row 2 to range

    Prior to that line, I changed the reference to:

    zlastRow = [a65536].End(xlUp).Row 'find last entry in column [A]; e.g. 157
    temp = "b2:bv" & zlastRow 'compute copy-to range; e.g. "b2:az157"
    Last edited by kweaver; 2013-04-01 at 14:04.

  11. #10
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi

    I can't see what the problem is.
    The vba line should compute to something like..
    [b2:bv2].Copy Range(b2:bv529)
    ..where zLastrow = 529 (for example)

    ..it must be a problem with the value of 'temp'
    Can you step through the code and check the value of 'temp'?
    It must be that the compute valued of 'temp' is wonky.

    zeddy

  12. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 Posts
    zLastrow = 56 and it's showing [b2:bv56]

    Hmmm. That's the last row (56) of Sheet2 of unique names.

    Zeddy,

    I changed: Range(b2.bv56) to start in b3 and it worked! Hmmm.

    So, Zeddy, educate me more...in VB, it seems you cannot paste over what you've selected...right?
    Last edited by kweaver; 2013-04-02 at 08:52.

  13. #12
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi

    No. You can paste over a destination range which includes your 'source range'.
    So it's quite OK to use something like [b2:bv2].copy Range(temp), where temp = "b2:bv56"
    I use this method quite often.
    So still puzzling, without seeing the real data.
    Any merged cells in the range will often cause problems, but I don't think that would be the case here.

    zeddy

  14. #13
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi Kevin

    In my example file I showed how to use a [Refresh Data] routine to extract all the unique Persons from a data source sheet, then copy pre-defined formulas (in a specified row and range) to correspond alongside these extracted Persons.

    If you have really large numbers of 'unique' Persons in your source data, then this 'rebuilding' of the formulas on the summary extraction sheet can take some time, particularly if your formulas extend accross lots of columns.

    So for really large data sets, the way to do it would be to leave any existing Persons and formulas on the summary sheet, and then just 'append' any new Persons that are not already on the summary sheet, and then just copy the predefined formulas for these fewer records.

    So, to do this, you use an 'Updates' sheet, which extracts the unique Persons from the raw source sheet (as before), but then uses a simple formula to detect whether any of these are 'new Persons' or not (by using a MATCH formula to check against Persons on the summary sheet).

    If you detect new Persons (using a SUMIF formula cell to show total 'new Persons' found), then you simply do another filter extract to extract just the new Persons to a new location. You then copy these 'new Persons' to your summary sheet (appending them at the bottom of existing entries) and then copy the required formulas to this new location.

    Do you follow that, or would you like me to create a sample file?

    zeddy
    Last edited by zeddy; 2013-04-02 at 16:43.

  15. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 Posts
    Thanks, Zeddy. I follow that.

    I don't think this is too large, actually, with <500 rows and generally fewer than 100 columns.
    I don't think this is large, do you? Based on these numbers, I think your original macro is just fine.

    What I'm unclear about is why it didn't work until I change the start of the range from: [b2:bv56] to: [b3:bv56]

    Kevin

  16. #15
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    Hi Kevin

    Re: "What I'm unclear about is why it didn't work until I change the start of the range from: [b2:bv56] to: [b3:bv56]"

    ..you and me both.
    Have you tried changing it back from b3:bv56 to b2:bv56 and trying it again????

    zeddy

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
  •