From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.6 (2021-04-09) on ip-172-31-74-118.ec2.internal X-Spam-Level: X-Spam-Status: No, score=0.8 required=3.0 tests=BAYES_50,FREEMAIL_FROM autolearn=ham autolearn_force=no version=3.4.6 X-Received: by 2002:a37:bd05:: with SMTP id n5mr11302562qkf.293.1640596910990; Mon, 27 Dec 2021 01:21:50 -0800 (PST) X-Received: by 2002:a05:6902:703:: with SMTP id k3mr19954981ybt.31.1640596910735; Mon, 27 Dec 2021 01:21:50 -0800 (PST) Path: eternal-september.org!reader02.eternal-september.org!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail Newsgroups: comp.lang.ada Date: Mon, 27 Dec 2021 01:21:50 -0800 (PST) Injection-Info: google-groups.googlegroups.com; posting-host=213.166.55.173; posting-account=sDyr7QoAAAA7hiaifqt-gaKY2K7OZ8RQ NNTP-Posting-Host: 213.166.55.173 User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <7bede061-4b0f-4029-beb1-1056637e57d6n@googlegroups.com> Subject: Some advice required [OT] From: Laurent Injection-Date: Mon, 27 Dec 2021 09:21:50 +0000 Content-Type: text/plain; charset="UTF-8" Xref: reader02.eternal-september.org comp.lang.ada:63273 List-Id: Hi all My problem is not directly related to Ada but on how to solve it in general. Also writing via the web interface of google groups :( I have to do statistics on the results of antimicrobial susceptibility testings. I have to keep only one strain/patient and the most resistant one. Until now I have been doing it manually by staring for hours at Excel sheets. I am trying to get it automated but I don't know how to solve my problem. I have tried calculating a checksum from the results but I have cases which are unclear/collide. The result for a strain is one row, the results are in columns. I treat the results in blocks of 3. S has a value of 1, I =2 and R=3, empty cells = 0 without weight SRS (1+3+1) and RSS (3+1+1) or SSR (1+1+3) give both 5 I would have to keep the 3 because they are different. I thought that weighting the position would solve the collisions but nope. The first cell has a value of 1, 2nd of 2 and 3rd of 3. S has a value of 1, I =2 and R=3, empty cells = 0 with weight RRS (1*3+2*3+3*1) and SSR (1*1 + 2*1+3*3) give both 12 Is there a better way doing this? What I have so far as VBA code: Public Function Test_Checksum(rng_Range As Range) As String Dim rng_Cell As Range Dim int_Counter As Integer Dim str_Result As String Dim i As Long int_Counter = 1 For Each rng_Cell In rng_Range If rng_Cell.Value = "S" Then i = i + 1 * int_Counter ElseIf rng_Cell.Value = "I" Then i = i + 2 * int_Counter ElseIf rng_Cell.Value = "R" Then i = i + 3 * int_Counter Else --empty cell i = i + 0 End If If int_Counter = 3 Then int_Counter = 0 If i < 9 Then str_Result = str_Result & "0" & CStr(i) Else str_Result = str_Result & CStr(i) End If i = 0 End If int_Counter = int_Counter + 1 Next rng_Cell --if the loop terminates but i <> 3 If i < 9 Then str_Result = str_Result & "0" & CStr(i) Else str_Result = str_Result & CStr(i) End If Test_Checksum = str_Result End Function Thank you very much Kind regards Laurent