Search for data in a column bring all related items in other colum sk 02.09.2006 11:02:01 I have data in the excel sheet with .A column for OrderNo and B Column for items for that Order. A B ORD001 ITEM1 ITEM2 ITEM3 ORD002 ITEM4 ITEM1 ITEM5 There are more than thousand orders.I want search for an Order so that it brings all items with it. I cannot use filter in A column as it doesn't recognise blank cell.So Option to repeat order nos in A for every item involves a lot of data entry. Any Solution ?

Re: Search for data in a column bring all related items in other colum Dave Peterson <petersod[ at ]verizonXSPAM.net> 02.09.2006 13:08:26 I'd fill those empty cells in column a with the previous value. And use format|conditional formatting to hide the duplicated values (if I needed to). Then I'd use data|Filter|autofilter. If you want to try, see Debra Dalgleish's site: http://www.contextures.com/xlDataEntry02.html http://www.contextures.com/xlCondFormat03.html#Duplicate and http://www.contextures.com/xlautofilter01.html (ps. I wouldn't use the format|conditional formatting--just in case I filtered by another column and hid the topmost row of that group. I like to see all the data.) sk wrote: [Quoted Text] > > I have data in the excel sheet with .A column for OrderNo and B Column for > items > for that Order. > > > A B > > ORD001 ITEM1 > ITEM2 > ITEM3 > > ORD002 ITEM4 > ITEM1 > ITEM5 > > There are more than thousand orders.I want search for an Order so that it > brings all items with it. > I cannot use filter in A column as it doesn't recognise blank cell.So Option > to > repeat order nos in A for every item involves a lot of data entry. > Any Solution ? -- Dave Peterson

Re: Search for data in a column bring all related items in other colum "Biff" <biffinpitt[ at ]comcast.net> 02.09.2006 21:39:35 Here's another one: Assumptions: The order numbers in column A are TEXT entries. There is one empty row between each order. Mark the end of your data with a flag. Like this: ..................A..............B 1............header......header 2.........ORD001.....ITEM1 3............................ITEM2 4............................ITEM3 5....................................... 6..........ORD002....ITEM4 7............................ITEM1 8............................ITEM5 9....................................... 10........<END>................ Then: D2 = order number to lookup = ORD002 Enter this formula in E2 and copy down until you get blanks: =IF(ROWS($1:1)<=MATCH("*",A$10:INDEX(A$2:A$10,MATCH(D$2,A$2:A$10,0)+1),0)-1,INDEX(B$2:B$8,MATCH(D$2,A$2:A$10,0)+ROWS($1:1)-1),"") Sample file: Sample_lookup.xls 15kb http://cjoint.com/?jcxJGStQ80 Biff "sk" <sk[ at ]discussions.microsoft.com> wrote in message news:C4662C32-E84E-4DB1-9F6A-1E155F7E869C[ at ]microsoft.com... [Quoted Text] >I have data in the excel sheet with .A column for OrderNo and B Column for > items > for that Order. > > > A B > > ORD001 ITEM1 > ITEM2 > ITEM3 > > ORD002 ITEM4 > ITEM1 > ITEM5 > > There are more than thousand orders.I want search for an Order so that it > brings all items with it. > I cannot use filter in A column as it doesn't recognise blank cell.So > Option > to > repeat order nos in A for every item involves a lot of data entry. > Any Solution ?