merging data

C

codergreg

Hi all:)
I'm new to this forum and to to Excel (2000) and not sure that I'm
asking this question correctly but here is what I'm trying to do.

The data on my work sheet is pasted from another sorce and includes
information: name, ref #, time, date. I use this information looking
up the status of an order and enter a "status note" in 5th (last)
column.

As the day progresses, the source that the data comes from is copied
and pasted under the current list I already have going and much of the
data on the lower list is a duplicate. How can I merge the to list so
that I have one that also carries in my status note in the 5th column.
 
M

Max

One play to try ..

Assume source table is in Sheet1, cols A to E,
(col headers: name, ref#, time, date, status)
Data is from row2 down
Key col = col A (names)

In Sheet2,
A1:E1 contains the same col headers as in Sheet1

Put in A2:
=IF(ISERROR(SMALL($F:$F,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 across to E2

Put in F2:
=IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!$A$2:A2,Sheet1!A2)>1,"",ROW()))
(Leave F1 empty)

Select A2:F2, fill down to say, F100
to cover the max expected data range in Sheet1
(Format the cols C & D in time/date format accordingly)

Sheet2 will return the required merge of the data from Sheet1,
with all lines neatly bunched at the top

The status data in col E would be pulled over correctly since these
will be entered next to the first instances of the names in Sheet1
(Duplicate names, e.g. 2nd occurrences, if any, will be omitted in the
extract)
 
Top