Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Q: compare and replace in a workbook
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions |