ExcelBanter - Multiple Lookups with some Calculations and Sum Frequencies

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Multiple Lookups with some Calculations and Sum Frequencies (https://www.excelbanter.com/excel-discussion-misc-queries/173095-multiple-lookups-some-calculations-sum-frequencies.html)

Multiple Lookups with some Calculations and Sum Frequencies

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

Multiple Lookups with some Calculations and Sum Frequencies

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

Multiple Lookups with some Calculations and Sum Frequencies

Thanks. That gives me a COUNT of ROWS of lots - NOT of "Discrete" lots. For
instance, your Pivot Table gives me 5 lots in Location A - there's only one.
Lot 5.

And, to get fancier, I actually need to separate out those with a "Pass" of
"1" from the rest, but that's a different issue (on the master sheet, it's a
"COUNTIF" deal.)

Open to more thoughts!

"Herbert Seidenberg" wrote:

Try Pivot Table
http://www.freefilehosting.net/download/3adka

Multiple Lookups with some Calculations and Sum Frequencies

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

Multiple Lookups with some Calculations and Sum Frequencies

There are many trade-offs in life. Macros are just one of them. Macros have
two great advantaggs. First is that they can repeatively perform the same
operation over and over again without making mistakes. Second they save time.

People are amazed after the first time they use a macro how much they can
really do. For novices, I recommend only using simple macros that are under
20 instructions and make sure they understand wht the macro is actually doing.

Don't be afraid to ask questions when you don't understand sometthing!

"RJB" wrote:

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

Multiple Lookups with some Calculations and Sum Frequencies

"Discrete Lot" count can be easily added
to the Pivot Table.
To show just "1" in the "Pass" field,
just click on the arrow and select "1".
http://www.freefilehosting.net/download/3aeaj