Find then Sum
 

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

Find then Sum
 

On Nov 9, 2:21*pm, Tsunami wrote:
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).

Find then Sum
 

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/sh...d.php?t=152457

Microsoft Office Help

Find then Sum
 

On Nov 9, 4:19*pm, joel wrote:
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/sh...d.php?t=152457

Microsoft Office Help

Works like a charm. Thanks