Hi, Deen

Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.

Here are some free file hosting options:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php


***********
Regards,
Ron

XL2002, WinXP


"deen" wrote:

Hi Ron,

your are not able to undersatand what i was say,

with ur permission shall i send excel sheet , that given clear
structure.





On Apr 6, 5:48 pm, "deen" wrote:
On Apr 6, 4:48 pm, Ron Coderre
wrote:



It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.

Here are a couple options:

1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt

Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns

The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200

The last step is to hide the Total column.

or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1

Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1

Then....
Select from A2 through the last data cell in Col_A
<format<conditional formatting
Formula is: =(A1=A2)
Click the [format] button and set the font color to white

Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200

Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP

"deen" wrote:
On Apr 5, 9:02 pm, Ron Coderre
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5< $D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)=ROWS($E$1:$E1),SMALL( IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)), "")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"deen" wrote:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

Dear Ron,

Thx for your quick reply, i am not able to understand the formula,

Eg:

In Sheet1 i have data like

Code No Amt

F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200

I Need result in sheet2 like,

Code No Amt
F0001 100
10
20

F0002 50
60
70
200

Pls Help Me,

Regards
Deen

Thx Mr.Ron,

With use of macro they have any possible