Hi there,
Can someone help me, I have a problem in that i have a range of data with
part numbers down column A, with various data in columnsB,C,D. The part
numbers could appear one or more times, what i want to do is detailed below.
The range extends to row 1000.


EXCEL: BEFORE
A B C D E
1 ABA 50
2 ABA 20
3 ABA 10
4 ABA 50

EXCEL:AFTER
A B C D E
1 ABA 20 50 10 50

regards Harry

Try this. Select your entire data list (including a single field name row)
and run this procedure. It outputs the new data a few rows below the
existing table, so make sure you have room there....
--
Jay

Public Sub consolidatePartNumber()
'Select your data list, including the field name row,
'then run this procedure.
Dim cleanTable() As Variant
Dim partNumbers As Collection

Set Rng = Selection

If Not Rng Is Nothing Then
Set Rng2 = Rng.Columns(1)
Else
Exit Sub
End If

Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)

'Get unique part numbers
Set partNumbers = New Collection
On Error Resume Next
For Each pn In Rng2.Cells
With pn
partNumbers.Add .Value, CStr(.Value)
End With
Next 'pn
On Error GoTo 0

'Consolidate data for each part number and store in array cleanTable
pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
For Each upn In partNumbers
i = i + 1
cleanTable(i, 1) = upn
For Each pn In Rng2.Cells
If pn.Value = upn Then
Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
For d = 1 To 4
If Not IsEmpty(data(1, d)) Then cleanTable(i, d + 1) =
data(1, d).Value
Next d
End If
Next 'pn
Next 'upn
imax = i

'Put results (cleanTable) below existing table.
Selection.Rows(1).Copy Destination:= _
Cells(Selection.Rows.Count + Selection.Row + 2, Selection.Column)
Cells(Selection.Rows.Count + Selection.Row + 2, Selection.Column).Select

For i = 1 To imax
For j = 0 To 4
ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
Next j
Next i

End Sub


"Big H" wrote:

> Hi there,
> Can someone help me, I have a problem in that i have a range of data with
> part numbers down column A, with various data in columnsB,C,D. The part
> numbers could appear one or more times, what i want to do is detailed below.
> The range extends to row 1000.
>
>
> EXCEL: BEFORE
> A B C D E
> 1 ABA 50
> 2 ABA 20
> 3 ABA 10
> 4 ABA 50
>
> EXCEL:AFTER
> A B C D E
> 1 ABA 20 50 10 50
>
> regards Harry
>
>
>

Hi Jay,

the code looks promising, however when i run it I keep getting a compile
error saying variables not defined.
regards Harry

"Jay" <(E-Mail Removed)> wrote in message
news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
> Try this. Select your entire data list (including a single field name
> row)
> and run this procedure. It outputs the new data a few rows below the
> existing table, so make sure you have room there....
> --
> Jay
>
> Public Sub consolidatePartNumber()
> 'Select your data list, including the field name row,
> 'then run this procedure.
> Dim cleanTable() As Variant
> Dim partNumbers As Collection
>
> Set Rng = Selection
>
> If Not Rng Is Nothing Then
> Set Rng2 = Rng.Columns(1)
> Else
> Exit Sub
> End If
>
> Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
> 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
>
> 'Get unique part numbers
> Set partNumbers = New Collection
> On Error Resume Next
> For Each pn In Rng2.Cells
> With pn
> partNumbers.Add .Value, CStr(.Value)
> End With
> Next 'pn
> On Error GoTo 0
>
> 'Consolidate data for each part number and store in array cleanTable
> pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
> For Each upn In partNumbers
> i = i + 1
> cleanTable(i, 1) = upn
> For Each pn In Rng2.Cells
> If pn.Value = upn Then
> Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
> For d = 1 To 4
> If Not IsEmpty(data(1, d)) Then cleanTable(i, d + 1) =
> data(1, d).Value
> Next d
> End If
> Next 'pn
> Next 'upn
> imax = i
>
> 'Put results (cleanTable) below existing table.
> Selection.Rows(1).Copy Destination:= _
> Cells(Selection.Rows.Count + Selection.Row + 2, Selection.Column)
> Cells(Selection.Rows.Count + Selection.Row + 2,
> Selection.Column).Select
>
> For i = 1 To imax
> For j = 0 To 4
> ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
> Next j
> Next i
>
> End Sub
>
>
> "Big H" wrote:
>
>> Hi there,
>> Can someone help me, I have a problem in that i have a range of data with
>> part numbers down column A, with various data in columnsB,C,D. The part
>> numbers could appear one or more times, what i want to do is detailed
>> below.
>> The range extends to row 1000.
>>
>>
>> EXCEL: BEFORE
>> A B C D E
>> 1 ABA 50
>> 2 ABA 20
>> 3 ABA 10
>> 4 ABA 50
>>
>> EXCEL:AFTER
>> A B C D E
>> 1 ABA 20 50 10 50
>>
>> regards Harry
>>
>>
>>

So very sorry, Harry. I had to step out unexpectedly. I'll take a look at
this right now...
--
Jay


"Big H" wrote:

> Hi Jay,
>
> the code looks promising, however when i run it I keep getting a compile
> error saying variables not defined.
> regards Harry
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
> > Try this. Select your entire data list (including a single field name
> > row)
> > and run this procedure. It outputs the new data a few rows below the
> > existing table, so make sure you have room there....
> > --
> > Jay
> >
> > Public Sub consolidatePartNumber()
> > 'Select your data list, including the field name row,
> > 'then run this procedure.
> > Dim cleanTable() As Variant
> > Dim partNumbers As Collection
> >
> > Set Rng = Selection
> >
> > If Not Rng Is Nothing Then
> > Set Rng2 = Rng.Columns(1)
> > Else
> > Exit Sub
> > End If
> >
> > Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
> > 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
> >
> > 'Get unique part numbers
> > Set partNumbers = New Collection
> > On Error Resume Next
> > For Each pn In Rng2.Cells
> > With pn
> > partNumbers.Add .Value, CStr(.Value)
> > End With
> > Next 'pn
> > On Error GoTo 0
> >
> > 'Consolidate data for each part number and store in array cleanTable
> > pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
> > For Each upn In partNumbers
> > i = i + 1
> > cleanTable(i, 1) = upn
> > For Each pn In Rng2.Cells
> > If pn.Value = upn Then
> > Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
> > For d = 1 To 4
> > If Not IsEmpty(data(1, d)) Then cleanTable(i, d + 1) =
> > data(1, d).Value
> > Next d
> > End If
> > Next 'pn
> > Next 'upn
> > imax = i
> >
> > 'Put results (cleanTable) below existing table.
> > Selection.Rows(1).Copy Destination:= _
> > Cells(Selection.Rows.Count + Selection.Row + 2, Selection.Column)
> > Cells(Selection.Rows.Count + Selection.Row + 2,
> > Selection.Column).Select
> >
> > For i = 1 To imax
> > For j = 0 To 4
> > ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
> > Next j
> > Next i
> >
> > End Sub
> >
> >
> > "Big H" wrote:
> >
> >> Hi there,
> >> Can someone help me, I have a problem in that i have a range of data with
> >> part numbers down column A, with various data in columnsB,C,D. The part
> >> numbers could appear one or more times, what i want to do is detailed
> >> below.
> >> The range extends to row 1000.
> >>
> >>
> >> EXCEL: BEFORE
> >> A B C D E
> >> 1 ABA 50
> >> 2 ABA 20
> >> 3 ABA 10
> >> 4 ABA 50
> >>
> >> EXCEL:AFTER
> >> A B C D E
> >> 1 ABA 20 50 10 50
> >>
> >> regards Harry
> >>
> >>
> >>
>
>
>

Hi Harry -

I can't duplicate the error you describe. So check the following:

1. Make sure you have the data range selected before you run the procudure.

2. During the failed compile, does VBA highlight any suspected statements ?
If so, which one(s) ?

3. What version of Excel do you have ?

I'll continue working on a more universal (environment-independent) version,
but it would help to know about the 3 items above.

Jay

"Big H" wrote:

> Hi Jay,
>
> the code looks promising, however when i run it I keep getting a compile
> error saying variables not defined.
> regards Harry
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
> > Try this. Select your entire data list (including a single field name
> > row)
> > and run this procedure. It outputs the new data a few rows below the
> > existing table, so make sure you have room there....
> > --
> > Jay
> >
> > Public Sub consolidatePartNumber()
> > 'Select your data list, including the field name row,
> > 'then run this procedure.
> > Dim cleanTable() As Variant
> > Dim partNumbers As Collection
> >
> > Set Rng = Selection
> >
> > If Not Rng Is Nothing Then
> > Set Rng2 = Rng.Columns(1)
> > Else
> > Exit Sub
> > End If
> >
> > Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
> > 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
> >
> > 'Get unique part numbers
> > Set partNumbers = New Collection
> > On Error Resume Next
> > For Each pn In Rng2.Cells
> > With pn
> > partNumbers.Add .Value, CStr(.Value)
> > End With
> > Next 'pn
> > On Error GoTo 0
> >
> > 'Consolidate data for each part number and store in array cleanTable
> > pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
> > For Each upn In partNumbers
> > i = i + 1
> > cleanTable(i, 1) = upn
> > For Each pn In Rng2.Cells
> > If pn.Value = upn Then
> > Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
> > For d = 1 To 4
> > If Not IsEmpty(data(1, d)) Then cleanTable(i, d + 1) =
> > data(1, d).Value
> > Next d
> > End If
> > Next 'pn
> > Next 'upn
> > imax = i
> >
> > 'Put results (cleanTable) below existing table.
> > Selection.Rows(1).Copy Destination:= _
> > Cells(Selection.Rows.Count + Selection.Row + 2, Selection.Column)
> > Cells(Selection.Rows.Count + Selection.Row + 2,
> > Selection.Column).Select
> >
> > For i = 1 To imax
> > For j = 0 To 4
> > ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
> > Next j
> > Next i
> >
> > End Sub
> >
> >
> > "Big H" wrote:
> >
> >> Hi there,
> >> Can someone help me, I have a problem in that i have a range of data with
> >> part numbers down column A, with various data in columnsB,C,D. The part
> >> numbers could appear one or more times, what i want to do is detailed
> >> below.
> >> The range extends to row 1000.
> >>
> >>
> >> EXCEL: BEFORE
> >> A B C D E
> >> 1 ABA 50
> >> 2 ABA 20
> >> 3 ABA 10
> >> 4 ABA 50
> >>
> >> EXCEL:AFTER
> >> A B C D E
> >> 1 ABA 20 50 10 50
> >>
> >> regards Harry
> >>
> >>
> >>
>
>
>

Hi Jay
1. I am selecting the range
2.variable not defined error, for the following: Rng,
Rng2,pn,pc,upn,i,data,d
3. Excel xp (I think its 2003)

"Jay" <(E-Mail Removed)> wrote in message
news:3F19DB62-C63B-4DC6-AE31-(E-Mail Removed)...
> Hi Harry -
>
> I can't duplicate the error you describe. So check the following:
>
> 1. Make sure you have the data range selected before you run the
> procudure.
>
> 2. During the failed compile, does VBA highlight any suspected statements
> ?
> If so, which one(s) ?
>
> 3. What version of Excel do you have ?
>
> I'll continue working on a more universal (environment-independent)
> version,
> but it would help to know about the 3 items above.
>
> Jay
>
> "Big H" wrote:
>
>> Hi Jay,
>>
>> the code looks promising, however when i run it I keep getting a compile
>> error saying variables not defined.
>> regards Harry
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
>> > Try this. Select your entire data list (including a single field name
>> > row)
>> > and run this procedure. It outputs the new data a few rows below the
>> > existing table, so make sure you have room there....
>> > --
>> > Jay
>> >
>> > Public Sub consolidatePartNumber()
>> > 'Select your data list, including the field name row,
>> > 'then run this procedure.
>> > Dim cleanTable() As Variant
>> > Dim partNumbers As Collection
>> >
>> > Set Rng = Selection
>> >
>> > If Not Rng Is Nothing Then
>> > Set Rng2 = Rng.Columns(1)
>> > Else
>> > Exit Sub
>> > End If
>> >
>> > Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
>> > 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
>> >
>> > 'Get unique part numbers
>> > Set partNumbers = New Collection
>> > On Error Resume Next
>> > For Each pn In Rng2.Cells
>> > With pn
>> > partNumbers.Add .Value, CStr(.Value)
>> > End With
>> > Next 'pn
>> > On Error GoTo 0
>> >
>> > 'Consolidate data for each part number and store in array cleanTable
>> > pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
>> > For Each upn In partNumbers
>> > i = i + 1
>> > cleanTable(i, 1) = upn
>> > For Each pn In Rng2.Cells
>> > If pn.Value = upn Then
>> > Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
>> > For d = 1 To 4
>> > If Not IsEmpty(data(1, d)) Then cleanTable(i, d + 1)
>> > =
>> > data(1, d).Value
>> > Next d
>> > End If
>> > Next 'pn
>> > Next 'upn
>> > imax = i
>> >
>> > 'Put results (cleanTable) below existing table.
>> > Selection.Rows(1).Copy Destination:= _
>> > Cells(Selection.Rows.Count + Selection.Row + 2,
>> > Selection.Column)
>> > Cells(Selection.Rows.Count + Selection.Row + 2,
>> > Selection.Column).Select
>> >
>> > For i = 1 To imax
>> > For j = 0 To 4
>> > ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
>> > Next j
>> > Next i
>> >
>> > End Sub
>> >
>> >
>> > "Big H" wrote:
>> >
>> >> Hi there,
>> >> Can someone help me, I have a problem in that i have a range of data
>> >> with
>> >> part numbers down column A, with various data in columnsB,C,D. The
>> >> part
>> >> numbers could appear one or more times, what i want to do is detailed
>> >> below.
>> >> The range extends to row 1000.
>> >>
>> >>
>> >> EXCEL: BEFORE
>> >> A B C D
>> >> E
>> >> 1 ABA 50
>> >> 2 ABA 20
>> >> 3 ABA 10
>> >> 4 ABA
>> >> 50
>> >>
>> >> EXCEL:AFTER
>> >> A B C D
>> >> E
>> >> 1 ABA 20 50 10 50
>> >>
>> >> regards Harry
>> >>
>> >>
>> >>
>>
>>
>>

Look for the line "Option Explicit" at or near the top of the module and
temporarily delete it or disable it by preceeding it with a single quotation
mark ('). Then run the code again.

If Option Explicit is not present, let me know.
--
Jay


"Big H" wrote:

> Hi Jay
> 1. I am selecting the range
> 2.variable not defined error, for the following: Rng,
> Rng2,pn,pc,upn,i,data,d
> 3. Excel xp (I think its 2003)
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:3F19DB62-C63B-4DC6-AE31-(E-Mail Removed)...
> > Hi Harry -
> >
> > I can't duplicate the error you describe. So check the following:
> >
> > 1. Make sure you have the data range selected before you run the
> > procudure.
> >
> > 2. During the failed compile, does VBA highlight any suspected statements
> > ?
> > If so, which one(s) ?
> >
> > 3. What version of Excel do you have ?
> >
> > I'll continue working on a more universal (environment-independent)
> > version,
> > but it would help to know about the 3 items above.
> >
> > Jay
> >
> > "Big H" wrote:
> >
> >> Hi Jay,
> >>
> >> the code looks promising, however when i run it I keep getting a compile
> >> error saying variables not defined.
> >> regards Harry
> >>
> >> "Jay" <(E-Mail Removed)> wrote in message
> >> news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
> >> > Try this. Select your entire data list (including a single field name
> >> > row)
> >> > and run this procedure. It outputs the new data a few rows below the
> >> > existing table, so make sure you have room there....
> >> > --
> >> > Jay
> >> >
> >> > Public Sub consolidatePartNumber()
> >> > 'Select your data list, including the field name row,
> >> > 'then run this procedure.
> >> > Dim cleanTable() As Variant
> >> > Dim partNumbers As Collection
> >> >
> >> > Set Rng = Selection
> >> >
> >> > If Not Rng Is Nothing Then
> >> > Set Rng2 = Rng.Columns(1)
> >> > Else
> >> > Exit Sub
> >> > End If
> >> >
> >> > Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
> >> > 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
> >> >
> >> > 'Get unique part numbers
> >> > Set partNumbers = New Collection
> >> > On Error Resume Next
> >> > For Each pn In Rng2.Cells
> >> > With pn
> >> > partNumbers.Add .Value, CStr(.Value)
> >> > End With
> >> > Next 'pn
> >> > On Error GoTo 0
> >> >
> >> > 'Consolidate data for each part number and store in array cleanTable
> >> > pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
> >> > For Each upn In partNumbers
> >> > i = i + 1
> >> > cleanTable(i, 1) = upn
> >> > For Each pn In Rng2.Cells
> >> > If pn.Value = upn Then
> >> > Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
> >> > For d = 1 To 4
> >> > If Not IsEmpty(data(1, d)) Then cleanTable(i, d + 1)
> >> > =
> >> > data(1, d).Value
> >> > Next d
> >> > End If
> >> > Next 'pn
> >> > Next 'upn
> >> > imax = i
> >> >
> >> > 'Put results (cleanTable) below existing table.
> >> > Selection.Rows(1).Copy Destination:= _
> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
> >> > Selection.Column)
> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
> >> > Selection.Column).Select
> >> >
> >> > For i = 1 To imax
> >> > For j = 0 To 4
> >> > ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
> >> > Next j
> >> > Next i
> >> >
> >> > End Sub
> >> >
> >> >
> >> > "Big H" wrote:
> >> >
> >> >> Hi there,
> >> >> Can someone help me, I have a problem in that i have a range of data
> >> >> with
> >> >> part numbers down column A, with various data in columnsB,C,D. The
> >> >> part
> >> >> numbers could appear one or more times, what i want to do is detailed
> >> >> below.
> >> >> The range extends to row 1000.
> >> >>
> >> >>
> >> >> EXCEL: BEFORE
> >> >> A B C D
> >> >> E
> >> >> 1 ABA 50
> >> >> 2 ABA 20
> >> >> 3 ABA 10
> >> >> 4 ABA
> >> >> 50
> >> >>
> >> >> EXCEL:AFTER
> >> >> A B C D
> >> >> E
> >> >> 1 ABA 20 50 10 50
> >> >>
> >> >> regards Harry
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Jay,

It works perfect thanks. Why does it not work when option explicit is on?
regards from Scotland

"Jay" <(E-Mail Removed)> wrote in message
news:9BB05323-9201-4070-BFEB-(E-Mail Removed)...
> Look for the line "Option Explicit" at or near the top of the module and
> temporarily delete it or disable it by preceeding it with a single
> quotation
> mark ('). Then run the code again.
>
> If Option Explicit is not present, let me know.
> --
> Jay
>
>
> "Big H" wrote:
>
>> Hi Jay
>> 1. I am selecting the range
>> 2.variable not defined error, for the following: Rng,
>> Rng2,pn,pc,upn,i,data,d
>> 3. Excel xp (I think its 2003)
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:3F19DB62-C63B-4DC6-AE31-(E-Mail Removed)...
>> > Hi Harry -
>> >
>> > I can't duplicate the error you describe. So check the following:
>> >
>> > 1. Make sure you have the data range selected before you run the
>> > procudure.
>> >
>> > 2. During the failed compile, does VBA highlight any suspected
>> > statements
>> > ?
>> > If so, which one(s) ?
>> >
>> > 3. What version of Excel do you have ?
>> >
>> > I'll continue working on a more universal (environment-independent)
>> > version,
>> > but it would help to know about the 3 items above.
>> >
>> > Jay
>> >
>> > "Big H" wrote:
>> >
>> >> Hi Jay,
>> >>
>> >> the code looks promising, however when i run it I keep getting a
>> >> compile
>> >> error saying variables not defined.
>> >> regards Harry
>> >>
>> >> "Jay" <(E-Mail Removed)> wrote in message
>> >> news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
>> >> > Try this. Select your entire data list (including a single field
>> >> > name
>> >> > row)
>> >> > and run this procedure. It outputs the new data a few rows below
>> >> > the
>> >> > existing table, so make sure you have room there....
>> >> > --
>> >> > Jay
>> >> >
>> >> > Public Sub consolidatePartNumber()
>> >> > 'Select your data list, including the field name row,
>> >> > 'then run this procedure.
>> >> > Dim cleanTable() As Variant
>> >> > Dim partNumbers As Collection
>> >> >
>> >> > Set Rng = Selection
>> >> >
>> >> > If Not Rng Is Nothing Then
>> >> > Set Rng2 = Rng.Columns(1)
>> >> > Else
>> >> > Exit Sub
>> >> > End If
>> >> >
>> >> > Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
>> >> > 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
>> >> >
>> >> > 'Get unique part numbers
>> >> > Set partNumbers = New Collection
>> >> > On Error Resume Next
>> >> > For Each pn In Rng2.Cells
>> >> > With pn
>> >> > partNumbers.Add .Value, CStr(.Value)
>> >> > End With
>> >> > Next 'pn
>> >> > On Error GoTo 0
>> >> >
>> >> > 'Consolidate data for each part number and store in array
>> >> > cleanTable
>> >> > pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
>> >> > For Each upn In partNumbers
>> >> > i = i + 1
>> >> > cleanTable(i, 1) = upn
>> >> > For Each pn In Rng2.Cells
>> >> > If pn.Value = upn Then
>> >> > Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
>> >> > For d = 1 To 4
>> >> > If Not IsEmpty(data(1, d)) Then cleanTable(i, d +
>> >> > 1)
>> >> > =
>> >> > data(1, d).Value
>> >> > Next d
>> >> > End If
>> >> > Next 'pn
>> >> > Next 'upn
>> >> > imax = i
>> >> >
>> >> > 'Put results (cleanTable) below existing table.
>> >> > Selection.Rows(1).Copy Destination:= _
>> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
>> >> > Selection.Column)
>> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
>> >> > Selection.Column).Select
>> >> >
>> >> > For i = 1 To imax
>> >> > For j = 0 To 4
>> >> > ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
>> >> > Next j
>> >> > Next i
>> >> >
>> >> > End Sub
>> >> >
>> >> >
>> >> > "Big H" wrote:
>> >> >
>> >> >> Hi there,
>> >> >> Can someone help me, I have a problem in that i have a range of
>> >> >> data
>> >> >> with
>> >> >> part numbers down column A, with various data in columnsB,C,D. The
>> >> >> part
>> >> >> numbers could appear one or more times, what i want to do is
>> >> >> detailed
>> >> >> below.
>> >> >> The range extends to row 1000.
>> >> >>
>> >> >>
>> >> >> EXCEL: BEFORE
>> >> >> A B C D
>> >> >> E
>> >> >> 1 ABA 50
>> >> >> 2 ABA 20
>> >> >> 3 ABA 10
>> >> >> 4 ABA
>> >> >> 50
>> >> >>
>> >> >> EXCEL:AFTER
>> >> >> A B C D
>> >> >> E
>> >> >> 1 ABA 20 50 10 50
>> >> >>
>> >> >> regards Harry
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Excellent -

An Option Explicit statement keeps spelling errors from creating problems in
code by forcing discipline on the programmer. With Option Explicit, a
program won't run unless a variable that is used in the code is also declared
at the start of the procedure, e.g., Dim var1 as range.

Now, if all of your variables are declared (that is, rigourously 'defined'),
then if you accidentally type 'vaar1' instead of 'var1' ("vaar1 =
cells(1,7)") deep in the bowels of your program, excel will not run the
program, but will give you the warning that you received ("variable not
defined). If Option Explicit is NOT set in this example, VB would assign a
cells(1,7) to vaar1 and var1 would have no value - although the programmer
would think that var1 was assigned the range cells (1,7).

That will create a whole bunch of problems later in programming if you're
assuming one thing when it it not the case. The sentinal protects you from
this.

Note though, that this forces a different programming style. In a rapid
application development environment like VBA, most of us don't know what
variables we'll be using until we start programming. So, it's often
programmer's choice as to whether it is used.

Have a great day, Scotland,
--

Jay (Alaska, USA)


"Big H" wrote:

> Jay,
>
> It works perfect thanks. Why does it not work when option explicit is on?
> regards from Scotland
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:9BB05323-9201-4070-BFEB-(E-Mail Removed)...
> > Look for the line "Option Explicit" at or near the top of the module and
> > temporarily delete it or disable it by preceeding it with a single
> > quotation
> > mark ('). Then run the code again.
> >
> > If Option Explicit is not present, let me know.
> > --
> > Jay
> >
> >
> > "Big H" wrote:
> >
> >> Hi Jay
> >> 1. I am selecting the range
> >> 2.variable not defined error, for the following: Rng,
> >> Rng2,pn,pc,upn,i,data,d
> >> 3. Excel xp (I think its 2003)
> >>
> >> "Jay" <(E-Mail Removed)> wrote in message
> >> news:3F19DB62-C63B-4DC6-AE31-(E-Mail Removed)...
> >> > Hi Harry -
> >> >
> >> > I can't duplicate the error you describe. So check the following:
> >> >
> >> > 1. Make sure you have the data range selected before you run the
> >> > procudure.
> >> >
> >> > 2. During the failed compile, does VBA highlight any suspected
> >> > statements
> >> > ?
> >> > If so, which one(s) ?
> >> >
> >> > 3. What version of Excel do you have ?
> >> >
> >> > I'll continue working on a more universal (environment-independent)
> >> > version,
> >> > but it would help to know about the 3 items above.
> >> >
> >> > Jay
> >> >
> >> > "Big H" wrote:
> >> >
> >> >> Hi Jay,
> >> >>
> >> >> the code looks promising, however when i run it I keep getting a
> >> >> compile
> >> >> error saying variables not defined.
> >> >> regards Harry
> >> >>
> >> >> "Jay" <(E-Mail Removed)> wrote in message
> >> >> news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
> >> >> > Try this. Select your entire data list (including a single field
> >> >> > name
> >> >> > row)
> >> >> > and run this procedure. It outputs the new data a few rows below
> >> >> > the
> >> >> > existing table, so make sure you have room there....
> >> >> > --
> >> >> > Jay
> >> >> >
> >> >> > Public Sub consolidatePartNumber()
> >> >> > 'Select your data list, including the field name row,
> >> >> > 'then run this procedure.
> >> >> > Dim cleanTable() As Variant
> >> >> > Dim partNumbers As Collection
> >> >> >
> >> >> > Set Rng = Selection
> >> >> >
> >> >> > If Not Rng Is Nothing Then
> >> >> > Set Rng2 = Rng.Columns(1)
> >> >> > Else
> >> >> > Exit Sub
> >> >> > End If
> >> >> >
> >> >> > Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
> >> >> > 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
> >> >> >
> >> >> > 'Get unique part numbers
> >> >> > Set partNumbers = New Collection
> >> >> > On Error Resume Next
> >> >> > For Each pn In Rng2.Cells
> >> >> > With pn
> >> >> > partNumbers.Add .Value, CStr(.Value)
> >> >> > End With
> >> >> > Next 'pn
> >> >> > On Error GoTo 0
> >> >> >
> >> >> > 'Consolidate data for each part number and store in array
> >> >> > cleanTable
> >> >> > pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i = 0
> >> >> > For Each upn In partNumbers
> >> >> > i = i + 1
> >> >> > cleanTable(i, 1) = upn
> >> >> > For Each pn In Rng2.Cells
> >> >> > If pn.Value = upn Then
> >> >> > Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
> >> >> > For d = 1 To 4
> >> >> > If Not IsEmpty(data(1, d)) Then cleanTable(i, d +
> >> >> > 1)
> >> >> > =
> >> >> > data(1, d).Value
> >> >> > Next d
> >> >> > End If
> >> >> > Next 'pn
> >> >> > Next 'upn
> >> >> > imax = i
> >> >> >
> >> >> > 'Put results (cleanTable) below existing table.
> >> >> > Selection.Rows(1).Copy Destination:= _
> >> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
> >> >> > Selection.Column)
> >> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
> >> >> > Selection.Column).Select
> >> >> >
> >> >> > For i = 1 To imax
> >> >> > For j = 0 To 4
> >> >> > ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
> >> >> > Next j
> >> >> > Next i
> >> >> >
> >> >> > End Sub
> >> >> >
> >> >> >
> >> >> > "Big H" wrote:
> >> >> >
> >> >> >> Hi there,
> >> >> >> Can someone help me, I have a problem in that i have a range of
> >> >> >> data
> >> >> >> with
> >> >> >> part numbers down column A, with various data in columnsB,C,D. The
> >> >> >> part
> >> >> >> numbers could appear one or more times, what i want to do is
> >> >> >> detailed
> >> >> >> below.
> >> >> >> The range extends to row 1000.
> >> >> >>
> >> >> >>
> >> >> >> EXCEL: BEFORE
> >> >> >> A B C D
> >> >> >> E
> >> >> >> 1 ABA 50
> >> >> >> 2 ABA 20
> >> >> >> 3 ABA 10
> >> >> >> 4 ABA
> >> >> >> 50
> >> >> >>
> >> >> >> EXCEL:AFTER
> >> >> >> A B C D
> >> >> >> E
> >> >> >> 1 ABA 20 50 10 50
> >> >> >>
> >> >> >> regards Harry
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Jay,
sorry for being a nuisance, can you tell me what paramaters i would change
if i wanted to include more columns. As most times I would use more columns
than the example I have given.

regards Harry

"Jay" <(E-Mail Removed)> wrote in message
news:78296F5C-B2DF-41E4-BB08-(E-Mail Removed)...
> Excellent -
>
> An Option Explicit statement keeps spelling errors from creating problems
> in
> code by forcing discipline on the programmer. With Option Explicit, a
> program won't run unless a variable that is used in the code is also
> declared
> at the start of the procedure, e.g., Dim var1 as range.
>
> Now, if all of your variables are declared (that is, rigourously
> 'defined'),
> then if you accidentally type 'vaar1' instead of 'var1' ("vaar1 =
> cells(1,7)") deep in the bowels of your program, excel will not run the
> program, but will give you the warning that you received ("variable not
> defined). If Option Explicit is NOT set in this example, VB would assign
> a
> cells(1,7) to vaar1 and var1 would have no value - although the programmer
> would think that var1 was assigned the range cells (1,7).
>
> That will create a whole bunch of problems later in programming if you're
> assuming one thing when it it not the case. The sentinal protects you
> from
> this.
>
> Note though, that this forces a different programming style. In a rapid
> application development environment like VBA, most of us don't know what
> variables we'll be using until we start programming. So, it's often
> programmer's choice as to whether it is used.
>
> Have a great day, Scotland,
> --
>
> Jay (Alaska, USA)
>
>
> "Big H" wrote:
>
>> Jay,
>>
>> It works perfect thanks. Why does it not work when option explicit is on?
>> regards from Scotland
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:9BB05323-9201-4070-BFEB-(E-Mail Removed)...
>> > Look for the line "Option Explicit" at or near the top of the module
>> > and
>> > temporarily delete it or disable it by preceeding it with a single
>> > quotation
>> > mark ('). Then run the code again.
>> >
>> > If Option Explicit is not present, let me know.
>> > --
>> > Jay
>> >
>> >
>> > "Big H" wrote:
>> >
>> >> Hi Jay
>> >> 1. I am selecting the range
>> >> 2.variable not defined error, for the following: Rng,
>> >> Rng2,pn,pc,upn,i,data,d
>> >> 3. Excel xp (I think its 2003)
>> >>
>> >> "Jay" <(E-Mail Removed)> wrote in message
>> >> news:3F19DB62-C63B-4DC6-AE31-(E-Mail Removed)...
>> >> > Hi Harry -
>> >> >
>> >> > I can't duplicate the error you describe. So check the following:
>> >> >
>> >> > 1. Make sure you have the data range selected before you run the
>> >> > procudure.
>> >> >
>> >> > 2. During the failed compile, does VBA highlight any suspected
>> >> > statements
>> >> > ?
>> >> > If so, which one(s) ?
>> >> >
>> >> > 3. What version of Excel do you have ?
>> >> >
>> >> > I'll continue working on a more universal (environment-independent)
>> >> > version,
>> >> > but it would help to know about the 3 items above.
>> >> >
>> >> > Jay
>> >> >
>> >> > "Big H" wrote:
>> >> >
>> >> >> Hi Jay,
>> >> >>
>> >> >> the code looks promising, however when i run it I keep getting a
>> >> >> compile
>> >> >> error saying variables not defined.
>> >> >> regards Harry
>> >> >>
>> >> >> "Jay" <(E-Mail Removed)> wrote in message
>> >> >> news:01868BAC-0321-4F5A-8DE0-(E-Mail Removed)...
>> >> >> > Try this. Select your entire data list (including a single field
>> >> >> > name
>> >> >> > row)
>> >> >> > and run this procedure. It outputs the new data a few rows below
>> >> >> > the
>> >> >> > existing table, so make sure you have room there....
>> >> >> > --
>> >> >> > Jay
>> >> >> >
>> >> >> > Public Sub consolidatePartNumber()
>> >> >> > 'Select your data list, including the field name row,
>> >> >> > 'then run this procedure.
>> >> >> > Dim cleanTable() As Variant
>> >> >> > Dim partNumbers As Collection
>> >> >> >
>> >> >> > Set Rng = Selection
>> >> >> >
>> >> >> > If Not Rng Is Nothing Then
>> >> >> > Set Rng2 = Rng.Columns(1)
>> >> >> > Else
>> >> >> > Exit Sub
>> >> >> > End If
>> >> >> >
>> >> >> > Set Rng2 = Rng2.Offset(1).Resize(Rng2.Rows.Count - 1)
>> >> >> > 'Set Rng2 = Rng2.SpecialCells(xlCellTypeVisible)
>> >> >> >
>> >> >> > 'Get unique part numbers
>> >> >> > Set partNumbers = New Collection
>> >> >> > On Error Resume Next
>> >> >> > For Each pn In Rng2.Cells
>> >> >> > With pn
>> >> >> > partNumbers.Add .Value, CStr(.Value)
>> >> >> > End With
>> >> >> > Next 'pn
>> >> >> > On Error GoTo 0
>> >> >> >
>> >> >> > 'Consolidate data for each part number and store in array
>> >> >> > cleanTable
>> >> >> > pc = partNumbers.Count: ReDim cleanTable(1 To pc, 1 To 5): i =
>> >> >> > 0
>> >> >> > For Each upn In partNumbers
>> >> >> > i = i + 1
>> >> >> > cleanTable(i, 1) = upn
>> >> >> > For Each pn In Rng2.Cells
>> >> >> > If pn.Value = upn Then
>> >> >> > Set data = Range(pn.Offset(0, 1), pn.Offset(0, 4))
>> >> >> > For d = 1 To 4
>> >> >> > If Not IsEmpty(data(1, d)) Then cleanTable(i,
>> >> >> > d +
>> >> >> > 1)
>> >> >> > =
>> >> >> > data(1, d).Value
>> >> >> > Next d
>> >> >> > End If
>> >> >> > Next 'pn
>> >> >> > Next 'upn
>> >> >> > imax = i
>> >> >> >
>> >> >> > 'Put results (cleanTable) below existing table.
>> >> >> > Selection.Rows(1).Copy Destination:= _
>> >> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
>> >> >> > Selection.Column)
>> >> >> > Cells(Selection.Rows.Count + Selection.Row + 2,
>> >> >> > Selection.Column).Select
>> >> >> >
>> >> >> > For i = 1 To imax
>> >> >> > For j = 0 To 4
>> >> >> > ActiveCell.Offset(i, j) = cleanTable(i, j + 1)
>> >> >> > Next j
>> >> >> > Next i
>> >> >> >
>> >> >> > End Sub
>> >> >> >
>> >> >> >
>> >> >> > "Big H" wrote:
>> >> >> >
>> >> >> >> Hi there,
>> >> >> >> Can someone help me, I have a problem in that i have a range of
>> >> >> >> data
>> >> >> >> with
>> >> >> >> part numbers down column A, with various data in columnsB,C,D.
>> >> >> >> The
>> >> >> >> part
>> >> >> >> numbers could appear one or more times, what i want to do is
>> >> >> >> detailed
>> >> >> >> below.
>> >> >> >> The range extends to row 1000.
>> >> >> >>
>> >> >> >>
>> >> >> >> EXCEL: BEFORE
>> >> >> >> A B C D
>> >> >> >> E
>> >> >> >> 1 ABA 50
>> >> >> >> 2 ABA 20
>> >> >> >> 3 ABA 10
>> >> >> >> 4 ABA
>> >> >> >> 50
>> >> >> >>
>> >> >> >> EXCEL:AFTER
>> >> >> >> A B C D
>> >> >> >> E
>> >> >> >> 1 ABA 20 50 10
>> >> >> >> 50
>> >> >> >>
>> >> >> >> regards Harry
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>