help needed with excel macro
 

I have a macro for consolidating sales data, but it's not doing exactl
what I need it to. I'm not a programmer, so doing this is a littl
beyond me.

I am using Excel XP.

The attached file (wtnov2.xls) is the data that we need to work with
the output I am looking for is a breakdown of how many of each Item
was sold. The point of sale software stores sales like this: say w
sold a 24 exp film (item#10024), prophetline would store it like this


10024 FUJ POP CN 135-4 1

If 5 were sold it would be stored like this:

10024 FUJ POP CN 135-4 5

So the macro would need to count how many cells contain matching item
's as well as adding the quantities if it is more that 1.

Currently, the macro only looks at the first and second columns, Item
and Desc. If I run the macro as is on wtnov.xls the quantites ar
totally out of whack.

Here's the code for the macro as it is now:


Sub ConsolidateData()

Dim i As Integer, ii As Integer, iii As Integer
Dim NumRows As Integer
NumRows = Range("A65536").End(xlUp).Row
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Columns("A:B").VerticalAlignment = xlVAlignTop
For i = 3 To NumRows + 3
For ii = i + 1 To NumRows + 3
If Trim(Cells(ii, 1)) = Trim(Cells(i, 1)) Then
iii = iii + 1
Else
If iii 0 Then
Range(Cells(i, 1), Cells(ii - 1, 1)).MergeCells = True
Range(Cells(i, 2), Cells(ii - 1, 2)).MergeCells = True
End If
Cells(i, 3) = iii + 1
i = ii - 1
iii = 0
Exit For
End If
Next ii
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Another thought I had was to not merge the cells but to:
1) Count the number of same Item numbers in column A and then add th
quantities if they are greater than 1
2) Then record the result to column D
3) Then delete the rows containing the duplicates

Clear as mud?

Thanks in advance for whatever help that you can give me...

Neil

+----------------------------------------------------------------
| Attachment filename: wtnov2.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=364549
+----------------------------------------------------------------

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

help needed with excel macro
 

on another sheet
A1: 10024
B1: =sumif(Sheet1!A:A,A1,C:C) assuming the quantities are in column C.

Regards,
Tom Ogilvy

"shaltar" wrote in message
...

I have a macro for consolidating sales data, but it's not doing exactly
what I need it to. I'm not a programmer, so doing this is a little
beyond me.

I am using Excel XP.

The attached file (wtnov2.xls) is the data that we need to work with,
the output I am looking for is a breakdown of how many of each Item#
was sold. The point of sale software stores sales like this: say we
sold a 24 exp film (item#10024), prophetline would store it like this:


10024 FUJ POP CN 135-4 1

If 5 were sold it would be stored like this:

10024 FUJ POP CN 135-4 5

So the macro would need to count how many cells contain matching item#
's as well as adding the quantities if it is more that 1.

Currently, the macro only looks at the first and second columns, Item#
and Desc. If I run the macro as is on wtnov.xls the quantites are
totally out of whack.

Here's the code for the macro as it is now:


Sub ConsolidateData()

Dim i As Integer, ii As Integer, iii As Integer
Dim NumRows As Integer
NumRows = Range("A65536").End(xlUp).Row
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Columns("A:B").VerticalAlignment = xlVAlignTop
For i = 3 To NumRows + 3
For ii = i + 1 To NumRows + 3
If Trim(Cells(ii, 1)) = Trim(Cells(i, 1)) Then
iii = iii + 1
Else
If iii 0 Then
Range(Cells(i, 1), Cells(ii - 1, 1)).MergeCells = True
Range(Cells(i, 2), Cells(ii - 1, 2)).MergeCells = True
End If
Cells(i, 3) = iii + 1
i = ii - 1
iii = 0
Exit For
End If
Next ii
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Another thought I had was to not merge the cells but to:
1) Count the number of same Item numbers in column A and then add the
quantities if they are greater than 1
2) Then record the result to column D
3) Then delete the rows containing the duplicates

Clear as mud?

Thanks in advance for whatever help that you can give me...

Neil.


+----------------------------------------------------------------+
| Attachment filename: wtnov2.xls |
|Download attachment:
http://www.excelforum.com/attachment.php?postid=364549|
+----------------------------------------------------------------+

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/