Excel Pattern Matching?

[SrSeagull]

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]

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))

 

[liveinhope]

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]

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.