HLOOKUP: List Aggregation

[Austin80]

Hi, I could probably solve this in VBA, but there's got to be a better way to do this in Excel.

I'd like collect every instance of an entry in list A, that appears in list B (with its search criteria), and put it in a results table, as shown below.

For example:

 

[DILIPandey]

How you got 7,8,9,5 in column H against Goose ?
whereas column B has 3, 5, 8, 2 for Goose.


Regards,
DILIPandey

 

[Austin80]

You're exactly right, I knew I was tired last night, but that's embarrassing. Thanks for pointing that out.

 

[DILIPandey]

In C1, enter heading as "chk" and in c2, enter formula =VLOOKUP($A2,$D$2:$E$5,2,0)

Now, create a pivot table with "chk" field in Filter area and "Title" field and "Value" field in Rows area of pivot table. Right click-> pivot table options -> Display tab -> Classic pivot table layout.
Now, filter filed "chk" to Yes and your pivot should look like below:-

Title	Value	chk	Search	Search		chk	Yes
G	33	Yes	G	Yes
D	34	No	D	No
T	25	Yes	T	Yes		Title	Value
G	28	Yes	C	No		G	6
C	10	No				G	28
T	5	Yes				G	33
G	98	Yes				G	89
D	63	No				G	93
T	59	Yes				G	98
G	6	Yes				G Total
C	48	No				T	5
T	39	Yes				T	25
G	93	Yes				T	39
G	89	Yes				T	59
D	17	No				T Total
						Grand Total

<colgroup><col span="6"><col><col></colgroup><tbody>
</tbody>

Which will show you all fields marked as Yes and their respective values


Regards,
DILIPandey

 

[Austin80]

Thank you very much. I never would have thought to add that chk colum with vlookup. Brilliant.

 

[Peter_SSs]

Another option.

For original data as shown in columns A:B and D:E below

1. Leave F1 blank
2. Put the formula shown in F2
3. Select A1:B16
4. Data ribbon tab|Sort & Filter Group|Advanced|Copy to another location|List range: (should already be populated with A1:B16)|Criteria range: F1:F2|Copy to G1 (say)|OK

Results are as shown in columns G:H

To tidy up if required:
5. Clear F2
6. Sort results table

Excel Workbook
	A	B	C	D	E	F	G	H
1	Title	Value		Search	Search		Title	Value
2	G	33		G	Yes	TRUE	G	33
3	D	34		D	No		T	25
4	T	25		T	Yes		G	28
5	G	28		C	No		T	5
6	C	10					G	98
7	T	5					T	59
8	G	98					G	6
9	D	63					T	39
10	T	59					G	93
11	G	6					G	89
12	C	48
13	T	39
14	G	93
15	G	89
16	D	17
17
List

 

[DILIPandey]

You are welcome Austin80.... Cheers


Regards,
DILIPandey