A little direction...

[brandonrlz]

First...best find!! The site and video's are pure awesome and help provide me with a lot of refresher courses as well as new tactics so thanks to everyone!!

Here's my dilemma...I did some searching but couldn't really find anything that really targets what I'm trying to do, so any direction on how to approach this would be fantastic!!

I've got three columns in a spreadsheet that I want to slim down to one level and use some conditional formatting to build a dashboard out of it. That part won't be to difficult to do; however, I'm having a little bit of a brain fart in how to accomplish this.

Column 1 - Column 2 - Column 3
Bob - 1 - 1
Bob - 2 - 2
Bob - 3 - 3
Bill - 4 - 4
Bill - 5 - 5
Bill - 6 - 6
Steve - 7 - 7
Steve - 8 - 8
Steve - 9 - 9

What I want to do is take each line item and combine/calculate it into one line item.

Bob - 12
Bill - 30
Steve - 48

I've tried using "sumif" to do this; however, it's only capturing the first column of data.

=SUMIF(A1:A9,"Bob",B1:C9)

I'm wondering if doing an array would work better in this situation, or if I should be taking a different approach entirely.

Thanks for the help and insight on this.

 

[njimack]

Excel Workbook
	A	B	C	D	E	F
1
2	Bob	1	1		BoB	12
3	Bob	2	2		Bill	30
4	Bob	3	3		Steve	48
5	Bill	4	4
6	Bill	5	5
7	Bill	6	6
8	Steve	7	7
9	Steve	8	8
10	Steve	9	9
Sheet1

 

[snoopyhr]

or more dynamic (that is if you assign a name to the range)

This way you do not need to retype your list and risk of having duplication. Just a suggestion.

Excel Workbook
	C	D	E	F	G	H
6	*	*	*	*	Unique L.	Totals
7	Bob	1	1	*	Bill	30
8	Bob	2	2	*	Bob	12
9	Bob	3	3	*	Steve	48
10	Bill	4	4	*	*	*
11	Bill	5	5	*	*	*
12	Bill	6	6	*	*	*
13	Steve	7	7	*	*	*
14	Steve	8	8	*	*	*
15	Steve	9	9	*	*	*
Sheet1

 

[snoopyhr]

Just noticed the "0" was missing in the mactch formula for match type. Though it works but better have it in the correct way.

{=INDEX($C$7:$C$15,MATCH(0,COUNTIF($C$7:$C$15,"<"&$C$7:$C$15)-SUM(COUNTIF($C$7:$C$15,$G$6:G6)),))}

should be :
{=INDEX($C$7:$C$15,MATCH(0,COUNTIF($C$7:$C$15,"<"&$C$7:$C$15)-SUM(COUNTIF($C$7:$C$15,$G$6:G6)),0))}

 

[brandonrlz]

Thanks a ton for the info, I really appreciate it! A follow-up question though is, are you not able to pass number values with some of the formula's? Here's what I have:

Excel Workbook
	A	B	C
1	Customer	Value 1	Value 2
2	Bob	4,548,778	3,539,052
3	Bob	4,548,778	3,539,052
4	Bob	4,548,820	3,539,052
5	Bob	315,332	257,639
6	888	694,936	7,051
7	888	74,631	6,811
8	888	803,193	284,351
9	Bill	2,510,404	0
10	Bill	410	0
11	Bill	203	0
12	Bill	4,664,963	29,394
13
14	Cell Range	Name
15	A2:A12	customer
16	B2:C12	custCMB
Sheet1

Excel Workbook
	F
2	Formula
3	{=INDEX(customer,MATCH(0,COUNTIF(customer,"
4	=SUMPRODUCT((customer=$A6)*(custCMB))
Sheet1

Excel Workbook
	A	B
18	Customer	Combined Value
19	Bob	24836503
20	Bill	43503567
Sheet1

The formula completely skips over the "888" values as if they aren't even there. Also, with these types of calculations am I going to run into any sort of "calculation process"? I ask because the data extract will be broken up into months with each month having roughly 2k line items.

 

[snoopyhr]

I think this one can handle whatever is throw at it.

Excel Workbook
	C	D	E	F	G	H
5	*	*	*	*	Count of uniques	*
6	*	*	*	*	5	*
7	Customers	Value 1	Value 2	*	Customers	Total
8	Bob	4,548,778	3,539,052	*	Bob	24,836,503.00
9	Bob	4,548,778	3,539,052	*	888	* 1,870,973.00
10	Bob	4,548,820	3,539,052	*	Bill	* 2,510,814.00
11	Bob	315,332	257,639	*	Anything	* * * * * *203.00
12	888	694,936	7,051	*	45toy	* 4,694,357.00
13	888	74,631	6,811	*	*	*
14	888	803,193	284,351	*	*	*
15	Bill	2,510,404	0	*	*	*
16	Bill	410	0	*	*	*
17	Anything	203	0	*	*	*
18	45toy	4,664,963	29,394	*	*	*
Sheet1

this has high overhead my trick is after the extract I copy and paste special the entire sheet with exception of top row (so I can reproduce it ) and then send it out to others for review or whatever purpose they need it for.

 

[brandonrlz]

Hey thanks for the formula it helped a ton!! I'm running into a problem though when I try to use name ranges for the formulas...the values return a no data, but I can't seem to figure out what I'm missing in the syntax.

Excel Workbook
	C	D	E
7	Customers	Value 1	Value 2
8	Bob	4548778	3539052
9	Bob	4548778	3539052
10	Bob	4548820	3539052
11	Bob	315332	257639
12	888	694936	7051
13	888	74631	6811
14	888	803193	284351
15	Bill	2510404	0
16	Bill	410	0
17	Anything	203	0
18	45toy	4664963	29394
19
20		custJan2011
21		custCMBJan2011
22
23	A1	{=SUMPRODUCT((custJan2011"")/COUNTIF(custJan2011,custJan2011&""))}
24	=IF(A6="","",SUMPRODUCT((custJan2011=A6)*(custCMBJan2011)))
25	{=IF(ROWS(custJan2011)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW($A$6)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A6))))}
Sheet3

 

[snoopyhr]

{=IF(ROWS(custJan2011)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW($A$6)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A6))))}

ROWS(custJan2011) <--- You can not use range for this part of the formula, it chaeck to see if the counts of row are less more than the count of unique names. in another words it control when to stop calculation ( processing the formula)

Hence the working formula will be:
=IF(ROWS($E2:E2)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW($A$6)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($E$2:E2))))

Provided your unique list STARTS on A6 on my table as below it starts E2

Excel Workbook
	A	B	C	D	E	F	G	H
1	5				Customers	Value 1	Value 2	Value 1 & 2
2	*	Customers	Value 1	Value 2	Bob	13,961,708.00	10,874,795.00	24836503
3	*	Bob	4548778	3539052	888	* 1,572,760.00	* * 298,213.00	1870973
4	*	Bob	4548778	3539052	Bill	* 2,510,814.00	* * * * * * * * - *	2510814
5	*	Bob	4548820	3539052	Anything	* * * * * 203.00	* * * * * * * * - *	203
6	*	Bob	315332	257639	45toy	* 4,664,963.00	* * * 29,394.00	4694357
7	*	888	694936	7051	*	*	*	*
8	*	888	74631	6811	*	*	*	*
9	*	888	803193	284351	*	*	*	*
10	*	Bill	2510404	0	*	*	*	*
11	*	Bill	410	0	*	*	*	*
12	*	Anything	203	0	*	*	*	*
13	*	45toy	4664963	29394	*	*	*	*
14	*	*	*	*	*	*	*	*
15	*	*	custJan2011	*	*	*	*	*
16	*	*	custCMBJan2011	*	*	*	*	*
Sheet2

#VALUE!

</td></tr></table></td></tr></table>

 

[brandonrlz]

Ah...that's right. Makes sense. Thanks for pointing that out.

One thing I'm noticing in some instances now that I've built out more data is...I'm getting values that are being duplicated. As you can see its working on a large amount of data and doing exactly what I expected; however, it's spitting back instances like this:

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:left; ">72</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td colspan="2" style="text-align:center; ">Jan-11</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:left; ">Customer</td><td> Combined </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td> <td style="text-align:left; ">Customer A</td> <td style="text-align:right; "> 24,836,503 </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:left; ">888</td><td style="text-align:right; "> 102,911,262 </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td> <td style="text-align:left; ">Customer B</td> <td style="text-align:right; "> 43,503,567 </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td> <td style="text-align:left; ">Customer C</td> <td style="text-align:right; ">1,453</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td> <td style="text-align:left; ">Customer D</td> <td style="text-align:right; "> 914,409 </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td> <td style="text-align:left; ">Customer D</td> <td style="text-align:right; "> 914,409 </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td> <td style="text-align:left; ">Customer E</td> <td style="text-align:right; ">203</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td> <td style="text-align:left; ">Customer F</td> <td style="text-align:right; "> 584 </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td> <td style="text-align:left; ">Customer G</td> <td style="text-align:right; "> 36,213,785 </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td> <td style="text-align:left; ">Customer G</td> <td style="text-align:right; "> 36,213,785 </td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>A1</td><td>=SUMPRODUCT((custJan2011<>"")/COUNTIF(custJan2011,custJan2011&""))</td></tr><tr><td>A6</td><td>{=IF(ROWS($A6:A6)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A6)+1),ROWS($A6:A6))))}</td></tr><tr><td>B6</td><td>=IF($A6="","",SUMPRODUCT((custJan2011=$A6)*(custCMBJan2011)))</td></tr><tr><td>A7</td><td>{=IF(ROWS($A$6:A7)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A7))))}</td></tr><tr><td>B7</td><td>=IF($A7="","",SUMPRODUCT((custJan2011=$A7)*(custCMBJan2011)))</td></tr><tr><td>A8</td><td>{=IF(ROWS($A$6:A8)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A8))))}</td></tr><tr><td>B8</td><td>=IF($A8="","",SUMPRODUCT((custJan2011=$A8)*(custCMBJan2011)))</td></tr><tr><td>A9</td><td>{=IF(ROWS($A$6:A9)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A9))))}</td></tr><tr><td>B9</td><td>=IF($A9="","",SUMPRODUCT((custJan2011=$A9)*(custCMBJan2011)))</td></tr><tr><td>A10</td><td>{=IF(ROWS($A$6:A10)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A10))))}</td></tr><tr><td>B10</td><td>=IF($A10="","",SUMPRODUCT((custJan2011=$A10)*(custCMBJan2011)))</td></tr><tr><td>A11</td><td>{=IF(ROWS($A$6:A11)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A11))))}</td></tr><tr><td>B11</td><td>=IF($A11="","",SUMPRODUCT((custJan2011=$A11)*(custCMBJan2011)))</td></tr><tr><td>A12</td><td>{=IF(ROWS($A$6:A12)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A12))))}</td></tr><tr><td>B12</td><td>=IF($A12="","",SUMPRODUCT((custJan2011=$A12)*(custCMBJan2011)))</td></tr><tr><td>A13</td><td>{=IF(ROWS($A$6:A13)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A13))))}</td></tr><tr><td>B13</td><td>=IF($A13="","",SUMPRODUCT((custJan2011=$A13)*(custCMBJan2011)))</td></tr><tr><td>A14</td><td>{=IF(ROWS($A$6:A14)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A14))))}</td></tr><tr><td>B14</td><td>=IF($A14="","",SUMPRODUCT((custJan2011=$A14)*(custCMBJan2011)))</td></tr><tr><td>A15</td><td>{=IF(ROWS($A$6:A15)>$A$1,"",INDEX(custJan2011,SMALL(IF(FREQUENCY(IF(custJan2011<>"",MATCH(custJan2011&"",custJan2011&"",0)),ROW(custJan2011)-ROW('PEPM Extract 2011'!$A$9)+1),ROW(custJan2011)-ROW($A$6)+1),ROWS($A$6:A15))))}</td></tr><tr><td>B15</td><td>=IF($A15="","",SUMPRODUCT((custJan2011=$A15)*(custCMBJan2011)))</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[snoopyhr]

Post your ranges, there seem to be some inconsistency. I ran the formula with 3000 line and I received 200 unique name. No duplicate was found in the unique list of names.