Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Search for data in a column bring all related items in other colum
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 ? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Search for data in a column bring all related items in other colum
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/xlCondFor...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: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Search for data in a column bring all related items in other colum
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,MATC H(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" wrote in message ... 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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for a value in one column and find another value from anoth | Excel Discussion (Misc queries) | |||
Search /Filter vertical Numeric pattern (down single column) | Excel Worksheet Functions | |||
Problem with search and replace data,thanks for you help in advance. | Excel Discussion (Misc queries) | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) |