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:-

[TABLE="width: 535"]
<colgroup><col span="6"><col><col></colgroup><tbody>[TR]
[TD]Title[/TD]
[TD]Value[/TD]
[TD]chk[/TD]
[TD]Search[/TD]
[TD]Search[/TD]
[TD][/TD]
[TD]chk[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]33[/TD]
[TD]Yes[/TD]
[TD]G[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]34[/TD]
[TD]No[/TD]
[TD]D[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]25[/TD]
[TD]Yes[/TD]
[TD]T[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Title[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]28[/TD]
[TD]Yes[/TD]
[TD]C[/TD]
[TD]No[/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]10[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]5[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]98[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]89[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]63[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]93[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]59[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]6[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]G Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]48[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD="align: right"]39[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]93[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD="align: right"]89[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]17[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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