Excel question

Factual Questions
1

Can someone help me either form a concatenate or suggest an alternative for a mail merge? Here’s what I’m trying to do:

I have a membership list of all of our club’s members. There is a column for each of the last 10 years and whether or not the person is a member (there’s actually a number of different inputs, which you’ll see below). I’d like to do one of two things: either spit out a list of each year they’ve been a member, or spit out a phrase (i.e. “four of the last ten years”, “nine of the last ten years”, “each of the last ten years”). So, the data:



A          B       C       D       E       F       G       H       I       J       K
Member     2002    2003    2004    2005    2006    2007    2008    2009    2010    2011
John Doe   FREE    FREE    FREE    PAID    PD-EM                           PAID
Jane Doe                   PAID    PD-Gold PD-Gold PAID
Al Smith   PD-Blue PD-Blue Pd-Blue PD-Gold PD-Gold PD-Gold PD-Gold PD-Gold PD-Gold PD-Gold
Ed Jones                   Pd-Sps  PD-Sps  PD-Reg  PD-Reg          PD-Reg          PD-Reg

I have a feeling that a concatenate won’t work, as it will spit out a horrible string of years that will look horrible on a letter. Is there a way to make it spit it out in a list? So that when it comes out, it looks like this?

Any help is appreciated.

2

could you do a count of how many columns from B - J are non blank and then concatenate that with the “of the last ten years” string?

3

Got this working

=COUNTIF(B1:J1,"<>*")

Returns a number for the non blank cells which you can then use in your sentence.

4

Excellent, thanks. Anyone have a solution for creating a list? I suppose I can just go through the data and replace any text with the year of the column header - the data itself isn’t doing anything for anyone, and it’s imported to boot. I really don’t know how I’m going to get the info into a list rather than a string - a string is going to look really weird, especially for long time members (“thank you for being a member in 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010 and 2011!” looks really weird and cumbersome, to me).

5

OK, here goes.

I have set up the formulas with the first person in the list’s name in A2 and the year 2002 in B1.

Across column B, after the first person’s name, I have the listed data of whether they paid or not, just like in your example.

Below that, in rows 3, 4, 5, 6, and 7 I have created formulas that will drive this thing. You will need to enter the second person in the list in row 8, and then have 5 more dummy rows below that, etc. If you need formulas to just have the data on one tab, and then use another tab to bring over the data into the rows spaced out, that’s no problem, I can give it later.

Here are the formulas that will go into the rows, each one starting in Column B, and being dragged across under all of the years (using years 2002 through 2011, that means columns B through K, just like your example) - Row 4 is the only exception as it has 1 formula in cell B4, and a different formula for the rest of the row.

*These formulas are written into the first (leftmost cell) and then “dragged” across - that is, you grab the corner of the cell and literally drag it with your mouse cursor to populate the cells to its right.

Row 3: (cell B3 through K3): =IF(B2<>0,1,0)
Row 4: (in cell B4): =B3
(in cell C4 through K4): =IF(AND(B3=0,C3=1,1),1,0)
Row 5: (in cell B5 through K5): =SUM($B$4:B4)
Row 6: (in cell B6 through K6): =IF(OR(C5-B5=1,C5=0),1,0)
Row 7: (in cell B7 through K7): =SUM($B$6:B6)

After you do that, in columns M through R, we’re goign to set up the rest of the formulas.

In Row 1, we’re going to add some labels. We don’t to repeat these for addition customers, they are headers kind of like the years 2002-2011 and other stuff in Row 1.

In M1 through Q1, put the numbers 1, 2, 3, 4, and 5 - in order. So, put 1 in M1, 2 in N1, etc.

In cell R1, put the word Periods - this is just a label, but it’s handy.

In rows 3 through 7, we are going to have more formulas. These will also be “dragged” - in this case, they will all be entered into Column M and then dragged across through Column Q.

Row 3: =MATCH(M$1,$B5:$K5,0)
Row 4: =2001+M3
Row 5: =MATCH(M$1,$B7:$K7,0)
Row 6: =2001+M5
Row 7: =IF(M6=M4,"in "&M4,“from “&M4&”-”&M6)

Now, just 2 more formulas.

In cell R3: =COUNT(M3:Q3)

And the big one, in cell R7:

="Thank you so much for your previous support. According to our records, in the last ten years, you have been a paid member “&IF(R3=1,M7,IF(R3=2,M7&” and “&N7,IF(R3=3,M7&”, “&N7&”, and “&O7,IF(R3=4,M7&”, “&N7&”, “&O7&”, and “&P7,M7&”, “&N7&”, “&O7&”, “&P7&”, and "&Q7))))

Just put your data for the next customer in Row 8, and then select all of the cells from A3 through R7 and copy and paste that starting in A9. Rinse and repeat.

The formula in R7 (and similar) will return the following type of statement:

Thank you so much for your previous support. According to our records, in the last ten years, you have been a paid member from 2002-2005, from 2006-2008, and from 2009-2011

Hope this works for you.

6

The full set of data is several hundred rows, so this seems like it’s going to be burdensome. Any chance of getting it into a single cell that is at the end of each row? But thank you so much - that seems like a ton of work you just did. I’ll see if I can shoehorn it.

7

Just dealing with the first two columns, which can be easily extended to more columns:
=CONCATENATE(IF(B2<>"",B$1&CHAR(10),""),IF(C2<>"",C$1&CHAR(10),""))

The CHAR(10) puts a carriage return in there which should print properly in a text program. In Excel, it will show an odd looking character. I’m not sure if this is what you were looking for.

8

Dealing with several hundred rows is no problem at all. Just leave them as is on Sheet 1 (again, assumign that the first person’s name appears in cell A2).

Now, go to Sheet 2 - on the top Row - Row 1 - put in all of the labels as we talked about them before. That is, in A1 put Member, in B1, put 2002, etc. Also put in the labels I described for columns M through R (the 1,2,3,4,5 and Periods).

Then put this formula into cell A2 on Sheet 2:

=OFFSET(Sheet1!A$1,ROUNDDOWN((ROW(Sheet1!A2)+4)/6,0),0)

Go ahead and drag that formula across through Column K. It should populate those cells with all of the data for your first customer.

Now, go ahead and fill out rows 3 through 7 like described above, including Columns M through R. This should replicate what was described above entirely and give you he tag line for your first customer.

Now, just select cells A2 through R7, and copy and paste into cell A8. This will fill out all of the info for your second customer (the formula included above basically just moves down 1 row in Sheet1 for every 6 rows in Sheet2).

Once you’ve done that, you can just keep copying and pasting. But now you can copy and paste 2 customers worth of rows. Then copy and paste 4 customers. Then 8, etc. So, it’s not like you have to copy and paste several hundred times - 10 or so shoudl do it.

Let me know if that works.

9

Nailed it. I pushed it across all the columns - that works absolutely perfectly.