ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Grouping records (https://www.excelbanter.com/excel-discussion-misc-queries/136964-grouping-records.html)

MEAD5432

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?

Please ask any questions to clarify.

Thanks

MyVeryOwnSelf

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,""" ")"



All times are GMT +1. The time now is 02:32 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com