why are my bottom two contras not matching 2? can someone help

Why the the green lines not saying 2?

the top lines are fine

iv concatanetd the name, amount,
i have a countif in there also

here i forgot to add the file thanks alot guys

sorry i cannot upload a file as its too large but if you can help i would appreciate it:

bascically

in column AD it counts how many 180 there are above - its got over 100,000 lines so if you look at cell AI 971, at the end it says 180-4, this is because it looks at column AD and see how many times before 180 has come so it says its seen 18- 4 times before ( i am trying to net the same figures) problem is row 973 should match 971 but because the concat at the end says 180-4 & 180-1 it counts that as two separate lines and gives me the incorrect number 1 in column AJ

please see picture

if you look at column AI cels 971 and 973 they are exactly the same and should net off, but due to concat and countifs it doesnt match

does it have to be sorted a special way?

Attachment 868607

Originally Posted by popipipo

C7 and C8 are different
C9 and C10 are different

I don't know what you want to count in column E

sorry please see above my explnatation if you can help would really apprecaite

iv summarised more clearly:

Hi All,

I thought this formula was working as it did find a lot of contra entries, which then shown in column AJ a 1 or 2 – 2 meant it has a matching contra based on the concat in AI, 1 meant it cannot find a matching entry

I was happy until I randomly checked an amount (180) and realised it as 1 when it should have been 2

In column AD it counts how many 180 there are above (its filtered on 180) - its got over 100,000 lines so if you look at cell AI 971, at the end it says 180-4, this is because it looks at column AD and see how many times before 180 has come - so it says its seen 180- 4 times before ( i am trying to net the same figures) problem is row 973 should match 971 but BECAUSE the concat at the end says 180-4 & 180-1 it counts that as two separate lines (non matching) and gives me the incorrect number 1 in column AJ


if you look at column AI cells 971 and 973 they are exactly the same and should net off, but due to concat and countifs it doesn’t match, it thinks its different

I also I think need the countifs formula in Column AI as it if do not the countif in column AJ will show more numbers than 1 or 2 which is incorrect

As an exercise I deleted the 180’s in cells AD 73,136,220 and guess what the cell AJ 971/972 show a 2

also posted here:

https://www.mrexcel.com/board/thread...-5gbp.1258644/