SEARCH, MATCH, and FILL A TABLE WITH CONTENT FROM A CUSTOMER LIST

[trevmac4]

HI,
I am trying to create a formula that will grab all matching names i.e. "Existing" from a "Customer_List" (large table of customer information with multiple headings) and populate several related cells that Match the "Existing" search (ex. Name, Sales, Role, etc) . I am not proficient at VBA but willing to try. The worksheet where this Customer_List resides is named "Planning". My Customer_List is $BF$400:$BV$900, and I want the information to be transposed on worksheet "Acct Growth" starting in cell $A:$60. I am then thinking I can use a Vlookup to capture related information in subsequent row cells.

I want to be able to break out this "Customer List" information by several searches (Existing, Prospect, COI) and each search will deliver the results in a seperate table in the "Acct Growth" spreadsheet.

I hope this all makes sense. Thanking you for any and all insight.

Trevor

 

[Akashwani]

Hi Trevor,

I guess the reason why you haven't received an answer is because it is a bit unclear as to the layout of your data, can you provide a sample of what you have and the expected results? Use the link in my signature to post sample data.....

Sample Data...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Existing	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9	Data10	Data11	Data12	Data13	Data14	Data15	Data16	Data17	Data18	Data19	Data20	Data21
2	Bill	cat					Bus														red
3	Bob	dog					Van														blue
4	Tom	cow					car														red
5	Tim	pig					tram														grey
6	Ted	cat					Bus														blue
7	Ben	cat					Van														red
8	Bob	dog					car														yellow
9	Tim	dog					Bus														Black
10	Bob	cow					Van														green
11	Sue	dog					car														yellow
12	Tim	cow					tram														red
13	Joe	pig					Bus														green
14	Jim	cat					Van														red
15	Tim	cat					car														red
16
Planning

Sample Results....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	# Records	Name	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9	Data10	Data11	Data12	Data13	Data14	Data15	Data16	Data17	Data18	Data19	Data20	Data21
2	4	Tim	pig	0	0	0	0	tram	0	0	0	0	0	0	0	0	0	0	0	0	0	grey
3			dog	0	0	0	0	Bus	0	0	0	0	0	0	0	0	0	0	0	0	0	Orange
4			cow	0	0	0	0	tram	0	0	0	0	0	0	0	0	0	0	0	0	0	red
5			cat	0	0	0	0	car	0	0	0	0	0	0	0	0	0	0	0	0	0	red
6
ACCT GROWTH

The formula in C2 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.
Cell references will need to be changed to suit yours.

I hope that helps.

Good luck.

Ak

 

[Aladin Akyurek]

HI,
I am trying to create a formula that will grab all matching names i.e. "Existing" from a "Customer_List" (large table of customer information with multiple headings) and populate several related cells that Match the "Existing" search (ex. Name, Sales, Role, etc) . I am not proficient at VBA but willing to try. The worksheet where this Customer_List resides is named "Planning". My Customer_List is $BF$400:$BV$900, and I want the information to be transposed on worksheet "Acct Growth" starting in cell $A:$60. I am then thinking I can use a Vlookup to capture related information in subsequent row cells.

I want to be able to break out this "Customer List" information by several searches (Existing, Prospect, COI) and each search will deliver the results in a seperate table in the "Acct Growth" spreadsheet.

I hope this all makes sense. Thanking you for any and all insight.

Trevor

Try to adapt the set up from Post #3 of:

http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

which restricts for efficiency reasons the number of array formulas that is needed.

 

[Akashwani]

Hi Aladin,

I must try and remember where I keep that solution!!

Hi Trevor,

This is Aladin's solution....

Sample Data...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Existing	Data2					Data7														Data21
2	Bill	cat					Bus														red
3	Bob	dog					Van														blue
4	Tom	cow					car														red
5	Tim	pig					tram														grey
6	Ted	cat					Bus														blue
7	Ben	cat					Van														red
8	Bob	dog					car														yellow
9	Tim	dog					Bus														Black
10	Bob	cow					Van														green
11	Sue	dog					car														yellow
12	Tim	cow					tram														red
13	Joe	pig					Bus														green
14	Jim	cat					Van														red
15	Tim	cat					car														red
16
Planning

Sample Results....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	# Records	Name	Index	Data2					Data7														Data21
2	4	Tim	4	pig					tram														grey
3			8	dog					Bus														Black
4			11	cow					tram														red
5			14	cat					car														red
6
ACCT GROWTH

The formula in C2 needs entering with ctrl shift enter NOT just enter, you can then copy it down.
The formula in D2 just needs entering and can be copied across and down.

I hope this helps.

And, thanks Aladin.

Ak

 

[trevmac4]

Thank you very much!

Trevor