# Error in formula while tidying a list

1. ## Error in formula while tidying a list

Hi Guys,

I have in Column A
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.  Register To Reply

2. ## 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))),"")  Register To Reply

3. ## 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

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)  Register To Reply

4. ## 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.  Register To Reply

5. ## 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

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 ``Please Login or Register  to view this content.``
With the changes I made highlighted in red but it did not work  Register To Reply

6. ## Re: Error in formula while tidying a list

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

Formula:  `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.  Register To Reply

7. ## 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.  Register To Reply

8. ## 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.  Register To Reply

9. ## 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.  Register To Reply

10. ## 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  Register To Reply

11. ## 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.  Register To Reply

12. ## 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.  Register To Reply

13. ## 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.  Register To Reply

14. ## 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   Register To Reply

15. ## 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.  Register To Reply

##### Users Browsing this Thread

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

#### 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