Duplicate Transaction Reference

**Markulane** · 10-12-2021, 11:44 AM

Hi,

I am trying to mark as YES the duplicated reference for different journals. I tried combination of SUMIFS but for some reason I am getting duplicated results for Journal instead of Transaction Reference.

Any advice? Thanks a lot!

Please find attached document

**Markulane** · 10-12-2021, 12:04 PM

The tricky part is not to flag duplicated for the same Journal. It is duplicated if same reference for different journals.

Thanks once more

**PeteABC123** · 10-12-2021, 12:09 PM

Try this in C2 and copy down

Please Login or Register  to view this content.

Flags as yes for duped trans ref's.

Pete

**Markulane** · 10-12-2021, 12:19 PM

Thanks for your reply

I've attached the real table I am working on and it shows an invalid duplicate.

**Markulane** · 10-12-2021, 12:38 PM

it is not duplicated if the journal is the same.

**PeteABC123** · 10-12-2021, 12:49 PM

Pardon my confusion, but what are you evaluating to be a duplicate, and under what circumstances would col c return a yes (or whatever you want returned). . .?
Please be specific.

Thanks.

**Markulane** · 10-12-2021, 12:55 PM

I would like to make sure a Transaction reference appears only in one Journal number. Two different journals numbers cannot have the same Transaction Reference. If the transaction reference appears in multiple lines for the same Journal number it is not considered as duplicated because the journal number is the same.

**FlameRetired** · 10-12-2021, 02:53 PM

I too am somewhat confused. So I speculated your intent to be in the attached.

For clarity find a "helper" column in D with this formula

Formula:

Please Login or Register  to view this content.

then in column E

Formula:

Please Login or Register  to view this content.

Those two formulas can be combined to produce the same results. I feel that splitting them makes the logic clearer.

Are we there yet?

**Markulane** · 10-12-2021, 06:23 PM

Hi Dave,

I am really sorry, I am not expressing myself correctly.

We are not there yet and I will do my best to explain better (I am really interested in this solution). It might appear to be simple...

I need the result "Duplicated Transaction Reference" in Column C, when:

The transaction reference is equal better two journal numbers

i.e.:

HSBC-SGD-SGA24091GUOFKTHC 326400
HSBC-SGD-SGA24091GUOFKTHC 326401

but if you look the result on journal number 320899, with these formulas the result is "Duplicated Transaction Reference" and if you search, that Transaction in unique (the Transaction Reference IV2021/04/0119** is not duplicated). Why the result is duplicated?

Thank you so much for your help. i really need to find a formula for this...

**FlameRetired** · 10-12-2021, 07:47 PM

(the Transaction Reference IV2021/04/0119** is not duplicated). Why the result is duplicated?

The "*" and "**" are wildcards. COUNTIF(s) can use wildcards and return counts for whatever follows the "IV2021/04/0119" part in

the source data. Since I have seldom done work compensating for the effects of literal "*" in the actual data I can only guess that

COUNTIFS is treating these as wildcards and from there returning those as duplicates rather than recognizing "*" as literal characters.

There is a way around this that requires leading "~" characters. I used it once many years ago (under adult board supervision LOL

). I may have to call for help again rather than make you wait.

I don't understand your meaning in this next quote. I don't even know what questions to ask.

The transaction reference is equal better two journal numbers

i.e.:

HSBC-SGD-SGA24091GUOFKTHC 326400
HSBC-SGD-SGA24091GUOFKTHC 326401

but if you look the result on journal number 320899, with these formulas the result is "Duplicated Transaction Reference"

**protonLeah** · 10-12-2021, 10:24 PM

D2:

Please Login or Register  to view this content.

e2:

Please Login or Register  to view this content.

**rorya** · 10-13-2021, 05:53 AM

You can just amend Dave's formula to:

=COUNTIFS($A$2:$A2,SUBSTITUTE(A2,"*","~*"),$B$2:$B2,B2)

to cater for the wildcards

**josephteh** · 10-13-2021, 09:31 AM

Try this formula:=IF(SUMPRODUCT((A$2:A$842=A2)*(B$2:B$842<>B2))>0,"Duplicated Transaction Reference","Ok")

**Markulane** · 10-13-2021, 10:57 AM

Hello josephteh,

It work brilliantly...

I need to explore more the function SUMPRODUCT.

Thank you all for the great support solving this problem!

Duplicate Transaction Reference

LinkBack

Thread Tools

Rate This Thread

Display

Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Re: Duplicate Transaction Reference

Thread Information

Users Browsing this Thread

Similar Threads

Duplicate Worksheet but still reference it in new sheet

[SOLVED] How to get reference of duplicate value and set remove duplicate value for future entries

Duplicate value reference in the same column

Duplicate Cross Reference Help

Complicated set of calculations based on transaction IDs, transaction value, etc.

vlookup with duplicate reference numbers

how to duplicate a transaction register for my checkbook

Bookmarks

Bookmarks

Posting Permissions