Array Formula Using the Large Argument

Jason --

The long and short answer is No.

> For example Column A contains four salespeople and I want to see what their individual top 10 sales were for the month (column B).

> I hope this is understandable, can anyone help?

I guess you have another column with sales.

It would be more easier if we had some data. If you'd like to post 15 rows of data, you can do the following:

Activate an empty cell, type =, select 15 rows of your sales data, go the Formula Bar, hit F9, copy what you see between the braces, and past it in the follow up, along with the array formula that you are using.

Aladin

Aladin

Sorry I didn't give you enough (or correct) info, I want to determine the top 10 clients in each category. The data ranges are named "Clients", "Category" and "Data".

The data is below

{"Griffith High School","Leisure Group",44;"Grove Haulage","Business Individual",22;"Grundy Travel Artarm","Business Individual",14;"GWM Adviser Services","Business Individual",55;"Harvey World Travel","Business Individual",81;"Health World Ltd","Business Group",16;"Hearing Package","Leisure Individual",37;"Hifert Pty Ltd","Business Individual",5;"Honeymoon Pkg","Leisure Individual",18;"House of Travel","Leisure Individual",98;"Housing Industry Ass","Business Individual",24;"Hughes Travel","Business Individual",32;"Huyck Australia","Business Group",48;"Hydro-Chem Pty Ltd","Business Individual",31;"Ibis Hotel Melbourne","Business Individual",26;"Ideal Electrical","Business Individual",14;"Ideal Electrical - Conf","Business Group",18}

Also, can you assist with a formula that will match the return number of each category with the client name and if there is a duplicate number searches for the second client name.

Thanks

Jason

Aladin,

I'd be very interested in knowing if there is a way to paste the data (between braces) back into a workbook.

Thank you,

Russell

OK, Jason, here we go...

I'll assume that A2:C18 houses your sample data, A1 the label Clients, B1 Category, and C1 Data. I've used the following range names:

A2:A18 ==> CLIENTS (your Clients)
B2:B18 ==> CATEGORIES (your Category)
C2:C18 ==> DATA (your Data)

[ Naming in plural and in all capitals is just an habit with me :) ]

In E1:H1 enter the labels: {"Leisure Group","Business Individual","Business Group","Leisure Individual"}

which are the categories in the sample, created using Adv Filter and by transposing the results -- also can be done manually if you prefer.

In E2 array-enter: =IF(COUNTIF(CATEGORIES,E$1)>=ROW()-1,INDEX(CLIENTS,MATCH(SUMPRODUCT(LARGE(((CATEGORIES=E$1)*DATA),ROW()-1)+0)&E$1,DATA&CATEGORIES,0)),"")

Copy this first across than down up to 10 rows.

I tried to avoid setting up an array-formula, but that's not a good idea. If you prefer, you can also use the following "non-array" formula:

=IF(COUNTIF(CATEGORIES,E$1)>=ROW()-1,INDEX(CLIENTS,SUMPRODUCT(MATCH(SUMPRODUCT(LARGE(((CATEGORIES=E$1)*DATA),ROW()-1)+0)&E$1,DATA&CATEGORIES,0))),"")

which uses an additional SUMPRODUCT.

I guess you know how to enter an array-formula: CONTROL+SHIFT+ENTER instead of just ENTER.

Caveat. I didn't check the working sufficiently. But, you should. :)

Aladin

=============

Hi Russell --

Happy New Year.

> I'd be very interested in knowing if there is a way to paste the data (between braces) back into a workbook.

Yes, there is. Copy the whole thing (e.g., Jason's sample data), activate an (empty) cell, type =, and paste it in the Formula Bar. The next thing is to know how big the chunk is. The number of semi-colons tells you how many rows you need and the number of items between two semi-colons tells you how many rows . After establishing that, activate the cell where you pasted the chunk and select required number of columns and rows. Then hit CONTROL+SHIFT+ENTER. That's all.

Aladin

=========

Happy New Year to you to!. And thanks again for teaching me something new! I thought it was along those lines, but I forgot the = sign (silly me).

-Russell

Cheers mate

Aladin,

Thanks mate works a treat.
You're a Guru!

Jas,

Happy New Year to you as well...

I'm a little lazier than Aladin, i don't like counting semicolons, colons, backslash (Like they appear in my spanish version), and other characters, so here's what i do to "copy back" to the worksheet.

Follow same procedure, select and empty cell, paste the information beggining with a =. Then, select a big range, and hit Control Shift Enter, i don't count, i know where the "real" range is by looking at all the #REF's around, for example, if the "real data" is a 10x4 matrix, and i selected a 15x15 maxtrix, i'll get the first 10x4 with data and the rest with errors. Then, i can copy as values to remove the unneeded range.

Hope this is clear...

Juan Pablo G. Happy New Year to you to!. And thanks again for teaching me something new! I thought it was along those lines, but I forgot the = sign (silly me). -Russell : Hi Russell -- : Happy New Year. : > I'd be very interested in knowing if there is a way to paste the data (between braces) back into a workbook. : Yes, there is. Copy the whole thing (e.g., Jason's sample data), activate an (empty) cell, type =, and paste it in the Formula Bar. The next thing is to know how big the chunk is. The number of semi-colons tells you how many rows you need and the number of items between two semi-colons tells you how many rows . After establishing that, activate the cell where you pasted the chunk and select required number of columns and rows. Then hit CONTROL+SHIFT+ENTER. That's all. : ========= :