ExcelBanter - Q: compare and replace in a workbook

One way ..

Assuming the data below is in Sheet1, col A, in A1 down
(Note that I've corrected some discrepancies in your original data posted,
viz.
I've assumed that only there's only PAR039 for 121102 & only PAR002 for
121103)

121102
MENY DRØBAK
2004-11-30
PAR039
S550077
P144026
P40202

121102
MENY DRØBAK
2004-11-30
PAR039
S550077
P144026
P40202

121103
SPAR FOLKESTAD MAT
2004-11-30
PAR002
S550077
P144026
P40202

121103
SPAR FOLKESTAD MAT
2004-11-30
PAR002
S550077
P144026
P40202

121103
SPAR FOLKESTAD MAT
2004-11-30
PAR002
S550077
P144026
P40202

As the data in col A appears regular in structure, i.e. in groups of 7 lines
each with a single blank line in-between groups, it's possible to quickly
re-arrange into separate columns to the right

Put in B1:
=OFFSET($A$1,ROWS($A$1:A1)*8-8+COLUMNS($A$1:A1)-1,)

Copy B1 across to H1, fill down until zeros appear,
signalling exhaustion of data

The "vertical" data in col A will be re-arranged
nicely into "horizontal" lines in cols B to H

Put in J1: =TRIM(B1&C1&D1&E1&F1&G1&H1)

Put in K1: =IF(COUNTIF($J$1:J1,J1)1,"",ROW())

Select J1:K1, copy down as many rows as there is data in cols B to H

Col J will create concatenated strings
to identify the original data groups uniqueness

Col K will "filter out" duplicates in col J
by assigning "arbitrary" row numbers only to unique items in col J

In Sheet2
-------------
Put in say, A2:
=IF(ISERROR(SMALL(Sheet1!$K:$K,ROWS($A$1:A1))),"", INDEX(Sheet1!B:B,MATCH(SMA
LL(Sheet1!$K:$K,ROWS($A$1:A1)),Sheet1!$K:$K,0)))

Copy A2 across to G2,
fill down by as many rows as was done for col K in Sheet1

The above will extract only the unique lines over from Sheet1,
i.e. for the sample data, you'll get the final desired results:

121102 MENY DRØBAK 2004-11-30 PAR039 S550077
P144026 P40202
121103 SPAR FOLKESTAD MAT 2004-11-30 PAR002 S550077
P144026 P40202
< rest are "blanks"

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Arnfinn Hakkebo" wrote in message
...
I have data like this:
A B C
D E F G
121102
MENY DRØBAK
2004-11-30
PAR039
S550077
P144026
P40202

121102
MENY DRØBAK
2004-11-30
PAR040
S550077
P144026
P40202

121103
SPAR FOLKESTAD MAT
2004-11-30
PAR002
S550077
P144026
P40202

121103
SPAR FOLKESTAD MAT
2004-11-30
PAR003
S550077
P144026
P40202

121103
SPAR FOLKESTAD MAT
2004-11-30
PAR004
S550077
P144026
P40202

in an excel workbook.
What Id like to do is have a function or macro that replaces data with for
example "-" if column 1 has more than one instance of the same data.
Result should be like:

121102 MENY DRØBAK 2004-11-30 PAR039 S55077
P144026 P40202
- - -
- - - -
121103 SPAR FOLKESTAD MAT 2004-11-30 PAR002 S55077 P144026
P40202
- - -
- - - -
- - -
- - - -

I hope this is a good enough explanation.

regards
Arnfinn Hakkebo