Find then Sum

[Tsunami]

First off thanks for any help you can offer.

I have, on a regular basis, a list of 1,200 store numbers in one
column. And in the next column counts of customers. Of those 1,200
store numbers 15 have changed and are listed in the 1,200.

For example: stores 6, 10, & 15 are now 200, 132, & 1001.
6 is now 200, 10 is now 132, 15 is now 1001.

What I'm trying to do is find count of customers for the closed stores
and add it to the count of the new stores. Then delete the row
containing closed store.

Before
A B
6 1
10 1
15 1
132 30
200 30
1001 30

After
A B
132 31
200 31
1001 31

I've tried using an array to replace the old store number with the new
but haven't had much success. Any ideas or suggestions would be
greatly appreciated

 

[Tsunami]

First off thanks for any help you can offer.

I have, on a regular basis, a list of 1,200 store numbers in one
column. And in the next column counts of customers. Of those 1,200
store numbers 15 have changed and are listed in the 1,200.

For example: stores 6, 10, & 15 are now 200, 132, & 1001.
6 is now 200, 10 is now 132, 15 is now 1001.

What I'm trying to do is find count of customers for the closed stores
and add it to the count of the new stores. Then delete the row
containing closed store.

Before
A       B
6       1
10      1
15      1
132     30
200     30
1001    30

After
A       B
132     31
200     31
1001    31

I've tried using an array to replace the old store number with the new
but haven't had much success. Any ideas or suggestions would be
greatly appreciated

Ok, so I've figured how to change the store number to the current
store number using an array.

Dim Old
Dim Remod
Dim Closed
Dim x, y
Dim Rng As Integer

y = 0
x = 1

Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count

Closed = Array(-1, 15, 13, 15, 56, 873, 880)
Old = Array(6, 50, 53, 58, 61, 69, 74, 76, 88, 98, 120, 122, 128, 131,
133, 135, 137, 146, 203, 410)
Remod = Array(113, 109, 179, 177, 112, 180, 174, 496, 178, 1224, 111,
175, 223, 110, 106, 107, 674, 108, 176, 1233)
Range(Cells(3, 1), Cells(Rng, 1)).Select

Do Until y = 20
If Cells(x, 1) = Old(y) Then
Cells(x, 1) = Remod(y)
y = y + 1
End If

If y = 20 Then Exit Sub

If Cells((x + 1), 1) > Old(y) Then 'looks to see if the next cell
value is greater than the next value in the array
y = y + 1 'set's y to go to the next
value in the array
End If
x = x + 1
Loop


End Sub

now I just need to sum up the stores and delete the old store numbers
(but the problem with this is that they are not the old store numbers
now).

 

[joel]

the store customer counts are on sheet 1. I put the following table i
sheet 2 :

6 200
10 132
15 1001



Sub UpdateStores()

With Sheets("Sheet2")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
OldStore = .Range("A" & RowCount)
NewStore = .Range("B" & RowCount)
With Sheets("Sheet1")
FoundStore = True
Set c = .Columns("A").Find(what:=OldStore, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Old Store : " & OldStore)
FoundStore = False
End If
Set c1 = .Columns("A").Find(what:=NewStore, _
LookIn:=xlValues, lookat:=xlWhole)
If c1 Is Nothing Then
MsgBox ("Cannot find New Store : " & NewStore)
FoundStore = False
End If
If FoundStore = True Then
OldCount = c.Offset(0, 1)
NewCount = c1.Offset(0, 1)
c1.Offset(0, 1) = OldCount + NewCount
c.EntireRow.Delete
End If
End With
RowCount = RowCount + 1
Loop
End With
End Su

 

[Tsunami]

the store customer counts are on sheet 1.  I put the following table in
sheet 2 :

6       200
10      132
15      1001

Sub UpdateStores()

With Sheets("Sheet2")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
OldStore = .Range("A" & RowCount)
NewStore = .Range("B" & RowCount)
With Sheets("Sheet1")
FoundStore = True
Set c = .Columns("A").Find(what:=OldStore, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Old Store : " & OldStore)
FoundStore = False
End If
Set c1 = .Columns("A").Find(what:=NewStore, _
LookIn:=xlValues, lookat:=xlWhole)
If c1 Is Nothing Then
MsgBox ("Cannot find New Store : " & NewStore)
FoundStore = False
End If
If FoundStore = True Then
OldCount = c.Offset(0, 1)
NewCount = c1.Offset(0, 1)
c1.Offset(0, 1) = OldCount + NewCount
c.EntireRow.Delete
End If
End With
RowCount = RowCount + 1
Loop
End With
End Sub

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=152457

Microsoft Office Help

Works like a charm. Thanks