Is there a Formula to do the advance Filter Unqiue records,but not having blank lines

[John Colling]

Excel version 2002:

Backgound:

I have several thousand combinations of bills of materials & routing
combinations for finshed articles, each with different process, in a data
worksheet. An other sheet "summary" worksheet retrives all the data related
to a selected finshed material with the routing in the operational order
depending on the finished item code used for a vlookup, so its like a one
page summary.

Problem:

Data

Operation
Material Weigh Up
Material Weigh Up
Material
Material
Material
Material
Material
Material
Material
Material
Material
Material
Material
Material
Internal Mixer
Internal Mixer
Mill
Mill
Shape & Size
Shape & Size
QA Inspection
QA Inspection
Bake
Bake
Cut
Cut
Wash
Wash
FQC
FQC
Pack
Pack
Material


Advance filter with unquie records give the follwing result

Operation
Material Weigh Up
Material
Internal Mixer
Mill
Shape & Size
QA Inspection
Bake
Cut
Wash
FQC
Pack


The avance filter option works, but the problem is that I can not use a
hardcoded list to do a lookup as the list varies depending on the material,
hence why I'm looking for a formula to do the above, I've tried to use
={IF(COUNTIF($D$1010,D10)=1,D10,"")}, but this inserts blank lines on the
duplicated item as a running list.

The reason for two lines on the description is that one relates to the setup
operation and the other is the process time and I need to have an overall
summary buy each section for additional calculation but still have the
orginal data unaltered.

Many thanks
Jc

 

[Aladin Akyurek]

For a formula-based approach for extracting a unique list, see:

http://makeashorterlink.com/?G5D112167

 

[David Byrne]

Excel version 2002:

Backgound:

I have several thousand combinations of bills of materials & routing
combinations for finshed articles, each with different process, in a data
worksheet. An other sheet "summary" worksheet retrives all the data related
to a selected finshed material with the routing in the operational order
depending on the finished item code used for a vlookup, so its like a one
page summary.

John,

From what have indicated, Pivot Tables may well be worth investigating.

Allows for new/different data without the need to recode.

David

 

[John Colling]

Thanks for the Pivot table idea, but it does not do what I need it to do,
It seems to take no reference to the original order of operations, that is
why I'm trying to get the pervious link to work, but I don't understand VBA
and its not working for me... I seem to be doing something wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column > 1 Then Exit Sub
Range("a1", Range("a65536").End(xlUp)).Advanced Action:=xlFilterCopy,
CopyToRange:=Sheets("Sheet2").Range("B1" _
), Unique:=True
End Sub

..

I have a cell E3 which is the driver for the vlookup which is the key for
the change, my worksheet is called Summary Sheet and in the VBA Section the
same sheet is called Sheet4(Summary Sheet)

Any chance?

Thanks
JC