Two search category & two search terms
in [General]
|
Prev: Pre-loaded Excel 2007 missing "Analysis ToolPak"
Next: Excel currency format does not translate correctly in a Word merge
From: avk on 22 May 2010 15:33 Hello friend Using excel 2003. I have one excel file & in sheet1 i have lot of data. Data record as per below column a1 : is header 1 to h1: is header 8 column a2 : application code column b2 : partno column c2 : description column d2 : remark column e2 : qty Right now i have search only one base category & one search terms. i.e. search category : select header3 & search term : piston. Actually i required two category & two search terms. For example: If In first search category if i have select "Header 1" & enter in search term : "02.2115" (applicationcode) & another search category if i have select "Header 3" & enter in search term : "piston" (description). If application is 02.2115 & description is piston, then which partno is used? In search (list box) result i want to display ...applicationcode : " " ...partno : " " ...description : " " ...remark : " " ...qty : " " In view of the above please explain. Hope you will guide me. Thanks. I have sample attach file +-------------------------------------------------------------------+ |Filename: SEARCH1.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=151| +-------------------------------------------------------------------+ -- avk
From: joel on 23 May 2010 06:10 I assume you are writing a macro. To enter multiple items in a listbox you need to set the list box to multi-column not multirow. To get a box with multiple rows you can do something like this appcode = "abc" partno = "123" Desc = "hello" remark = "goodby" qty = 100 msgbox("applicationcode : " & appcode & vbcrlf & _ "partno : " & partno & vbcrlf & _ "description : " & Desc & vbcrlf & _ "remark : " & remark & vbcrlf _ "qty : " & qty) -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204536 http://www.thecodecage.com/forumz
From: Ron Rosenfeld on 23 May 2010 07:09 On Sat, 22 May 2010 20:33:58 +0100, avk <avk.63e3089(a)excelbanter.com> wrote: > >Hello friend > >Using excel 2003. I have one excel file & in sheet1 i have lot of data. >Data record as per below >column a1 : is header 1 to h1: is header 8 >column a2 : application code >column b2 : partno >column c2 : description >column d2 : remark >column e2 : qty > >Right now i have search only one base category & one search terms. i.e. >search category : select header3 & search term : piston. > > >Actually i required two category & two search terms. >For example: If In first search category if i have select "Header 1" & >enter in search term : "02.2115" (applicationcode) & another search >category if i have select "Header 3" & enter in search term : "piston" >(description). >If application is 02.2115 & description is piston, then which partno is >used? > >In search (list box) result i want to display >..applicationcode : " " >..partno : " " >..description : " " >..remark : " " >..qty : " " > >In view of the above please explain. Hope you will guide me. Thanks. > >I have sample attach file > > >+-------------------------------------------------------------------+ >|Filename: SEARCH1.zip | >|Download: http://www.excelbanter.com/attachment.php?attachmentid=151| >+-------------------------------------------------------------------+ Due to fear of viruses, I rarely download stuff from the web. But your problem seems to me to be one that could be solved, in principal, by using the Advanced Filter. In the Criteria Range, you can specify multiple combinations of terms. --ron
From: avk on 2 Jun 2010 08:25 Awaiting for reply. +-------------------------------------------------------------------+ +-------------------------------------------------------------------+ -- avk
From: Steve Dunn on 2 Jun 2010 09:11
Like most, if not all, of the people helping here, I will not open an attached file, but your explanation is almost sufficient. Assuming your criteria are in J1 and L1, and your results are in J2:N2, you could use this in J2: =INDEX($A$1:$H$5,MATCH(1,INDEX(($A$1:$A$5=$J$1)* ($C$1:$C$5=$L$1),),0),COLUMN()-COLUMN($J$2)+1) copied along to N2. HTH Steve D. "avk" <avk.64c0844(a)excelbanter.com> wrote in message news:avk.64c0844(a)excelbanter.com... > > Awaiting for reply. > > > +-------------------------------------------------------------------+ > +-------------------------------------------------------------------+ > > > > -- > avk |