Creating List of Values that Appear in Multiple Columns

[sskgintl]

I have 5 years of data on who has bought tickets for our annual raffle in columns by year. I am trying to determine to what degree the same people buy every year. To do so, I want to create the below unique lists. Each list should exclude anyone in the list above it and therefore divide all purchasers in the last 5 years into one of these categories.

Purchased Tickets Every Year
Purchased Tickets Past 4 Years
Purchased Tickets 4 of 5 Years
Purchased Tickets Past 3 Years
Purchased Tickets 3 of 5 Years
Purchased Tickets Past 2 Years
Purchased Tickets 2 of 5 Years
Purchased Tickets Last Year Only
Purchased Tickets Once

I have been using the below formula to pull all unique values from multiple lists and was trying to adapt it for this, but have not succeeded and am running out of time before my meeting with the head of the raffle committee.

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX(FY08.09,MATCH(0,COUNTIF(K$1:$T1,FY08.09),0)),INDEX(FY09.10,MATCH(0,COUNTIF(K$1:$T1,FY09.10),0))),INDEX(FY10.11,MATCH(0,COUNTIF(K$1:$T1,FY10.11),0))),INDEX(FY11.12,MATCH(0,COUNTIF(K$1:$T1,FY11.12),0))),INDEX(FY12.13,MATCH(0,COUNTIF(K$1:$T1,FY12.13),0))),"-")

Attached is the spreadsheet with names redacted to 4 letters. Any ideas would be much appreciated.

[mehmetcik]

Hi being in such a rush I would not use anything as complicated as that.

I'd create a unique list of all your customers.

then I would create a table of ones and zeros or maybe As and Bs would be simpler B meaning bought.

concatenate the table into one cell per customer [ Easier with A and B].

Then you can sort by your concatenated cells.

I see you have a unique list already so I'll start from there.

BRB

[mehmetcik]

Ok I filled in your columns for you.

Enjoy.

[sskgintl]

Thanks. It didn't really work, but I used the base concept of pulling the annual bought/didn't buy data (Y/N was wasier on my brain than A/B) into the full list of purchasers and some fairly simple conditionals to get the list of who bought in the various buying pattern categories as attached.

[peterjuhnke]

Is this more like what you are after?
Peter

[mehmetcik]

Hi

I'm glad you found a solution.

I am not convinced that some of your results are correct though.

When you check if the purchaser bought tickets in the last two years you check for NNNYY.

This is correct for people who only bought in the last two years, But what about YNNYY, NYNYY, NNYYY, YYNYY, NYYYY?

All these options are valid. the easiest way to test for them all is to test the last two characters for YY.