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/