Is this an array usage or is an array necessary?

[thetexan]

I have a quandary and I'd like to know how to pull this off. First here's the setup...

I have a large data set with thousands of rows of records that have to do with sales people, their sales, sales per item, etc.

In a second much smaller data set I have the employee records showing the sales people and what region they are from.


I want to filter the big list to show all sales records for sales people from region II. This may be 5 or 6 sales people.

Now, using advanced filtering, if I knew the names of these people, I could just list the 5 or 6 names in the criteria range and the big range would display only those names.

What I want to do is to somehow lookup the names of all region II sales people from the little employee range and have that list of 5 or 6 people populated in the "names" column of the criteria range as if I had manually entered them myself.

So I need something that will lookup and return a list of names and then enter that list into the criteria range.

I think vlookup only returns one result.

Is there anyway to return a list and have that list inserted some other place like the criteria range that can then be used to filter the big range?

tex

[TMS]

Why don't you do it the other way round? Add a helper column to the big list and put the VLOOKUP in there to search for the employee and return the region.


Regards, TMS

[thetexan]

Let me ask it this way...

Usually there is one formula in one cell that returns one result In That cell.

Is there a way for one formula in one cell to return a list of multiple results in multiple cells??

For example...I enter some formula in one cell and I get (say) a list of 30 items in thirty cells?

Tex

[FDibbins]

It would help if we could see a sample wf what you have and what you want, but see if this will get you started (if I understand you properly)...

	A	B	C	D	E	F
1	Name	Item	Rates	Unit		Name1
2	name1	Item 1	18	m2		Item 1
3	name1	Item 2	16	m2		Item 2
4	name1	Item 3	15	m2		Item 3
5	name1	Item 4	2	m2		Item 4
6	name 2	Item 1	36	m2
7	name 2	Item 2	30	m2
8	name 2	Item 3	30	m2
9	name 2	Item 4	28	m2
10	name 3	Item 1	26	m2
11	name 3	Item 2	38	m2
12	name 3	Item 3	34	m2
13	name 3	Item 4	32	m2

F2=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$13=$H$2,ROW($A$2:$A$13)),ROWS($A$1:A1))),"")
copied down.

this ia an ARRAY formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[TMS]

I imagine that someone here could come up with an array formula that might do that. But not me.

Personally, I'd look to make the data a Structured Table and then use the filtering and sorting to view the records you are interested in

But, if you're just going to drip feed information with no real detail, it will take forever to find a solution for you.

I suggest that you upload a sample workbook that accurately represents the live file but wit a limited subset of records with any sensitive data removed. If you also provide a mock up of what you hope to see based on the sample data, it will encourage people to offer solutions.

Bear in mind that if you have a small list of, say, hundreds of records, processed against a large list of, say, thousands of transaction, any array formula based solution is likely to be very processor heavy.

What do you plan to do with the data? Would a Pivot Table not be a better option?

[TMS]

@Ford: hat's in H2 to get that?

[FDibbins]

@ TMS...hat's? (that's?)
I didnt show column H?
What am I missing lol

[TMS]

Hat's off ... should have been what's . Your formula refers to cell H2. Just wondered what' sin it?

[FDibbins]

aahh gotcha, good catch.

H2 contained Name1

[TMS]

Woulda been better if it was NAME2 or NAME3. It looks like you just copied them across

[FDibbins]

Maybe this will make it clearer

	A	B	C	D	E	F	G	H
1	Name	Item	Rates	Unit		Name1
2	name1	Item 1	18	m2		Item 5		name2
3	name1	Item 2	16	m2		Item 6
4	name1	Item 3	15	m2		Item 7
5	name1	Item 4	2	m2		Item 8
6	name2	Item 5	36	m2
7	name2	Item 6	30	m2
8	name2	Item 7	30	m2
9	name2	Item 8	28	m2
10	name3	Item 9	26	m2
11	name3	Item 10	38	m2
12	name3	Item 11	34	m2
13	name3	Item 12	32	m2

F2=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$13=$H$2,ROW($A$2:$A$13)),ROWS($A$1:A1))),"")
ARRAY entered

[TMS]

Yep. Apart from the NAME1 in F1. What's that about then

[thetexan]

Here is what I'm getting at...


FINAL1.jpg

I want to enter a formula in A2 (I think, this is where I'm not sure) that will lookup all region 2 employees by name in the employee dataset then send those names in the form of a list and populate them in A2 and downward in an advanced filter criteria range without me having to type all of the names in the criteria list manually.

Thusly...

final2.jpg

Both of the data sets will be hundreds and thousands of lines long so it needs to be automated with a formula. In the end I want to advance filter the sales data set and display only region 2 employees.

tex

[TMS]

@tex: you know all the trouble you went to to make the pretty pictures of your sample workbook? Yes, well, much better to post the actual workbook

[FDibbins]

Yep. Apart from the NAME1 in F1. What's that about then

It's just a heading :0

[thetexan]

ok, here is the workbook...

[FDibbins]

Try these...
A2=IFERROR(INDEX($A:$A,SMALL(IF($H$9:$H$22=2,ROW($A$9:$A$22)),ROWS($A$1:A1))),"")
ARRAY entered
B2=IF($A2="","",INDEX($B$9:$D$21,MATCH($A2,$A$9:$A$22,0),MATCH(B$1,$B$8:$D$8,0)))
copied across

Then copy them all down as needed

[newdoverman]

In your criteria range under Name enter this Array formula and fill down:

Please Login or Register  to view this content.

I assigned H2 to be the region that you want to extract records from.

[thetexan]

Thank you for the idea and formula. I think that will solve the problem.

I still would like an answer to the other question I have. Here it is again.

Each of the above solutions require "and fill down" requiring a manual effort on my part to populate the formula for each cell below.

I'm not looking for a formula here just a yes or no answer if possible.

Whether on this subject or any other subject...is it possible to enter some formula into a single cell which results in a list of, say, 20 (for example) items each of which is populated into individual cells...for example...a formula in A1 which looks up something in a data set then sends the 20 results it finds into cells B1:B20.

Alternatively...is there a way (again yes or no) to enter a formula which retrieves a list of data where those 20 items are in the form of an array which can, in a second single step, be distributed into cells B1:B20.

As I stated earlier, it seems that in excel you enter one formula per cell (either manually or by filling down) and get one result per cell. I haven't seen where you enter a single formula into one cell which can result in a list of items which can be placed in numerous cells.

One thing that came to mind was if I used some lookup that looks up something, ie find all occurances of "Tom" then place each of his phone calls into cells B1:Bwhatever, where each time the lookup found an occurance it placed it into B1, then incremented the row to B2 waiting for the next occurance, and so on.

I hope I'm explaining myself adaquately. I'm not sure how to even phrase the question. This is the kind of thing I'm looking for. I just want to know if it's possible (auto incrementing of rows, etc) and if it is can you please point me in the right direction and I will do my homework and study it. I just don't know where to look.

thanks,
tex

[Kyle123]

Since you want a simple answer, No, excel doesn't do that. It's one formula, one answer

[Pete_UK]

@thetexan,

What you would usually do is to copy the formula down as many cells as you expect in the worse case, and arrange for the formula to return blanks if there is no more matching data. Within the formula you would make use of a term like ROWS($1:1), which increments as it is copied down.

Hope this helps.

Pete

[thetexan]

Perfect. I will quit looking for something that doesn't exist. Incrementing will be the way to go I suspect.

Thanks

[Pete_UK]

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete