Help please!

[sportsman1900]

Hello everybody! I posted a question earlier and figured I'd put an
example in to clarify what I'm seeking help for.

Here's a sample set of data - the comma's signify delimited data
(different columns.)


I have row headings strarting in A1 and they go to A5.
First row of data starts in A2. (Please note below data is just dummy
data)


Transaction Number, Company, Date, City, Status
Accntg-123-01,Alpha Corp,20070101,New York,New Data
Accntg-123-02,Beta Corp,20070508,New Jersey,New Data
Accntg-235-01,Gamma Corp,20070301,Delaware,New Data
Accntg-163-01,Delta Corp,20070101,New Haven,New Data
Accntg-175-01,Espsilon Corp,20071101,Newark,New Data


OK, the worksheet contains more data and columns, but above is done
only for the sake of example. Now, here's the problem. I input 3 more
rows of data starting at the next available cell in column A. This
data represents updates to entries above - here's the example:


Accntg-235-01,Gamma-C Corp,20075601,Delaware,New Data
Accntg-123-01,AlphaDog Corp,20072115,New York,New Data
Accntg-163-01,Delta Blues Corp,20070105,New Haven,New Data


As you can see, column A above is similar to 3 entries in the
original
data - so, once these entries are recorded, I want to press a button
to run a macro, which would compare the latest entries with the
previous entries by looking at column A and if there's a match above,
then column E should be changed from "New Data" to "Old Data" -
here's
what it should look like after i run the macro:


Transaction Number, Company, Date, City, Status
Accntg-123-01,Alpha Corp,20070101,New York,Old Data
Accntg-123-02,Beta Corp,20070508,New Jersey,New Data
Accntg-235-01,Gamma Corp,20070301,Delaware,Old Data
Accntg-163-01,Delta Corp,20070101,New Haven,Old Data
Accntg-175-01,Espsilon Corp,20071101,Newark,New Data
Accntg-235-01,Gamma-C Corp,20075601,Delaware,New Data
Accntg-123-01,AlphaDog Corp,20072115,New York,New Data
Accntg-163-01,Delta Blues Corp,20070105,New Haven,New Data


As you can see, the first entry in cell A2 matches A8, which is the
latest transaction, so cell E2 gets changed from "New Data" to "Old
Data." So, whenever i run this macro, the latest entries (at the
bottom of the data) represents new data and compares contents of
whatever is in A with that above and if there's a match above it, the
entries in column E gets updates from "New Data" to "Old Data."


I hope this was clear - please let me know if you need anything
further.


Thanks,


Tim

 

[Bernard Liengme]

I have assumed the accounts are in column A and the text New/old data in E

Sub update()
n = Application.WorksheetFunction.CountA(Range("A:A"))
Set arange = Range("a1:A" & n)
Set brange = Range("E1: E" & n)
For j = 1 To arange.Count
For k = j + 1 To arange.Count
If arange(j) = arange(k) Then
brange(j) = "Old Data"
End If
Next k
Next j
End Sub

 

[Susan]

hi tim
i don't know if you know anything about coding, but here's my idea....
i'd make the entry page a different worksheet, keeping the data
separate (same column headers, same number of columns).

this is what i came up - briefly - doesn't compile - but this is the
gist of it:

Option Explicit

Sub Find_Copy_Enter()

Dim wsEntry As Worksheet
Dim wsData As Worksheet
Dim wb As Workbook

Dim myDataRange As Range, rFind As Range
Dim myDataRow As Long
Dim myDataLastRow As Long

Dim myEntryRange As Range
Dim myEntryRow As Long
Dim myEntryLastRow As Long

Dim c As Range
Dim FindmyEntry As String
Dim FindmyData As Long

Set wb = ActiveWorkbook
Set wsEntry = wb.Worksheets("Sheet2")
Set wsData = wb.Worksheets("Sheet1")

myDataLastRow = wsData.Cells(5000, 1).End(xlUp).Row
Set myDataRange = wsData.Range("a2:a" & myDataLastRow)

myEntryLastRow = wsEntry.Cells(5000, 1).End(xlUp).Row
Set myEntryRange = wsEntry.Range("a2:a" & myEntryLastRow)

For Each c In myEntryRange
FindmyEntry = c.Value
Set rFind = myDataRange.Find(What:=FindmyEntry, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If rFind Is Nothing Then
FindmyEntry.EntireRow.Copy
wsData.Range("a" & myDataLastRow + 1).Paste
myDataLastRow = wsData.Cells(5000, 1).End(xlUp).Row
End If
FindmyData = rFind.Row
Set myDataRange = wsData.Range("a" & FindmyData & ":f" &
FindmyData)
myDataRange.Value = wsEntry.Range("a" & c.Row & ":f" & c.Row)
Next c

End Sub


sorry i don't have time to make it error-proof!
susan

 

[sportsman1900]

I have assumed the accounts are in column A and the text New/old data in E

Sub update()
n = Application.WorksheetFunction.CountA(Range("A:A"))
Set arange = Range("a1:A" & n)
Set brange = Range("E1: E" & n)
For j = 1 To arange.Count
For k = j + 1 To arange.Count
If arange(j) = arange(k) Then
brange(j) = "Old Data"
End If
Next k
Next j
End Sub

--
Bernard V Liengmewww.stfx.ca/people/bliengme
remove caps from email















- Show quoted text -

This worked perfect! Thank you to all who've helped - great group!

-Tim