Creating LARGE list from divisions and sub divisions of Data

[Lres81715]

Hello Mr Excel guru's,

I'm attempting to create a top 20 Client list only I'm running into a problem. Our client list has normal clients and companies with divisions. My task is to create a top 20 list that combines all the Divisions as 1 client... and then creating list that changes on the fly as new orders are taken.
A Pivot Table, (while Ideal in most circumstances) won't work as you need to manually refresh AND new columns/rows are added all the time.

Here is the Monthly excel file of the data in question. The daily version is too complex to post.
https://app.box.com/s/walouc7if71b6g6kc7wk62gohjg0lij5

In a simplified version,

[TABLE="width: 445"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Client Name[/TD]
[TD]Parent Company[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]AAA Company[/TD]
[TD]AAA Company[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]ABC Company[/TD]
[TD]ABC Company[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ACC Company[/TD]
[TD]ACC Company[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Alpha House Corp[/TD]
[TD]Alpha House Corp[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]American Corp[/TD]
[TD]American Corp[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]American Finance[/TD]
[TD]American Finance[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Anchor Corp[/TD]
[TD]Anchor Corp[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Bank of America default[/TD]
[TD]Bank of America[/TD]
[TD="align: right"]5675[/TD]
[/TR]
[TR]
[TD]Bank of America Div 1[/TD]
[TD]Bank of America[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Bank of America Div 2[/TD]
[TD]Bank of America[/TD]
[TD="align: right"]9967[/TD]
[/TR]
[TR]
[TD]Bank of America Div 3[/TD]
[TD]Bank of America[/TD]
[TD="align: right"]2050[/TD]
[/TR]
[TR]
[TD]BofA Div 4[/TD]
[TD]Bank of America[/TD]
[TD="align: right"]4799[/TD]
[/TR]
[TR]
[TD]Better Corp[/TD]
[TD]Better Corp[/TD]
[TD="align: right"]115[/TD]
[/TR]
[TR]
[TD]Big Company Div 1[/TD]
[TD]Big Company[/TD]
[TD="align: right"]12320[/TD]
[/TR]
[TR]
[TD]BigComp Div 2[/TD]
[TD]Big Company[/TD]
[TD="align: right"]461[/TD]
[/TR]
[TR]
[TD]Big Company Div 3[/TD]
[TD]Big Company[/TD]
[TD="align: right"]1326[/TD]
[/TR]
[TR]
[TD]Corp Company[/TD]
[TD]Corp Company[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Cost Efficient Company[/TD]
[TD]Cost Efficient Company[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Costco[/TD]
[TD]Costco[/TD]
[TD="align: right"]4585[/TD]
[/TR]
[TR]
[TD]Custom Corp[/TD]
[TD]Custom Corp[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Dalek incorp[/TD]
[TD]Dalek incorp[/TD]
[TD="align: right"]1519[/TD]
[/TR]
[TR]
[TD]Doctor ? Comp[/TD]
[TD]Doctor ? Comp[/TD]
[TD="align: right"]116[/TD]
[/TR]
[TR]
[TD]Eco friendly Corp[/TD]
[TD]Eco friendly Corp[/TD]
[TD="align: right"]659[/TD]
[/TR]
[TR]
[TD]Ecos, LLC[/TD]
[TD]Ecos, LLC[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Friendly Corp[/TD]
[TD]Friendly Corp[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


=LARGE(C2:C26,ROWS($A$28:A28))
Doesn't work because the divisions are being counted, not the Parent company name.

I know there is an easy way to accomplish this task involving sumifs and arrays but I can't figure it out right now.

Thanks for any help

 

[shg]

Like this?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

1​

[/td][td]

Client Name​

[/td][td]

Parent Company​

[/td][td]

Total​

[/td][td]

​

[/td][td]

​

[/td][td]

Distinct Parents​

[/td][td]

Total​

[/td][td]

Rank​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

2​

[/td][td]AAA Company[/td][td]AAA Company[/td][td]

33​

[/td][td][/td][td][/td][td]AAA Company[/td][td]

33​

[/td][td]

9​

[/td][td][/td][td]G2: =SUMIF($B$2:$B$26, F2, $C$2:$C$26)[/td][/tr]
[tr][td]

3​

[/td][td]ABC Company[/td][td]ABC Company[/td][td]

3​

[/td][td][/td][td][/td][td]ABC Company[/td][td]

3​

[/td][td]

11​

[/td][td][/td][td]H2: =RANK(G2, $G$2:$G$20)[/td][/tr]
[tr][td]

4​

[/td][td]ACC Company[/td][td]ACC Company[/td][td]

2​

[/td][td][/td][td][/td][td]ACC Company[/td][td]

2​

[/td][td]

13​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Alpha House Corp[/td][td]Alpha House Corp[/td][td]

0​

[/td][td][/td][td][/td][td]Alpha House Corp[/td][td]

0​

[/td][td]

19​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]American Corp[/td][td]American Corp[/td][td]

4​

[/td][td][/td][td][/td][td]American Corp[/td][td]

4​

[/td][td]

10​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]American Finance[/td][td]American Finance[/td][td]

1​

[/td][td][/td][td][/td][td]American Finance[/td][td]

1​

[/td][td]

16​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Anchor Corp[/td][td]Anchor Corp[/td][td]

35​

[/td][td][/td][td][/td][td]Anchor Corp[/td][td]

35​

[/td][td]

8​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]Bank of America default[/td][td]Bank of America[/td][td]

5675​

[/td][td][/td][td][/td][td]Bank of America[/td][td]

22562​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]Bank of America Div 1[/td][td]Bank of America[/td][td]

71​

[/td][td][/td][td][/td][td]Better Corp[/td][td]

115​

[/td][td]

7​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]Bank of America Div 2[/td][td]Bank of America[/td][td]

9967​

[/td][td][/td][td][/td][td]Big Company[/td][td]

14107​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]Bank of America Div 3[/td][td]Bank of America[/td][td]

2050​

[/td][td][/td][td][/td][td]Corp Company[/td][td]

2​

[/td][td]

13​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]BofA Div 4[/td][td]Bank of America[/td][td]

4799​

[/td][td][/td][td][/td][td]Cost Efficient Company[/td][td]

2​

[/td][td]

13​

[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]Better Corp[/td][td]Better Corp[/td][td]

115​

[/td][td][/td][td][/td][td]Costco[/td][td]

4585​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]Big Company Div 1[/td][td]Big Company[/td][td]

12320​

[/td][td][/td][td][/td][td]Custom Corp[/td][td]

1​

[/td][td]

16​

[/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]BigComp Div 2[/td][td]Big Company[/td][td]

461​

[/td][td][/td][td][/td][td]Dalek incorp[/td][td]

1519​

[/td][td]

4​

[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]Big Company Div 3[/td][td]Big Company[/td][td]

1326​

[/td][td][/td][td][/td][td]Doctor ? Comp[/td][td]

116​

[/td][td]

6​

[/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]Corp Company[/td][td]Corp Company[/td][td]

2​

[/td][td][/td][td][/td][td]Eco friendly Corp[/td][td]

659​

[/td][td]

5​

[/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]Cost Efficient Company[/td][td]Cost Efficient Company[/td][td]

2​

[/td][td][/td][td][/td][td]Ecos, LLC[/td][td]

3​

[/td][td]

11​

[/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]Costco[/td][td]Costco[/td][td]

4585​

[/td][td][/td][td][/td][td]Friendly Corp[/td][td]

1​

[/td][td]

16​

[/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]Custom Corp[/td][td]Custom Corp[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td]Dalek incorp[/td][td]Dalek incorp[/td][td]

1519​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td]Doctor ? Comp[/td][td]Doctor ? Comp[/td][td]

116​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

24​

[/td][td]Eco friendly Corp[/td][td]Eco friendly Corp[/td][td]

659​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

25​

[/td][td]Ecos, LLC[/td][td]Ecos, LLC[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

26​

[/td][td]Friendly Corp[/td][td]Friendly Corp[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Lres81715]

Ironically, we are doing something similar right now I don't think it will work for our needs as is.

Reason 1) Data source overwrites itself with new figures/numbers/stats constantly. Not that big of a deal but a pain for pivot table updates.
Reason 2) The idea of a secondary sheet that feeds off the Data sheet "could" work but would require frequent manual updating.
The Parent client list is updated frequently with new divisions added and subtracted constantly. What makes matters worse is that some clients SWITCH Parent companies. Example: So-and-So Mortgage Inc will be affiliated parent company Big Company for 5 months. Then Month 6, he will now be affiliated with Bank of America. Yadda Yadda its complicated and a nightmare trying to track actual numbers without having to revert to manual data calculations and forensic accounting.

So my question is: "Distinct Parents column" how is this updated if not manually? Can it be done automatically?

 

[shg]

In F2, array-entered and then copied down,

=IFERROR(INDEX($B$2:$B$100, MATCH(0, COUNTIF($F$1:F1, $B$2:$B$100), 0)), "")

 

[Lres81715]

Ok, yup that looks like it will solve the problem. =) Thanks Shg

*Edit* wow does it slow up my worksheet. ahahah but it's worth it.

 

[shg]

You're welcome.

wow does it slow up my worksheet

No surprise -- it does a lot of redundant calculation.

 

[Lres81715]

shg,

It's been about a month and I'm allotted more time on this project and I'm running into massive slowdown issues from the users who have slower computers. This report is d@mn near unreadable for them. Can you direct me in the right direction to achieve =IFERROR(INDEX($B$2:$B$100, MATCH(0, COUNTIF($F$1:F1, $B$2:$B$100), 0)), "") only... do it through VBA?
Because of the nature of the report, I have to use this 4 more times using different raw data sheets which is causing the massive slowdown.

 

[shg]

Via macro, you could copy, sort, remove duplicates, then just add the two formulas in the rightmost columns.