?
Solved

Identify similar groups of numbers

Posted on 2013-02-04
7
Medium Priority
?
214 Views
Last Modified: 2013-02-04
I have sets of six numbers.

I have selected all possible groups of three from each set of six numbers.

I have to cross examine between sets in order to identify where there are similarities.

This means if I find a set of three numers (eg 15,60,75) in one set of six numbers (eg 15,20,50,60,65,75), I have to inspect all other sets of six numbers to try and find the same.

I have to do this for all possible sets of three numbers from all sets of six.

on the worksheet that I have attached, I have separated out the "sets of three" for visual purposes, as I also have to be able to look through this list like this.
I would like to colour the cells of similar "sets of three numbers" the same colour. But each set pair found after that would require a different colour to be applied.

Would it be possible (without altering the cells of the original sets of six) to go through all of these sets of three numbers and identify other identical sets ?
select-groups-of-3.xls
0
Comment
Question by:Europa MacDonald
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38851640
Looking at the data it seems that all sets of three have a duplicate since rows 3 and 4 are the same as 7 and 8. So if you run this code against that data all the sets of 3 will be highlighted, but maybe your real data is different.


Sub IdentifyDuplicates()

Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim lngRow As Long
Dim lngCol As Long
Dim DupeSets() As Sets
Dim strSet As String
Dim lngFind As Long
Dim bFound As Boolean

ReDim DupeSets(0)
lngLastRow = Range("A65536").End(xlUp).Row
lngLastColumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

For lngRow = 3 To lngLastRow
    If Cells(lngRow, 1) <> "" Then
        For lngCol = 9 To lngLastColumn Step 4
            strSet = Cells(lngRow, lngCol) & Cells(lngRow, lngCol + 1) & Cells(lngRow, lngCol + 2)
                    bFound = False

            For lngFind = 0 To UBound(DupeSets)
                If strSet = DupeSets(lngFind).strCells Then
                    bFound = True
                    Exit For
                End If
            Next
            If bFound Then
                Range(DupeSets(lngFind).strAddr).Interior.Color = vbYellow
                Range(DupeSets(lngFind).strAddr).Offset(0, 1).Interior.Color = vbYellow
                Range(DupeSets(lngFind).strAddr).Offset(0, 2).Interior.Color = vbYellow
                Range(Cells(lngRow, lngCol), Cells(lngRow, lngCol + 2)).Interior.Color = vbYellow
            Else
                DupeSets(UBound(DupeSets)).strCells = strSet
                DupeSets(UBound(DupeSets)).strAddr = Cells(lngRow, lngCol).Address
                ReDim Preserve DupeSets(UBound(DupeSets) + 1)
            End If

        Next
    End If
Next
End Sub

Open in new window

0
 

Author Comment

by:Europa MacDonald
ID: 38851654
thankyou I will try that. the data I gave you was a sample, purposely duplicated for demonstration. thanks again, checking now.
0
 

Author Comment

by:Europa MacDonald
ID: 38851674
I get Error

cant execute code in break mode

Dim DupeSets() As Sets is highlighted

can you help as I am new user thankyou
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Europa MacDonald
ID: 38851675
and

user defined type not defined
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38851708
Sorry, I left out some code when I posted.

I've attached a workbook that contains the code. Look in Module1. IdentifyDuplicates is a macro to which I have assigned ctrl+z, so if you enter ctrl+z from the sheet the macro will execute. You could also go into the code environment select the macro and press F5.
select-groups-of-3.xls
0
 

Author Closing Comment

by:Europa MacDonald
ID: 38851851
does the job perfectly
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 38851972
Thanks and I'm glad I was able to help.

Your accepting my answer as the solution put me over the 1,000,000 point mark! Thank you.

Marty - MVP 2009 to 2012
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question