Search for data in a column bring all related items in other colum

[sk]

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]

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.)

 

[Biff]

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