Grouping records
I have a list of clients and their associated decision makers. Each
decision maker has a unique ID.
I would like to group the decision makers to the client record where
the total sales is a sum of all individual sales like this:
Client Name Total Sales
Decision Maker1 Sales
Decision Maker2 Sales
Decision Maker3 Sales
I need to conduct analysis on the sheet containing the clients
(averages, new calculations, etc...) but only for the client. I don't
care about the decision maker info and it will only inflate my
numbers. Is there a way for me to write my formulas and copy them to
all records but ignore the decision makers?
Not sure of your requirements or the precise data layout, but maybe the csv
file below has some ideas you can borrow as a starting point.
The formulas in C2 and D1 were copied down for as many rows as needed.
You can hide column C.
You can use auto-filter on column D to isolate the (non-blank) rows of
interest.
<<<<<<<<<<<<<<<<<<<<<<<<< <<<<
Client1,=SUM(B2:B5),1,"=IF(C1=1,B1,"""")"
DM11,122,"=IF(B2="""",0,C1+1)","=IF(C2=1,B2,"""")"
DM12,5,"=IF(B3="""",0,C2+1)","=IF(C3=1,B3,"""")"
DM13,4,"=IF(B4="""",0,C3+1)","=IF(C4=1,B4,"""")"
DM14,41,"=IF(B5="""",0,C4+1)","=IF(C5=1,B5,"""")"
,,"=IF(B6="""",0,C5+1)","=IF(C6=1,B6,"""")"
Client2,=SUM(B8:B9),"=IF(B7="""",0,C6+1)","=IF(C7= 1,B7,"""")"
DM21,561,"=IF(B8="""",0,C7+1)","=IF(C8=1,B8,"""")"
DM22,11,"=IF(B9="""",0,C8+1)","=IF(C9=1,B9,"""")"
,,"=IF(B10="""",0,C9+1)","=IF(C10=1,B10,"""")"
Client3,=SUM(B12:B14),"=IF(B11="""",0,C10+1)","=IF (C11=1,B11,"""")"
DM31,336,"=IF(B12="""",0,C11+1)","=IF(C12=1,B12,"" "")"
DM32,11,"=IF(B13="""",0,C12+1)","=IF(C13=1,B13,""" ")"
DM33,87,"=IF(B14="""",0,C13+1)","=IF(C14=1,B14,""" ")"
|