Home > Forum Home > Presenting and Reporting Data > Help with analysis of customer records Share

Help with analysis of customer records

Excel Help for Help With Analysis Of Customer Records in Presenting and Reporting Data


Forum TopicPost Reply Login

Help With Analysis Of Customer Records

Rate this:
(3/5 from 1 vote)
ConfusedHi

I work for a charity, and want to analyse how many of our members rejoin each year. To do this I have created an Excel file with current and lapsed members' names in it, all 2412 of them - plus blank columns for each financial year, 2006-07, 07-08, 08-09, 09-10 and this year, 2010-11.

Another five worksheets have the names and dates they paid for that particular year, e.g. "06-07", where the first entry is Aberhart, Angela who joined on 1 May 2006.

I would like to put into sheet "customers" the date that she joined for each particular year, but can't figure out how to create the right formula.

Can someone assist please?

Jacqui
 Jacqui Knight
 Posted by on
 
Replies - Displaying 1 to 3 of 3Order Replies By: Most Recent | Chronological | Highest Rated
Oops
Rate this:
(3/5 from 1 vote)
In the customers sheet you need to include a VLOOKUP formula in each of the columns for match the member name to the date joined in the sheet for that particular year. You can use absolute (with $ signs) references to the look up ranges and offset and relative reference to the member name so that the formulas can be copied down the columns for all members.

In the VLOOKUP formula, use FALSE for the fourth option at the end to return exact matches (TRUE finds the closest match).  In this case you may also want to wrap the formula in an IF function to filter out results where a match is not found.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Thanks for that.
 Jacqui Knight
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Lookup formula for join date is a very good idea. Thanks for sharing.
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Help with analysis of customer records in the