Well, I'm trying to avoid macros. I don't mind doing the cutting and pasting
myself - I can just AutoFilter and copy visible cells, but I want something
that stays "live", if you get my drift. So I can keep adding data to the one
sheet, and having it automagically populate the summary.

And since I won't be the one always in control of the file, macros scare me!



"Joel" wrote:

you need to use a macro. the macro below clears out sheet 2 and then copies
sheet 1 to 2. It then sorts the data location first and then lot. next it
combine rows where the location and lot number matches.

Sub combinelots()

'clear sheet 2
With Sheets("Sheet2")
.Cells.ClearContents
End With

'copy data from sheet1 to sheet 2
Sheets("Sheet1").Cells.Copy _
Destination:=Sheets("Sheet2").Cells

With Sheets("Sheet2")
Columns("C:C").Cut
Columns("A:A").Insert Shift:=xlToRight

LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Set SortRange = .Rows("2:" & LastRow)

SortRange.Sort _
Key1:=.Range("A2"), _
Order1:=xlAscending, _
Key2:=.Range("B2"), _
Order2:=xlAscending, _
Header:=xlGuess

RowCount = 2
Do While .Range("A" & RowCount) < ""
If .Range("A" & RowCount) = _
.Range("A" & (RowCount + 1)) And _
.Range("B" & RowCount) = _
.Range("B" & (RowCount + 1)) Then

.Range("C" & RowCount) = _
.Range("C" & RowCount) + _
.Range("C" & (RowCount + 1))
.Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End With

End Sub


"RJB" wrote:

32,000 lines of data on my 'Master Sheet':

LOT# # PASSES LOCATION
====================================

(plus many more columns of stuff I'm not worried about now)


There are 15,500 different lots. There are 12 different locations. Each lot
is only in one location.

BUT, there can be many passes for one lot. (SAMPLE DATA AT BOTTOM)

I used (SUM(IF(FREQUENCY(LOT1:LOT32000,LOT1:LOT32000)0,1 ,) to calculate the
15,500.

Here's what I'd like:

LOCATION # (DISCRETE)LOTS IN LOCATION SUM # PASSES


How? How how how?



=======SAMPLE DATA===========
LOT #PASSES LOCATION
100 1 B
101 1 A
101 2 A
102 1 C
103 1 C


OUTPUT
LOCATION # (DISCRETE)LOTS IN LOCATION SUM # PASSES
A 1
3
B 1
1
C 2
2