It's Friday & my head hurts - RESOLVED

[PaddyD]

I know I should be able to work this out, but it's Friday & my head hurts...

I have a large spreadsheet (approx 40,000 rows) that was created from the join between 2 access tables (neither of which exist anymore, so I can;t fix this by re-designing the original access query).

In the first access table there was account number & total. In the second table there was acount number & details. The excel file has account number twice, details & total.

E.g.

account details total
a 10 50
a 40 50
b 10 10
c 10 30
c 10 30

On some occasions, the sum of the details wont equal the total for that account.

How can I return the account numbers (or flag the record) when the details & the total don't add up. (As a small complication, the details need to be rounded before being compared to the total)

I'll post if I make any progress on this, but it's almost the weekend so I don't hold out much hope

As ever, all help gratefully received,

Paddy

[ This Message was edited by: PaddyD on 2002-05-26 16:36 ]

[Yogi Anand]

Hi Paddy, I used the TYPE function to check if some value in the details column is Text rather than a number -- since DSUM will not sum an entry that is Text. See the worksheet simulation

Microsoft Excel - y020523.xls
C16		=
	A	B	C	D	E	F
1					account
2					c	:alert('=TYPE(B5)=2')>FALSE
3
4	account	details	total
5	a	10	50
6	a	40	50
7	b	10	10
8	c	10	30
9	c	10	30
10
Sheet7

Click on a hyperlinked cell to see the underlying formula

Running an advanced filter on your data set, I used the criteria shown in range E1:F2, to pickup in my case errant entry in cell B8

Also =DSUM(A4:C9,"details",E1:E2) gave me 10

This led me to investigating which entry might be TYPE text, and from Advanced Filter, I picked that up as in cell B8.

HTH

Regards!


_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 12:59 ]

[PaddyD]

Hi Yogi,

Thanks for the response. Unfortunately, not only is it a Friday, but I've just come back from lunch, so I guess I'm not really helping myself here. Either I'm totally missing the point (most likely), my original post wasn't clear (fairly likely), or your post isn't the soluiton (massively unlikely).

The reason the sum of the 'details' sometimes don't equal the 'total' for an account is that we've got missing data from the original details table, not because it's coming in as text. So the 'total' might be 30, but we've only got two 10's in the details. I need to identify the accounts where total <> details.

Is this helping??? Am I barking up totally the wrong tree? Is it high time I went home?

Paddy


P.S. I've tought of another problem I've got where the solution you gave is just the thing - thank goodness for serendipity.

[sann0638]

something like:

{=if(round(sum(if(A:A=D1,B:B)),0)=vlookup(D1,A:C,3),"Correct","Incorrect")}

A:C is your original list, D contains a list of the individual accounts.
_________________
Regards, Mike.

[ This Message was edited by: sann0638 on 2002-05-24 01:09 ]

[inarbeth]

Paddy you've probably gone down the pub now or for some shut eye!
I take it from your example that the figures in the total column are correct but that the sum of the details is in some cases incorrect. If so then I think what you need to do is sum all the details where account number is the same, ie a, b, c etc and then to compare the result with the details figure. I would create a list of the unique account numbers and use SUMIF to calculate the sum of details for each of a, b, c etc. In Yogi's sheet this would be =SUMIF(A5:A9,"c",B5:B9). You will have to apply rounding as appropriate.
I would then use the entire table A5:C40004 in Yogi's example as a range and apply VLOOKUP to extract the stated totals for each of a, b, c etc. Then simply subtract one figure from the other from the other and obtain a TRUE or FALSE. You could then use the result as the basis of conditional formatting of column A to highlight where the details don't add up to the total.
I expect this can be solved more succinctly but I like to break problems down to small chunks. Please let me know if this helps.

PS sann0638 has just posted a succinct formula.

[ This Message was edited by: inarbeth on 2002-05-24 01:09 ]

[ This Message was edited by: inarbeth on 2002-05-24 01:12 ]

[Yogi Anand]

Hi Paddy:
Continuing on the approach suggested by sann0638 and inerbeth, you may want to try the following formulas to check for correctness of each account:

=IF(SUMIF(A:A,D1,B:B)=VLOOKUP(D1,A4:C9,3),"correct","incorrect")
where the account number is entered in cell D1 (formula proposed by saan0638 did not work for me ... you may have to include the ROUND function in my formula if necessary)

or you may use the following formula using DSUM and VLOOKUP functions:

=IF(DSUM(A4:C9,2,G1:G2)=VLOOKUP(G2,A4:C9,3),"correct","incorrect")
where G1 houses ... account
where G2 houses ... a
or b, or c one at a time

Regards!

[Mark W.]

Assuming that your data and headers are in A1:C6, enter the formula...

=ROUND(SUMIF($A$2:$A$6,A2,$B$2:$B$6),0)=C2

...into D2, set the 2nd ROUND argument to the desired precision, and fill down as needed. "Bad" records are flagged with FALSE.

[ This Message was edited by: Mark W. on 2002-05-24 15:24 ]

[PaddyD]

Hi all,

Thanks for the suggestions. Have tried all the alternatives, and with some tweeking to fit my data they all produced results I can use.

It's now Monday morning & my head still hurts - I suspect another stupid day is ahead of me!

Paddy