+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

Error in formula while tidying a list

  1. #1
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    292

    Error in formula while tidying a list

    Hi Guys,

    I have in Column A
    A1 Fundraiser (Heading)
    A2 Race Night B2 300
    A3 Golf Classic B3 200
    A4 Blank
    A5 Blank
    A6 Blank
    A7 Disco B7 200

    In Coumn C & D I wanted to tidy them all up to read
    C2 Race Night D2 300
    C3 Golf Classic D3 200
    C4 Disco D4 200

    To do this I have the following formula in C2 and dragged down and across.
    [CODE{=IF(ISERROR(INDEX(A2:A15,SMALL(IF(A$2:A$15<>"",ROW(A$2:A$15)-1),ROWS($1:2)))),"",INDEX(A$2:A$15,SMALL(IF(A$2:A$15<>"",ROW(A$2:A$15)-1),ROWS($1:2))))}[/CODE] "The Curley Brackets are from Control, Shift & Enter"

    The Result is
    C2 Golf Classic D2 200
    C3 Disco D3 200

    It is ignoring the 1st one for some reason. I think it has something to do with the -1,Rows($1:2) part but have messed with this and cannot get it.

    Any suggestions.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,922

    Re: Error in formula while tidying a list

    It should work as it is if you change it to ROWS($1:1), this is probably a bit over the top for your needs, but it is a more robust version, less prone to error if you insert or delete rows.

    =IF(ISERROR(INDEX(A$2:A$15,SMALL(IF(A$2:A$15<>"",ROW(A$2:A$15)-ROW(A$2)+1),ROWS(C$2:C2)))),"",INDEX(A$2:A$15,SMALL(IF(A$2:A$15<>"",ROW(A$2:A$15)-ROW(A$2)+1),ROWS(C$2:C2))))

    Are you still using excel 2003? If you have updated to a newer version that shown on your profile, then you could try this version instead.

    =IFERROR(INDEX(A$2:A$15,SMALL(IF(A$2:A$15<>"",ROW(A$2:A$15)-ROW(A$2)+1),ROWS(C$2:C2))),"")

  3. #3
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    292

    Re: Error in formula while tidying a list

    Hi,

    Thanks for the reply. I am unfortunately on 2003. My employer hasn't upgraded and as a lot of what I do at home is needed in work it is easier to build all sheets in 2003 to make it comparable.

    that seems to have worked but is it possible to tidy the list further. eg

    A1 Fundraiser (Heading)
    A2 Race Night B2 300
    A3 Golf Classic B3 200
    A4 Blank
    A5 Blank
    A6 Blank
    A7 Race Night B7 200 (duplicate)

    In Coumn C & D I wanted to tidy them all up to read
    C2 Race Night D2 500
    C3 Golf Classic D3 200


    ie (Removing & combining duplicates)

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,922

    Re: Error in formula while tidying a list

    Try this one in C2, Array entered

    =INDEX($A$2:$A$15,SMALL(IF(ISERROR(MATCH($A$2:$A$15,$C$1:$C1,0)),ROW($A$2:$A$15)-ROW($A$2)+1),1))&""

    and this one in D2, normal enter, not array

    =IF(C2="","",SUMIF($A$2:$A$15,C2,$B$2:$B$15))

    I've assumed that the Euro symbol is applied by formatting, if it has been typed into the cells with the amount then a much different formula will be needed to extract the amounts, or an extra column to separate the symbol from the amount first.
    Last edited by jason.b75; 12-29-2018 at 07:34 PM.

  5. #5
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    292

    Re: Error in formula while tidying a list

    Hi Jason,

    Thanks for that. When I input the Array formula and drag it down from A2-A15 I get a #Num! error from C6 down??

    Also

    I have a few lists on the same page that I want to tidy individually so again it would be

    A121 Fundraiser (Heading)
    A122 Race Night B2 €300
    A123 Golf Classic B3 €200
    A124 Blank
    A125 Blank
    A126 Blank
    A127 Race Night B7 €200 (duplicate)

    In Coumn C & D I wanted to tidy them all up to read
    C122 Race Night D2 €500
    C123 Golf Classic D3 €200

    I tried to adapt your formula
    Please Login or Register  to view this content.
    With the changes I made highlighted in red but it did not work

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,922

    Re: Error in formula while tidying a list

    The last part should always be +1, regardless of row.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Apart form that, it looks like you've adapted it correctly

    Not sure why you're getting #NUM! errors, unless you've changed that end number in the formula causing the problem.
    Last edited by jason.b75; 12-30-2018 at 05:43 PM.

  7. #7
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    292

    Re: Error in formula while tidying a list

    Hi

    Changing to +1 seems to work however I am still getting the Num error on every list. I'm not sure if this helps but the formula that is in Column "A"
    Please Login or Register  to view this content.
    I have uploaded a sample. I saved it as 2007 as someone previously advised me that makes it easier for you giys to open.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,922

    Re: Error in formula while tidying a list

    It's the formulas returning the blanks causing the problem, this will fix it.

    =IF(ISNUMBER(SMALL(IF(ISERROR(MATCH($A$2:$A$15,$C$1:$C1,0)),ROW($A$2:$A$15)-ROW($A$2)+1),1)),INDEX($A$2:$A$15,SMALL(IF(ISERROR(MATCH($A$2:$A$15,$C$1:$C1,0)),ROW($A$2:$A$15)-ROW($A$2)+1),1)),"")

    Just looking to see if there is an easier way before you change all of those formulas.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,922

    Re: Error in formula while tidying a list

    Here's a generic method for you, if it's suitable.

    First enter a single * character into A166 (to mark the end of the list in column A).

    Then enter this formula into E1, and fill it down as far as E166 (this is to identify the start ane end of each group in column A by looking for the * characters in the descriptions).

    =IF(RIGHT(A1,1)="*",ROW(A1),"")

    Enter this megaformula into C2, then array confirm it and fill down to C166

    =IF(ISNUMBER(SMALL(IF(ISERROR(MATCH(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$166)-1),INDEX($C:$C,MAX(E$1:E1)):INDEX($C:$C,ROW(C1)-MAX(E$1:E1)+1),0)),ROW(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$166)-1))-MAX(E$1:E1)+1),1)),INDEX($A:$A,SMALL(IF(ISERROR(MATCH(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$166)-1),INDEX($C:$C,MAX(E$1:E1)):INDEX($C:$C,ROW(C1)-MAX(E$1:E1)+1),0)),ROW(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$166)-1))-MAX(E$1:E1)+1),1)),"")

    Finally, this one in D2, fill down to D166

    =IF(C2="","",SUMIF(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$65536)-1),C2,INDEX($B:$B,MAX(E$1:E1)+1):INDEX($B:$B,MIN(E2:E$65536)-1)))

    Hope this helps.

  10. #10
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    292

    Re: Error in formula while tidying a list

    Wow that some formula

    That mega formula just gave me Race Night in every other list down the page in every cell
    Attached Files Attached Files

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,175

    Re: Error in formula while tidying a list

    If Jason's suggestion takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

    Forum Rules Updated September 2018. Please read them by clicking here.

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,922

    Re: Error in formula while tidying a list

    Megaformula take 2

    =IF(ISNUMBER(SMALL(IF(ISERROR(MATCH(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$166)-1),INDEX($C:$C,MAX(E$1:E1)):C1,0)),
    ROW(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$166)-1))),1)),INDEX($A:$A,SMALL(IF(ISERROR(MATCH(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,
    MIN(E2:E$166)-1),INDEX($C:$C,MAX(E$1:E1)):C1,0)),ROW(INDEX($A:$A,MAX(E$1:E1)+1):INDEX($A:$A,MIN(E2:E$166)-1))),1)),"")

    Too many row adjusments in the formula, everything was reverting back to the top of the sheet. Should work this time

    I was trying to do it all in one without the need for the extra formula in column E, but it was falling over on circular references.
    Last edited by jason.b75; 12-30-2018 at 08:17 PM.

  13. #13
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    292

    Re: Error in formula while tidying a list

    Absolute genius and gent.

    Thanks so much for taking the time to help me with that one.

    Working perfect now.

  14. #14
    Forum Contributor Nitro2481's Avatar
    Join Date
    09-09-2014
    Location
    Laois, Ireland
    MS-Off Ver
    2003
    Posts
    292

    Re: Error in formula while tidying a list

    Hi Jason

    Not sure if you are still subscribed to this thread but there seems to be a glitch. I have added a third account to the "combined" sheet and dragged every heading down accordingly. I have also amended your formula accordingly to allow for the extra lines. If you look at the fundraising on the combined sheet there is a space between lotto for some reason but if you look down as far as "Other funding " Miscellaneous has no gap in column D. Any ideas whats gone wrong? If I put lotto in the first account it gets rid of the gap making me think that everything in either the second or third account will need to be in the first account but in the example I gave miscellaneous is only in the second account but left no gap
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    06-08-2012
    Location
    swindon, england
    MS-Off Ver
    Excel 2016 / Windows 10
    Posts
    3,922

    Re: Error in formula while tidying a list

    It's not the gaps in the individual account sheets, but the first gap in column A of the combined accounts.

    From what I can remember, I don't think that there was a gap in the middle of the list of items in Fundraising* previously, so this possible error was one that didn't get allowed for.

    Simple fix, in C1 enter

    =""

    The first formula in column A that was returning a blank was carrying over to the consolidated list in column C, by entering a blank formula at the top of column C this can be prevented.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1