Tough - show a hierarchy
 

My company identifies products like this: #####-###-AAA, with specific
numbers taking the place of the # sign and specific letters taking the place
of the A. the 5-digit number identifies the type of product, the 3-digit
number identifies the specific model for the type of product, and the 3
letters identify where the part was made.

I have already split the product numbers into the three components, but I
want to remove any repetitions in cell values. To show a sort of a hierarchy.
I want to go from this:

__A__ _B__ _C_
12345|123|ABC
12345|123|SDE
12345|321|RSW
54321|098|CBA
54321|890|ABC


to this:
__A__ _B__ _C_
12345|123|ABC
| |SDE
|321|RSW
54321|098|CBA
|890|ABC

how would i do that? thanks!

comparini3000

Tough - show a hierarchy
 

Based on your description There are two possible answers.

If you want to sum up quantities of products or such then take a look at
pivot tables. Place your cusor in the middle of your data and select Data -
Pivot Tables - Then follow the wizard. Place your Product descriptions in
the left hand column and the quantities in the center of the table.

If you just want to display the sheet as a hierarchy then take a look at
conditional formats. If the value of the cell equals the value of the cell
above it then format the font colour to be the same as the background colour.
It does not delete the contents of the cell it just makes the text
invisible...
--
HTH...

Jim Thomlinson


"comparini3000" wrote:

My company identifies products like this: #####-###-AAA, with specific
numbers taking the place of the # sign and specific letters taking the place
of the A. the 5-digit number identifies the type of product, the 3-digit
number identifies the specific model for the type of product, and the 3
letters identify where the part was made.

I have already split the product numbers into the three components, but I
want to remove any repetitions in cell values. To show a sort of a hierarchy.
I want to go from this:

__A__ _B__ _C_
12345|123|ABC
12345|123|SDE
12345|321|RSW
54321|098|CBA
54321|890|ABC


to this:
__A__ _B__ _C_
12345|123|ABC
| |SDE
|321|RSW
54321|098|CBA
|890|ABC

how would i do that? thanks!

comparini3000

Tough - show a hierarchy
 

"comparini3000" wrote:

My company identifies products like this: #####-###-AAA, with specific
numbers taking the place of the # sign and specific letters taking the place
of the A. the 5-digit number identifies the type of product, the 3-digit
number identifies the specific model for the type of product, and the 3
letters identify where the part was made.

I have already split the product numbers into the three components, but I
want to remove any repetitions in cell values. To show a sort of a hierarchy.
I want to go from this:

__A__ _B__ _C_
12345|123|ABC
12345|123|SDE
12345|321|RSW
54321|098|CBA
54321|890|ABC


to this:
__A__ _B__ _C_
12345|123|ABC
| |SDE
|321|RSW
54321|098|CBA
|890|ABC

how would i do that? thanks!

comparini3000

sorry, my last diagram got messed up, but all the values should line up. i
figured out a working code, it's not the most efficient, but it works:

__________________________________________________ ___________
Option Explicit
-----------------------------------------------------------------------
Sub delete_nonunique()
Dim x As Integer, I As Variant
Range("I1").End(xlDown).Select
x = Selection.Row
For Each I In Range("I2:I" & x):
If Selection.Value = ActiveCell.Offset(-1, 0).Range("A1").Value Then
Selection.ClearContents
ActiveCell.Offset(-2, 0).Range("A1").Select
x = x - 2
On Error GoTo ErrMsg
Else
x = x - 2
Range("I" & x).Select
On Error GoTo ErrMsg
End If
Next I
ErrMsg:
Range("I2").Select
End Sub
__________________________________________________ ____________

the reason i have it offset the selection by 2 is because i know the "Next
I" will select the cell below the current selection. Is there some sort of
opposite to "Next I"?

thanks

comparini3000

Tough - show a hierarchy
 

See if this is close to the results you want. Select the data in both
columns A and B (or change the statement Set rngData = Selection to whatever
range you need).

I think you have to pay attention to the second column. You cannot just
delete the duplicates because the value in column A could be different (ie
same model number, but different product).


Sub DeleteDupes()
Dim rngData As Range
Dim rngCol As Range
Dim rngCell As Range
Dim rngDelete As Range
Dim colTemp As Collection
Dim i As Long

Set rngData = Selection
Set colTemp = New Collection

If rngData.Columns.Count < 2 Then _
Exit Sub

On Error Resume Next
For i = 2 To 1 Step -1
For Each rngCell In rngData.Columns(i).Cells
Select Case i
Case 1
colTemp.Add rngCell.Value, CStr(rngCell.Value)
Case 2
colTemp.Add CStr(rngCell(1, 0).Value) & CStr(rngCell.Value), _
CStr(rngCell(1, 0).Value) & CStr(rngCell.Value)
End Select
If Err.Number < 0 Then
Err.Clear
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell
If Not rngDelete Is Nothing Then
rngDelete.ClearContents
Set rngDelete = Nothing
End If
Next i

End Sub


"comparini3000" wrote:



"comparini3000" wrote:

My company identifies products like this: #####-###-AAA, with specific
numbers taking the place of the # sign and specific letters taking the place
of the A. the 5-digit number identifies the type of product, the 3-digit
number identifies the specific model for the type of product, and the 3
letters identify where the part was made.

I have already split the product numbers into the three components, but I
want to remove any repetitions in cell values. To show a sort of a hierarchy.
I want to go from this:

__A__ _B__ _C_
12345|123|ABC
12345|123|SDE
12345|321|RSW
54321|098|CBA
54321|890|ABC


to this:
__A__ _B__ _C_
12345|123|ABC
| |SDE
|321|RSW
54321|098|CBA
|890|ABC

how would i do that? thanks!

comparini3000

sorry, my last diagram got messed up, but all the values should line up. i
figured out a working code, it's not the most efficient, but it works:

__________________________________________________ ___________
Option Explicit
-----------------------------------------------------------------------
Sub delete_nonunique()
Dim x As Integer, I As Variant
Range("I1").End(xlDown).Select
x = Selection.Row
For Each I In Range("I2:I" & x):
If Selection.Value = ActiveCell.Offset(-1, 0).Range("A1").Value Then
Selection.ClearContents
ActiveCell.Offset(-2, 0).Range("A1").Select
x = x - 2
On Error GoTo ErrMsg
Else
x = x - 2
Range("I" & x).Select
On Error GoTo ErrMsg
End If
Next I
ErrMsg:
Range("I2").Select
End Sub
__________________________________________________ ____________

the reason i have it offset the selection by 2 is because i know the "Next
I" will select the cell below the current selection. Is there some sort of
opposite to "Next I"?

thanks

comparini3000

Tough - show a hierarchy
 

It may not cause a problem, but I should have added the following line
between the end if and Next i statements.

End If
Set colTemp = New Collection '<< Add this line
Next i

"comparini3000" wrote:



"comparini3000" wrote:

My company identifies products like this: #####-###-AAA, with specific
numbers taking the place of the # sign and specific letters taking the place
of the A. the 5-digit number identifies the type of product, the 3-digit
number identifies the specific model for the type of product, and the 3
letters identify where the part was made.

I have already split the product numbers into the three components, but I
want to remove any repetitions in cell values. To show a sort of a hierarchy.
I want to go from this:

__A__ _B__ _C_
12345|123|ABC
12345|123|SDE
12345|321|RSW
54321|098|CBA
54321|890|ABC


to this:
__A__ _B__ _C_
12345|123|ABC
| |SDE
|321|RSW
54321|098|CBA
|890|ABC

how would i do that? thanks!

comparini3000

sorry, my last diagram got messed up, but all the values should line up. i
figured out a working code, it's not the most efficient, but it works:

__________________________________________________ ___________
Option Explicit
-----------------------------------------------------------------------
Sub delete_nonunique()
Dim x As Integer, I As Variant
Range("I1").End(xlDown).Select
x = Selection.Row
For Each I In Range("I2:I" & x):
If Selection.Value = ActiveCell.Offset(-1, 0).Range("A1").Value Then
Selection.ClearContents
ActiveCell.Offset(-2, 0).Range("A1").Select
x = x - 2
On Error GoTo ErrMsg
Else
x = x - 2
Range("I" & x).Select
On Error GoTo ErrMsg
End If
Next I
ErrMsg:
Range("I2").Select
End Sub
__________________________________________________ ____________

the reason i have it offset the selection by 2 is because i know the "Next
I" will select the cell below the current selection. Is there some sort of
opposite to "Next I"?

thanks

comparini3000