# Excel Pattern Matching?

#### SrSeagull

##### New Member
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?
 DATA Possible Outcomes Year 1 Year 2 Year 3 Year 4 Formula? Year 1 Year 2 Year 3 Year 4 Customer 1 1 1 1 1 A A 1 1 1 1 Customer 2 1 0 1 1 E B 1 1 1 0 Customer 3 1 1 1 1 C 1 1 0 1 Customer 4 1 1 1 1 D 1 1 0 0 Customer 5 1 0 1 1 E 1 0 1 1 Customer 6 1 0 1 0 F 1 0 1 0 Customer 7 1 0 0 1 G 1 0 0 1 Customer 8 1 0 1 0 H 1 0 0 0 Customer 9 1 0 1 0 I 0 1 1 1 Customer 10 1 0 0 0 J 0 1 1 0 Customer 11 0 1 1 1 K 0 1 0 1 Customer 12 0 1 1 0 L 0 1 0 0 Customer 13 0 1 0 1 M 0 0 1 1 Customer 14 0 1 0 1 N 0 0 1 0 Customer 15 0 0 1 1 O 0 0 0 1 Customer 16 0 0 1 0 Customer 17 0 0 0 1

<tbody>
</tbody>

#### Eric W

##### MrExcel MVP
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
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
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.

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

### 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...