I have a worksheet with 3 columns of part numbers. What I'm looking for is to
be able to sort the data and return a count for each part number. The same
part number could appear in any of the three columns. I'm new at this, and
any help would be greatly appreciated. Thank you in advance.

Eample: Part# 1 Part#2 Part#3
IHZ-1590 IHZ-1480 IHZ-1599
IHZ-1599 IHZ-1599 IHZ-1590

IHZ-1480 = 1
IHZ-1590 = 2
IHZ-1599 = 3

If you are only looking to get the count then use worksheet function COUNTIF()

With partnumber in cell D1

=COUNTIF(A:D,D1)

'VBA
Application.countif(Range("A:C"),Range("D1"))

If this post helps click Yes
---------------
Jacob Skaria


"Dawna" wrote:

I have a worksheet with 3 columns of part numbers. What I'm looking for is to
be able to sort the data and return a count for each part number. The same
part number could appear in any of the three columns. I'm new at this, and
any help would be greatly appreciated. Thank you in advance.

Eample: Part# 1 Part#2 Part#3
IHZ-1590 IHZ-1480 IHZ-1599
IHZ-1599 IHZ-1599 IHZ-1590

IHZ-1480 = 1
IHZ-1590 = 2
IHZ-1599 = 3

The following code will take data from one sheet and put the reults in a
2nd sheet. Change the Sheet names in the SrcSht and DestSht as
required.


Sub GetCounts()

Set SrcSht = Sheets("sheet1")
Set DestSht = Sheets("sheet2")
'put header row into destination sheet
With DestSht
Range("A1") = "Part#"
Range("B1") = "Quant"
Newrow = 2
End With

With SrcSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
For ColCount = 1 To 3
PartNo = .Cells(RowCount, ColCount)
'if not blank
If PartNo < "" Then
'lookup Part number in Destination sheet
With DestSht
Set c = .Columns("A").Find(what:=PartNo, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Range("A" & Newrow) = PartNo
Range("B" & Newrow) = 1
Newrow = Newrow + 1
Else
'add one to the quantity
Range("B" & c.Row) = _
Range("B" & c.Row) + 1
End If
End With
End If
Next ColCount
Next RowCount
End With

With DestSht
'sort the results
LastRow = Newrow - 1
Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending
End With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=152114

Microsoft Office Help

I posted the response at both Microsoft and the Codecage because some of my
responses at Theodecasge haven't been posted at the microsoft site.

The code below take the data from a source sheet and puts the Results on the
Destinaton sheet. Change the SrcSht and DestSht as required.

Sub GetCounts()

Set SrcSht = Sheets("sheet1")
Set DestSht = Sheets("sheet2")
'put header row into destination sheet
With DestSht
.Range("A1") = "Part#"
.Range("B1") = "Quant"
Newrow = 2
End With

With SrcSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
For ColCount = 1 To 3
PartNo = .Cells(RowCount, ColCount)
'if not blank
If PartNo < "" Then
'lookup Part number in Destination sheet
With DestSht
Set c = .Columns("A").Find(what:=PartNo, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & Newrow) = PartNo
.Range("B" & Newrow) = 1
Newrow = Newrow + 1
Else
'add one to the quantity
.Range("B" & c.Row) = _
.Range("B" & c.Row) + 1
End If
End With
End If
Next ColCount
Next RowCount
End With

With DestSht
'sort the results
LastRow = Newrow - 1
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending
End With
End Sub


"Jacob Skaria" wrote:

If you are only looking to get the count then use worksheet function COUNTIF()

With partnumber in cell D1

=COUNTIF(A:D,D1)

'VBA
Application.countif(Range("A:C"),Range("D1"))

If this post helps click Yes
---------------
Jacob Skaria


"Dawna" wrote:

I have a worksheet with 3 columns of part numbers. What I'm looking for is to
be able to sort the data and return a count for each part number. The same
part number could appear in any of the three columns. I'm new at this, and
any help would be greatly appreciated. Thank you in advance.

Eample: Part# 1 Part#2 Part#3
IHZ-1590 IHZ-1480 IHZ-1599
IHZ-1599 IHZ-1599 IHZ-1590

IHZ-1480 = 1
IHZ-1590 = 2
IHZ-1599 = 3

I guess more specifically what I'm looking for is to sort multiple rows in
these columns. (This data will be pulled monthly) I would like the code to
pull all the part numbers used over that period of time, and return a count.
Is there a link to information on how to write this code in VB?

"Jacob Skaria" wrote:

If you are only looking to get the count then use worksheet function COUNTIF()

With partnumber in cell D1

=COUNTIF(A:D,D1)

'VBA
Application.countif(Range("A:C"),Range("D1"))

If this post helps click Yes
---------------
Jacob Skaria


"Dawna" wrote:

I have a worksheet with 3 columns of part numbers. What I'm looking for is to
be able to sort the data and return a count for each part number. The same
part number could appear in any of the three columns. I'm new at this, and
any help would be greatly appreciated. Thank you in advance.

Eample: Part# 1 Part#2 Part#3
IHZ-1590 IHZ-1480 IHZ-1599
IHZ-1599 IHZ-1599 IHZ-1590

IHZ-1480 = 1
IHZ-1590 = 2
IHZ-1599 = 3

Sample data posted do not have the date. Explain how your data is arranged so
that it would be easy (for anyone) to suggest a solution..

If this post helps click Yes
---------------
Jacob Skaria


"Dawna" wrote:

I guess more specifically what I'm looking for is to sort multiple rows in
these columns. (This data will be pulled monthly) I would like the code to
pull all the part numbers used over that period of time, and return a count.
Is there a link to information on how to write this code in VB?

"Jacob Skaria" wrote:

If you are only looking to get the count then use worksheet function COUNTIF()

With partnumber in cell D1

=COUNTIF(A:D,D1)

'VBA
Application.countif(Range("A:C"),Range("D1"))

If this post helps click Yes
---------------
Jacob Skaria


"Dawna" wrote:

I have a worksheet with 3 columns of part numbers. What I'm looking for is to
be able to sort the data and return a count for each part number. The same
part number could appear in any of the three columns. I'm new at this, and
any help would be greatly appreciated. Thank you in advance.

Eample: Part# 1 Part#2 Part#3
IHZ-1590 IHZ-1480 IHZ-1599
IHZ-1599 IHZ-1599 IHZ-1590

IHZ-1480 = 1
IHZ-1590 = 2
IHZ-1599 = 3

Thank you both for replies. The codes does exactly what I needed. :)
Have a great day!

"Joel" wrote:

I posted the response at both Microsoft and the Codecage because some of my
responses at Theodecasge haven't been posted at the microsoft site.

The code below take the data from a source sheet and puts the Results on the
Destinaton sheet. Change the SrcSht and DestSht as required.

Sub GetCounts()

Set SrcSht = Sheets("sheet1")
Set DestSht = Sheets("sheet2")
'put header row into destination sheet
With DestSht
.Range("A1") = "Part#"
.Range("B1") = "Quant"
Newrow = 2
End With

With SrcSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
For ColCount = 1 To 3
PartNo = .Cells(RowCount, ColCount)
'if not blank
If PartNo < "" Then
'lookup Part number in Destination sheet
With DestSht
Set c = .Columns("A").Find(what:=PartNo, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("A" & Newrow) = PartNo
.Range("B" & Newrow) = 1
Newrow = Newrow + 1
Else
'add one to the quantity
.Range("B" & c.Row) = _
.Range("B" & c.Row) + 1
End If
End With
End If
Next ColCount
Next RowCount
End With

With DestSht
'sort the results
LastRow = Newrow - 1
.Rows("1:" & LastRow).Sort _
header:=xlYes, _
key1:=.Range("A1"), _
order1:=xlAscending
End With
End Sub


"Jacob Skaria" wrote:

If you are only looking to get the count then use worksheet function COUNTIF()

With partnumber in cell D1

=COUNTIF(A:D,D1)

'VBA
Application.countif(Range("A:C"),Range("D1"))

If this post helps click Yes
---------------
Jacob Skaria


"Dawna" wrote:

I have a worksheet with 3 columns of part numbers. What I'm looking for is to
be able to sort the data and return a count for each part number. The same
part number could appear in any of the three columns. I'm new at this, and
any help would be greatly appreciated. Thank you in advance.

Eample: Part# 1 Part#2 Part#3
IHZ-1590 IHZ-1480 IHZ-1599
IHZ-1599 IHZ-1599 IHZ-1590

IHZ-1480 = 1
IHZ-1590 = 2
IHZ-1599 = 3