Excel Pattern Matching?

SrSeagull

New Member
Joined
Feb 6, 2017
Messages
1
Is there a way to match patterns of characteristics of data? Assume I have 10,000 rows of data and each is made up for 5 columns. The first column is a unique customer number and the remaining four columns each have either a 1 or 0. There are 15 unique patterns of 1 or 0 in the data set (e.g. 1,1,1,1 or 1,1,0,1, or 1,0,1,1 or 0,1,0,0 etc...). For each row of data I am trying to match its pattern against the 15 possible combinations and determine which combination it is? Any suggestions?
DATAPossibleOutcomes
Year 1Year 2Year 3Year 4Formula?Year 1Year 2Year 3Year 4
Customer11111A
A1111
Customer21011EB1110
Customer31111C1101
Customer41111D1100
Customer51011E1011
Customer61010F1010
Customer71001G1001
Customer81010H1000
Customer91010I0111
Customer101000J0110
Customer110111K0101
Customer120110L0100
Customer130101M0011
Customer140101N0010
Customer150011O0001
Customer160010
Customer170001

<tbody>
</tbody>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
Welcome to the forum.

There are ways to use INDEX/MATCH on multiple columns, but seeing as how your patterns are just counting in binary, this should be more efficient:

G3: =CHAR(80-(C4*8+D4*4+E4*2+F4))


This does assume you want the actual letters A, B, etc. If you want to search down column I for the actual name, you could use:

=INDEX($I$3:$I$17,16-(C4*8+D4*4+E4*2+F4))
 
Last edited:

liveinhope

Well-known Member
Joined
Dec 16, 2013
Messages
857
love your index solution Eric W .. so elegant!

just one point you may like to consider SrSeagull . you sated that your current data has 15 unique patterns ..


It would be a good idea to also cater for the 16th possibility ie all zeroes .. While none of your current customers have all zeroes this colud happen in future and if it did then the current formula will return N/A
(and even if it never happens no harm will be done by considering the possibility )
this would makes Eric W's formula =INDEX($I$3:$I$18,16-(C4*8+D4*4+E4*2+F4))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
Excellent point, liveinhope. It's always a good idea to try to allow for as many possibilities as possible, especially if all it takes is adding an extra line saying "Invalid combination". Along those lines, it's also worth noting that if there is some non-numeric data in columns C:F, the formula will return a #VALUE error. That might be adequate for the OP, but if not, using IFERROR can handle that.
 

Forum statistics

Threads
1,082,273
Messages
5,364,168
Members
400,785
Latest member
Mahar92

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top