Got a tough one here...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am dealing with a "Preferred Customers List" from a Country Club that was inported from a text file. The spreadsheet contains a list of members, there member numbers, and the total amount of money they spent for the year. However, my problem lies in the fact that spouses and children are listed as seperate entries and I need an easy way to calculate the total amount spent by individuals that have the same member number. For example

A B C
Member Name Member # Total Sale
WILLIAMS, MR. HAROLD 100 $(6.50
TURNER, MR. LARRY 101 $3,845.47
TURNER, MRS. KATHY 101 $102.75
CAMPBELL, MRS. SANDRA 108 $357.70
HATFIELD, MR. ROBERT 112 $567.20

What I am wondering is if there is a way to compare all the numbers in column B and determine witch are the same then if they are the same calculate the corresponding values of column C to determine their total sales for the year. You may be asking why not just do it manually, but the Member List is over 3000 names long. I was hoping it was possible to calculate those values and possibly list them in a new column such as column D. There are other problems to be considered, such as I only need one value (sales total) returned for each member number, and I would need to know what member number corresponds to the sales total that is calculated. If anyone has a solution please help

Thank you!
 
Just use

=SUMIF(B:B,101,C:C)

and add new rows for each member num ber

YOu can get the (first with number) member with

=INDEX(A:A,MATCH(101,B:B,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

690cougar said:
I am dealing with a "Preferred Customers List" from a Country Club that
Click to expand...
was inported from a text file. The spreadsheet contains a list of members,
there member numbers, and the total amount of money they spent for the year.
However, my problem lies in the fact that spouses and children are listed as
seperate entries and I need an easy way to calculate the total amount spent
by individuals that have the same member number. For example:
A B C
Member Name Member # Total Sales
WILLIAMS, MR. HAROLD 100 $(6.50)
TURNER, MR. LARRY 101 $3,845.47
TURNER, MRS. KATHY 101 $102.75
CAMPBELL, MRS. SANDRA 108 $357.70
HATFIELD, MR. ROBERT 112 $567.20

What I am wondering is if there is a way to compare all the numbers in
Click to expand...
column B and determine witch are the same then if they are the same
calculate the corresponding values of column C to determine their total
sales for the year. You may be asking why not just do it manually, but the
Member List is over 3000 names long. I was hoping it was possible to
calculate those values and possibly list them in a new column such as column
D. There are other problems to be considered, such as I only need one value
(sales total) returned for each member number, and I would need to know what
member number corresponds to the sales total that is calculated. If anyone
has a solution please help!
 
I may be way off base, but could you use Sorting and Subtotaling to
accomplish what you want? Sort the list by member number, then use the
subtotal feature to total the sales for each number?

Judy Freed


690cougar said:
I am dealing with a "Preferred Customers List" from a Country Club that
Click to expand...
was inported from a text file. The spreadsheet contains a list of members,
there member numbers, and the total amount of money they spent for the year.
However, my problem lies in the fact that spouses and children are listed as
seperate entries and I need an easy way to calculate the total amount spent
by individuals that have the same member number. For example:
A B C
Member Name Member # Total Sales
WILLIAMS, MR. HAROLD 100 $(6.50)
TURNER, MR. LARRY 101 $3,845.47
TURNER, MRS. KATHY 101 $102.75
CAMPBELL, MRS. SANDRA 108 $357.70
HATFIELD, MR. ROBERT 112 $567.20

What I am wondering is if there is a way to compare all the numbers in
Click to expand...
column B and determine witch are the same then if they are the same
calculate the corresponding values of column C to determine their total
sales for the year. You may be asking why not just do it manually, but the
Member List is over 3000 names long. I was hoping it was possible to
calculate those values and possibly list them in a new column such as column
D. There are other problems to be considered, such as I only need one value
(sales total) returned for each member number, and I would need to know what
member number corresponds to the sales total that is calculated. If anyone
has a solution please help!
 
Back
Top