Create an array based on a lookup that filters certain data

[benblaisdell]

I think what I am trying to accomplish is fairly strait forward, but I am having the hardest time with it. I have a whole set of data in one particular sheet (data in the columns and the different records in the rows, with a unique identifier for each record in the first column). I am trying to bring in an array of all those unique identifiers into a new workbook(or even another worksheet for that matter), that only delievers those unique identifiers if some other criteria is met (such as Column B meets a certain criteria). I need this array to be as automated as possible, so I really don't want to use VBA if I dont have to. In other words if the source data changes, I want the other workbook to be smart enough to update immedeatly as well every time it is opened.

Any help would be appreciated.

 

[Akashwani]

Hi,

Welcome to MrExcel.

Is this the solution you require?....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	ID	Criteria	*	Joined	Count	*	Criteria1	Criteria2	Total	List
2	ID1234	Yes	*	ID1234Yes	1	*	Yes	1	4	ID1234
3	ID1235	Yes	*	ID1235Yes	1	*	*	*	*	ID1235
4	ID1236	no	*	ID1236no	1	*	*	*	*	ID1236
5	ID1237	yes	*	ID1237yes	1	*	*	*	*	ID1237
6	ID1238	no	*	ID1238no	1	*	*	*	*	*
7	ID1239	no	*	ID1239no	1	*	*	*	*	*
8	ID1240	no	*	ID1240no	1	*	*	*	*	*
9	ID1234	Yes	*	ID1234Yes	0	*	*	*	*	*
10	ID1235	Yes	*	ID1235Yes	0	*	*	*	*	*
11	ID1236	no	*	ID1236no	0	*	*	*	*	*
12	ID1237	yes	*	ID1237yes	0	*	*	*	*	*
13	ID1238	no	*	ID1238no	0	*	*	*	*	*
14	ID1239	no	*	ID1239no	0	*	*	*	*	*
15	ID1234	Yes	*	ID1234Yes	0	*	*	*	*	*
16	ID1235	Yes	*	ID1235Yes	0	*	*	*	*	*
17	ID1236	Yes	*	ID1236Yes	1	*	*	*	*	*
18	ID1237	yes	*	ID1237yes	0	*	*	*	*	*
19	ID1238	no	*	ID1238no	0	*	*	*	*	*
20	ID1239	no	*	ID1239no	0	*	*	*	*	*
21	ID1240	no	*	ID1240no	0	*	*	*	*	*
Sheet4

As you can see I have added 2 extra columns with formulas, D & E, these can be placed anywhere on your Worksheet or hidden if you require. Copy the formulas in D2 & E2 down as far as your range goes.

The formula in G,H,I,J can be placed on another Worksheet, the formula in J2 needs to be copied down as far as you think you will need to go. Criteria1 can be changed to suit your criteria, Criteria2 MUST be 1.

I hope this helps.

Ak