tring to add a search cell

[jamtay317]

I have a data base that I want to search where if I type Sm* it will bring up all of the last names that start with Sm. also useing the same cell I would like to be able to search lot numbers and sales order numbers. I have a data set, please tell me what or where I need to add for someone can help me with the VBA code.

 

[Aladin Akyurek]

ok, I would say that if there is a number look up, like sales order number, then yes it could be in the address portion of the row, but if I'm looking up "man" that would just be under the last name

also, there is about 2000 records in this data that is the reason that there isnt a "man" in the data set that you saw.

Please just try to give an example to look up, appropriate to the sample posted.

 

[jamtay317]

ok if I type "Summerset" it will bring up all of the Summerset Jobs, if I type "13" it will bring up all the records that SaleOrderNumber start with 13

 

[Akashwani]

Hi,

I'm not sure that I fully understand this, but here goes my attempt...

Limited Sample Data....

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3
4		SOrderNumber	invoiceNumber	Date	Billing Number	Last	First	Address	Sq Footage	Flooring Type	GroutColor	Total	City	State
5		13606	13818			Hampton	0	20902 Freemont Moore Road	20	Builder backsplash diagonal		$300.00	Westfield	IN
6		13586	13824			Loyal	Joyce	3617 Tiara Ct	0	0		$45.00	Indianapolis	IN
7		13750	13825			Archie	Betsy	10923 firefly Ct	0	0		$160.00	Indianapolis	IN
8		13873	13871			Sumerset	58	11624 Cannington Cir	0	0		$130.00	Fishers	IN
9		13378	13858			Place	4 Stafford	14534 Alderbrook Trail	1	Builder fireplace		$95.00	Carmel	IN
10		13682-0	13857			Brookheaven	102	2708 Benmore Ct	505	Builder straight		$2,144.00	Zionsville	IN
11		13320	13738			Builders	Shamrock	9800 Westpoint Dr	0	none		$277.00	Indianapolis	IN
12		13506	13977			lakeside	213	13995 amblewind place	24.5	Builder backsplash straight		$294.00	carmel	IN
13		13941	13949			Basham	Virginnia	5801 Lawton Loop E. Dr Unit 5	264	Retail full spread		$205.92	Indianapolis	IN
14		13682-1	13857			Brookheaven	102	2708 Benmore Ct	90	Builder diagonal		$698.00	Zionsville	IN
15
ExDrexelInvoices

Example Results (SOrderNumber) based on the above Limited Sample Data....

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Criteria		Search	Count	10
2		SOrderNumber		SOrderNumber
3		Last		13
4		Flooring_Type		SOrderNumber	invoiceNumber	Date	Billing Number	Last	First	Address	Sq Footage	Flooring Type	GroutColor	Total	City	State
5				13606	13818	0	0	Hampton	0	20902 Freemont Moore Road	20	Builder backsplash diagonal	0	$300.00	Westfield	IN
6				13586	13824	0	0	Loyal	Joyce	3617 Tiara Ct	0	0	0	$45.00	Indianapolis	IN
7				13750	13825	0	0	Archie	Betsy	10923 firefly Ct	0	0	0	$160.00	Indianapolis	IN
8				13873	13871	0	0	Sumerset	58	11624 Cannington Cir	0	0	0	$130.00	Fishers	IN
9				13378	13858	0	0	Place	4 Stafford	14534 Alderbrook Trail	1	Builder fireplace	0	$95.00	Carmel	IN
10				13682-0	13857	0	0	Brookheaven	102	2708 Benmore Ct	505	Builder straight	0	$2,144.00	Zionsville	IN
11				13320	13738	0	0	Builders	Shamrock	9800 Westpoint Dr	0	none	0	$277.00	Indianapolis	IN
12				13506	13977	0	0	lakeside	213	13995 amblewind place	24.5	Builder backsplash straight	0	$294.00	carmel	IN
13				13941	13949	0	0	Basham	Virginnia	5801 Lawton Loop E. Dr Unit 5	264	Retail full spread	0	$205.92	Indianapolis	IN
14				13682-1	13857	0	0	Brookheaven	102	2708 Benmore Ct	90	Builder diagonal	0	$698.00	Zionsville	IN
15
16
Sheet2

Example2 (Last)....

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Criteria		Search	Count	4
2		SOrderNumber		Last
3		Last		B
4		Flooring_Type		SOrderNumber	invoiceNumber	Date	Billing Number	Last	First	Address	Sq Footage	Flooring Type	GroutColor	Total	City	State
5				13682-0	13857	0	0	Brookheaven	102	2708 Benmore Ct	505	Builder straight	0	$2,144.00	Zionsville	IN
6				13320	13738	0	0	Builders	Shamrock	9800 Westpoint Dr	0	none	0	$277.00	Indianapolis	IN
7				13941	13949	0	0	Basham	Virginnia	5801 Lawton Loop E. Dr Unit 5	264	Retail full spread	0	$205.92	Indianapolis	IN
8				13682-1	13857	0	0	Brookheaven	102	2708 Benmore Ct	90	Builder diagonal	0	$698.00	Zionsville	IN
9
10
11
12
13
14
15
16
Sheet2

Example3 (Flooring_Type).....

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Criteria		Search	Count	5
2		SOrderNumber		Flooring_Type
3		Last		Bui
4		Flooring_Type		SOrderNumber	invoiceNumber	Date	Billing Number	Last	First	Address	Sq Footage	Flooring Type	GroutColor	Total	City	State
5				13606	13818	0	0	Hampton	0	20902 Freemont Moore Road	20	Builder backsplash diagonal	0	$300.00	Westfield	IN
6				13378	13858	0	0	Place	4 Stafford	14534 Alderbrook Trail	1	Builder fireplace	0	$95.00	Carmel	IN
7				13682-0	13857	0	0	Brookheaven	102	2708 Benmore Ct	505	Builder straight	0	$2,144.00	Zionsville	IN
8				13506	13977	0	0	lakeside	213	13995 amblewind place	24.5	Builder backsplash straight	0	$294.00	carmel	IN
9				13682-1	13857	0	0	Brookheaven	102	2708 Benmore Ct	90	Builder diagonal	0	$698.00	Zionsville	IN
10
11
12
13
14
15
16
Sheet2

Is that doing what you require?

Ak

 

[jamtay317]

no, that just shows a blank

 

[Akashwani]

Hi,

I don't understand!!

The above shows the following results...
Searching for "SOrderNumber" 13 (returning every record starting with 13)
Searching for "Last" B (returning every record starting with B)
Searching for "Flooring_Type" (returning every record starting with Bui)

What do you mean that it just shows a blank?

Ak

 

[Akashwani]

Hi,

I think I understand now what you mean by, it just shows a blank.

Have you tried to use the formula above in your worksheet?
If so, it wont work until I tell you how!
I didn't want to go into a long explanation if the Example Results were not what you would expect.
So can you let me know if Post #23 is correct or not?

Ak

 

[Aladin Akyurek]

ok if I type "Summerset" it will bring up all of the Summerset Jobs, if I type "13" it will bring up all the records that SaleOrderNumber start with 13

Sheet1, A:M, contains the data.

Sheet2, A:N, contains the processing (here shown in part)...

SOrderNumber	Last	Flooring_Type
		backsplash
Idx	SOrderNumber	invoiceNumber	Date	Billing Number	Last
1	13606	13818	0	0	Hampton
8	13506	13977	0	0	lakeside

<colgroup><col style="width: 103pt; mso-width-source: userset; mso-width-alt: 4892;" width="138"> <col style="width: 134pt; mso-width-source: userset; mso-width-alt: 6343;" width="178"> <col style="width: 132pt; mso-width-source: userset; mso-width-alt: 6257;" width="176"> <col style="width: 122pt; mso-width-source: userset; mso-width-alt: 5774;" width="162"> <col style="width: 131pt; mso-width-source: userset; mso-width-alt: 6200;" width="174"> <col style="width: 127pt; mso-width-source: userset; mso-width-alt: 6001;" width="169"> <tbody>
</tbody>

A1:C1 houses criteria headers.
A2:C2 can be filled in partially; here just a substring as criterion value for Flooring_Type.

A4, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH("*"&$A$2&"|*"&"*"&$B$2&"|*"&"*"&$C$2&"*",
  Sheet1!$A$2:$A$11&"|"&Sheet1!$E$2:$E$11&"|"&Sheet1!$I$2:$I$11)),
  ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1),ROWS($A$4:A4)),"")

B4, just enter, copy across as far as needed, and copy down:

=IF($A4="","",INDEX(Sheet1!$A$2:$M$11,$A4,COLUMNS($B$2:B2)))

 

[jamtay317]

Thanks to everyone for the help, but Its just returning the row numbers any ideas?

 

[jamtay317]

AK, yes I tried your fourmula, and that is when I got a blant

 

[Aladin Akyurek]

Thanks to everyone for the help, but Its just returning the row numbers any ideas?

Not sure what you are responding to... Here is a link:

https://dl.dropboxusercontent.com/u/65698317/aaGenerateSublistUsingPartiallySpecifiedCriteria%20jamtay317.xlsx