Excel Help

Kido

This is a roughly what the spreadsheet looks like.



Basically I get around 6,000 lines a month like this and rows with a balance in them need investigating (I have to delete the nil lines) unless (which is not shown on here), somewhere further down the rows, the same figure re-appears in which case I'd like those lines to be on another spreadsheet so I check if the matches. In the perfect world I'd like the 3 different spreadsheets, the main spreadsheet with all the data on I started with, a second one with all the rows on where there is the same amount of positive and negative, just on different rows (e.g. Row 5 would have a 7 in it and Row 23 the -7). The third spreadsheet would have the rest of the rows on. The check though should only contain data from columns for 1 and 6 or 2 and 7.

Hope this makes a bit more sense.

If the picture of the spreadsheet doesn't show please can someone let me know how I include it.

Kido

Title A B C D TOTAL
A 1 -2 -1
B 2 -2 0
C 3 -2 1
D 4 -3 1
E 1 -1 0
F 1 -2 -3
G 2 -3 -1
H 5 -5 0

This is a rough adaption of the spreadsheet. The columns are actually showing transaction amounts. There should be a positive and a minus for each transaction, and my job is to find and investigate where they're incorrect. The first 2 columns should have one set of plus and minus figures, columns 3 and 4 should be the same and on my full spreadsheet 5 & 6 and 7&8 as the same.

I do hope I'm starting to make a little sense.

Tom99

Kido wrote: »

Title A B C D TOTAL
A 1 -2 -1
B 2 -2 0
C 3 -2 1
D 4 -3 1
E 1 -1 0
F 1 -2 -3
G 2 -3 -1
H 5 -5 0

This is a rough adaption of the spreadsheet. The columns are actually showing transaction amounts. There should be a positive and a minus for each transaction, and my job is to find and investigate where they're incorrect. The first 2 columns should have one set of plus and minus figures, columns 3 and 4 should be the same and on my full spreadsheet 5 & 6 and 7&8 as the same.

I do hope I'm starting to make a little sense.

Your link does not work, try:
https://imgbb.com/
So in the above example Col D should be Col B + Col C? Therefore they are all correct except F which should be 1 -2 -1 not 1 -2 -3?
If the formula is not already in Col D then use a spare Col to put a checking formula in eg:
=if(B5+C5=0,0,1)
If the above results in 1 then there is an error.
You can filter the list just to show those rows where there is an error and if you want highlight and copy the filtered list to a different sheet which will then be a list of just the errors.

stragglebod

OP, serious question: is English not your first language? Do you know anyone who can help you articulate what you want a bit better?

that

[QUOTE=Kido;76386316][code][FONT="Courier New"]Title    A        B        C        D        TOTAL
A        1        -2                            -1
B        2        -2                             0
C        3        -2                             1
D        4        -3                             1
E        1        -1                             0
F                          1       -2           -3
G                          2       -3           -1
H                          5       -5            0
[/FONT]

This is a rough adaption of the spreadsheet. The columns are actually showing transaction amounts. There should be a positive and a minus for each transaction, and my job is to find and investigate where they're incorrect. The first 2 columns should have one set of plus and minus figures, columns 3 and 4 should be the same and on my full spreadsheet 5 & 6 and 7&8 as the same.

I do hope I'm starting to make a little sense.[/QUOTE]
it makes a lot more sens if you format it correctly. on F 1-2= -1? or am i missing something?

what you want to do is create a 4th column in there you want the formula ABS(AD) or in real terms if the number starts at A2 (first column, second row down) the the formula coumn would be E2 and the formula in the sixth column which you will create will be ABS(E2)

Drag the ABS(E2) sown so it becomes ABS(E3), ABS(E4), ABS(E5) ....

Then use pivot tables as previously suggested.

Please in the future, rather than letting people stumble around and keep wading through crud, if something looks wrong, or out of place, then tell the people so, rather than let them find the issues out by chance

Tom99

Try this:
https://ibb.co/4MPXTGw

WaywardDriver

Tom99 wrote: »

Try this:
https://ibb.co/4MPXTGw

But what if B & C are incorrect and D & E also incorrect but the discrepancies cancel each other out?
e.g. 1 -2 3 -2 gives sum=0.

Tom99

WaywardDriver wrote: »

But what if B & C are incorrect and D & E also incorrect but the discrepancies cancel each other out?
e.g. 1 -2 3 -2 gives sum=0.

From what the OP has described there are only 2 entries per row so if B & C are completed then D E F G H & I will be blank but maybe the OP can confirm that.
If there can be more than 2 entries per row the checking formula will need slight adjustment.

WaywardDriver

Tom99 wrote: »

From what the OP has described there are only 2 entries per row so if B & C are completed then D E F G H & I will be blank but maybe the OP can confirm that.
If there can be more than 2 entries per row the checking formula will need slight adjustment.

Agreed - more input from OP needed.