Summarizing Data

law

New Member
Joined
Sep 16, 2002
Messages
16
How do I summarize data from a spreadsheet without using the pivot table???. I have a supply list and I want to summarize the quantity, size , and description of what was ordered on a seperate worksheet. Is this possible. Thanks for your help.
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
I helped someone with something like that recently.... they started out with this:
SumOn2Cols.xls
ABCD
1DEPTDATEDEBITCREDIT
25COLCP1USE7/2/2002313.230
35COLCP1USE7/2/20020-1730.13
45COLCP1USE7/2/20020-3450.82
55COLCP1USE7/2/20020-3665.65
65COLCP1USE7/4/2002132.120
75COLCP1USE7/4/20022027.220
Sheet1


... and wanted to summarize by part no and date like this:
SumOn2Cols.xls
ABCD
1DEPTDATEDEBITCREDIT
25COLCP1USE7/2/2002313.23-8846.6
35COLCP1USE7/4/20022159.340
4
Sheet2


The following is the code... hope this gives you a good start!<pre>
Sub MergeRows()

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim Sell As Range
Dim CurrRow As Long
Dim KeyFld As String
Dim LastRow As Long
Dim OldRange As Range
Dim NewRow As Long
Dim LastKeyFld As String

' turn off screen updating
Application.ScreenUpdating = False

' point to the worksheets
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")

' clear Sheet2
wks2.Columns("A:IV").Clear

' copy the headings to Sheet2
wks1.Rows("1:1").Copy
wks2.Rows("1:1").PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

' point to Sheet1
wks1.Activate

' get the number of rows used on Sheet1
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
NewRow = 1

' set the range to process to column A
Set OldRange = wks1.Range("A2:A" & LastRow)

For Each Sell In OldRange
CurrRow = Sell.Row

' build comparison key (Dept & Date)
KeyFld = Trim(Cells(CurrRow, 1).Value) & Trim(Cells(CurrRow, 2).Value)

If KeyFld = LastKeyFld Then
'store the debit
If Cells(CurrRow, 3).Value<> 0 Then
wks2.Range("C" & NewRow).Value = wks2.Range("C" & NewRow).Value + _
wks1.Range("C" & CurrRow).Value
End If
'store the credit
If Cells(CurrRow, 4).Value<> 0 Then
wks2.Range("D" & NewRow).Value = wks2.Range("D" & NewRow).Value + _
wks1.Range("D" & CurrRow).Value
End If

Else
LastKeyFld = KeyFld 'store the new key
NewRow = NewRow + 1 ' increment the new row for sheet2
' copy the row from sheet1 to sheet2
wks1.Range(CurrRow & ":" & CurrRow).Copy
wks2.Range(NewRow & ":" & NewRow).PasteSpecial Paste:=xlAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Next Sell

'point to cell A1 on Sheet2
Application.CutCopyMode = False
wks2.Activate
Range("A1").Select
Application.ScreenUpdating = True

End Sub</pre>
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-09-19 06:58, law wrote:
How do I summarize data from a spreadsheet without using the pivot table???. I have a supply list and I want to summarize the quantity, size , and description of what was ordered on a seperate worksheet. Is this possible. Thanks for your help.

If you know SQL you could use the Data | Get External Data | New Database Query... menu command, Microsoft Query and the Excel ODBC
Driver.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top