I need a formula that will do the following: I want to copy A1:A3 to
different cells A10:A12. if the upper cells have identical entries I want the
lower cells to have the duplicated entry only once.

A
1 Joe
2 Bill
3 Joe
Because Joe was entered twice in the upper cells I want
the result to look like the lower cells. Essentually grouping like entries in
the upper cell to one entry in the lower cell.
A
10 Bill
11 Joe
12

Any help regarding this matter will be greatly appreciated. Thanks for your
time.

Hi,
A macro could do it, but you might first try using the filter function.
Here is the description from Excel help:

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

A duplicate row (also called a record) in a list is one where all values in
the row are an exact match of all the values in another row. To delete
duplicate rows, you filter a list for unique rows, delete the original list,
and then replace it with the filtered list. The original list must have
column headers.

Caution Because you are permanently deleting data, it's a good idea to
copy the original list to another worksheet or workbook before using the
following procedure.

Select all the rows, including the column headers, in the list you want to
filter.

Click the top left cell of the range, and then drag to the bottom right cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the selection
appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

Regards...

--
Ken Hudson


"Sdbenn90" wrote:

I need a formula that will do the following: I want to copy A1:A3 to
different cells A10:A12. if the upper cells have identical entries I want the
lower cells to have the duplicated entry only once.

A
1 Joe
2 Bill
3 Joe
Because Joe was entered twice in the upper cells I want
the result to look like the lower cells. Essentually grouping like entries in
the upper cell to one entry in the lower cell.
A
10 Bill
11 Joe
12

Any help regarding this matter will be greatly appreciated. Thanks for your
time.

That worked great, but I was looking for something that would do this
automatically.

"Ken Hudson" wrote:

Hi,
A macro could do it, but you might first try using the filter function.
Here is the description from Excel help:

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

A duplicate row (also called a record) in a list is one where all values in
the row are an exact match of all the values in another row. To delete
duplicate rows, you filter a list for unique rows, delete the original list,
and then replace it with the filtered list. The original list must have
column headers.

Caution Because you are permanently deleting data, it's a good idea to
copy the original list to another worksheet or workbook before using the
following procedure.

Select all the rows, including the column headers, in the list you want to
filter.

Click the top left cell of the range, and then drag to the bottom right cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the selection
appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

Regards...

--
Ken Hudson


"Sdbenn90" wrote:

I need a formula that will do the following: I want to copy A1:A3 to
different cells A10:A12. if the upper cells have identical entries I want the
lower cells to have the duplicated entry only once.

A
1 Joe
2 Bill
3 Joe
Because Joe was entered twice in the upper cells I want
the result to look like the lower cells. Essentually grouping like entries in
the upper cell to one entry in the lower cell.
A
10 Bill
11 Joe
12

Any help regarding this matter will be greatly appreciated. Thanks for your
time.

A couple of questions....
Do you have only one column of data (column A) to manipulate?
Is it okay to write over the old data with the unique list?

--
Ken Hudson


"Sdbenn90" wrote:

That worked great, but I was looking for something that would do this
automatically.

"Ken Hudson" wrote:

Hi,
A macro could do it, but you might first try using the filter function.
Here is the description from Excel help:

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

A duplicate row (also called a record) in a list is one where all values in
the row are an exact match of all the values in another row. To delete
duplicate rows, you filter a list for unique rows, delete the original list,
and then replace it with the filtered list. The original list must have
column headers.

Caution Because you are permanently deleting data, it's a good idea to
copy the original list to another worksheet or workbook before using the
following procedure.

Select all the rows, including the column headers, in the list you want to
filter.

Click the top left cell of the range, and then drag to the bottom right cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the selection
appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

Regards...

--
Ken Hudson


"Sdbenn90" wrote:

I need a formula that will do the following: I want to copy A1:A3 to
different cells A10:A12. if the upper cells have identical entries I want the
lower cells to have the duplicated entry only once.

A
1 Joe
2 Bill
3 Joe
Because Joe was entered twice in the upper cells I want
the result to look like the lower cells. Essentually grouping like entries in
the upper cell to one entry in the lower cell.
A
10 Bill
11 Joe
12

Any help regarding this matter will be greatly appreciated. Thanks for your
time.

There is only one column to manipulate
No, I want the old data to remain as it is
Thanks.

"Ken Hudson" wrote:

A couple of questions....
Do you have only one column of data (column A) to manipulate?
Is it okay to write over the old data with the unique list?

--
Ken Hudson


"Sdbenn90" wrote:

That worked great, but I was looking for something that would do this
automatically.

"Ken Hudson" wrote:

Hi,
A macro could do it, but you might first try using the filter function.
Here is the description from Excel help:

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

A duplicate row (also called a record) in a list is one where all values in
the row are an exact match of all the values in another row. To delete
duplicate rows, you filter a list for unique rows, delete the original list,
and then replace it with the filtered list. The original list must have
column headers.

Caution Because you are permanently deleting data, it's a good idea to
copy the original list to another worksheet or workbook before using the
following procedure.

Select all the rows, including the column headers, in the list you want to
filter.

Click the top left cell of the range, and then drag to the bottom right cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the selection
appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

Regards...

--
Ken Hudson


"Sdbenn90" wrote:

I need a formula that will do the following: I want to copy A1:A3 to
different cells A10:A12. if the upper cells have identical entries I want the
lower cells to have the duplicated entry only once.

A
1 Joe
2 Bill
3 Joe
Because Joe was entered twice in the upper cells I want
the result to look like the lower cells. Essentually grouping like entries in
the upper cell to one entry in the lower cell.
A
10 Bill
11 Joe
12

Any help regarding this matter will be greatly appreciated. Thanks for your
time.

Okay, give this macro a try.
Be sure to make a back up of your workbook.
The macro assumes that there is no header row.

To set up and run the macro:
With the workbook open, press Alt-F11 to open visual basic.
Go to Insert Module.
Copy and paste the code from below into the module.
Close Visual Basic.
Back on the worksheet go to Tools Macro Macros...
Highlight the macro and click the Run button.

Code....

Option Explicit
Sub CompressRecs()

Dim Iloop As Double
Dim NumRowsA As Double
Dim NumRowsB As Double
NumRowsA = Range("A65536").End(xlUp).Row
Columns("A").Copy
Columns("B").Select
ActiveSheet.Paste
Range("B1:B" & NumRowsA).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
For Iloop = NumRowsA To 2 Step -1
If Cells(Iloop, "B") = Cells(Iloop - 1, "B") Then
Cells(Iloop, "B").Delete Shift:=xlUp
End If
Next Iloop
NumRowsB = Range("B65536").End(xlUp).Row
Range("B1:B" & NumRowsB).Cut
Range("A" & NumRowsA + 2).Select
ActiveSheet.Paste
Range("A1").Select

End Sub

--
Ken Hudson


"Sdbenn90" wrote:

There is only one column to manipulate
No, I want the old data to remain as it is
Thanks.

"Ken Hudson" wrote:

A couple of questions....
Do you have only one column of data (column A) to manipulate?
Is it okay to write over the old data with the unique list?

--
Ken Hudson


"Sdbenn90" wrote:

That worked great, but I was looking for something that would do this
automatically.

"Ken Hudson" wrote:

Hi,
A macro could do it, but you might first try using the filter function.
Here is the description from Excel help:

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

A duplicate row (also called a record) in a list is one where all values in
the row are an exact match of all the values in another row. To delete
duplicate rows, you filter a list for unique rows, delete the original list,
and then replace it with the filtered list. The original list must have
column headers.

Caution Because you are permanently deleting data, it's a good idea to
copy the original list to another worksheet or workbook before using the
following procedure.

Select all the rows, including the column headers, in the list you want to
filter.

Click the top left cell of the range, and then drag to the bottom right cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the selection
appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

Regards...

--
Ken Hudson


"Sdbenn90" wrote:

I need a formula that will do the following: I want to copy A1:A3 to
different cells A10:A12. if the upper cells have identical entries I want the
lower cells to have the duplicated entry only once.

A
1 Joe
2 Bill
3 Joe
Because Joe was entered twice in the upper cells I want
the result to look like the lower cells. Essentually grouping like entries in
the upper cell to one entry in the lower cell.
A
10 Bill
11 Joe
12

Any help regarding this matter will be greatly appreciated. Thanks for your
time.

Thanks exactly want I needed. Thank you so much for your help.

"Ken Hudson" wrote:

Okay, give this macro a try.
Be sure to make a back up of your workbook.
The macro assumes that there is no header row.

To set up and run the macro:
With the workbook open, press Alt-F11 to open visual basic.
Go to Insert Module.
Copy and paste the code from below into the module.
Close Visual Basic.
Back on the worksheet go to Tools Macro Macros...
Highlight the macro and click the Run button.

Code....

Option Explicit
Sub CompressRecs()

Dim Iloop As Double
Dim NumRowsA As Double
Dim NumRowsB As Double
NumRowsA = Range("A65536").End(xlUp).Row
Columns("A").Copy
Columns("B").Select
ActiveSheet.Paste
Range("B1:B" & NumRowsA).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
For Iloop = NumRowsA To 2 Step -1
If Cells(Iloop, "B") = Cells(Iloop - 1, "B") Then
Cells(Iloop, "B").Delete Shift:=xlUp
End If
Next Iloop
NumRowsB = Range("B65536").End(xlUp).Row
Range("B1:B" & NumRowsB).Cut
Range("A" & NumRowsA + 2).Select
ActiveSheet.Paste
Range("A1").Select

End Sub

--
Ken Hudson


"Sdbenn90" wrote:

There is only one column to manipulate
No, I want the old data to remain as it is
Thanks.

"Ken Hudson" wrote:

A couple of questions....
Do you have only one column of data (column A) to manipulate?
Is it okay to write over the old data with the unique list?

--
Ken Hudson


"Sdbenn90" wrote:

That worked great, but I was looking for something that would do this
automatically.

"Ken Hudson" wrote:

Hi,
A macro could do it, but you might first try using the filter function.
Here is the description from Excel help:

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2002

A duplicate row (also called a record) in a list is one where all values in
the row are an exact match of all the values in another row. To delete
duplicate rows, you filter a list for unique rows, delete the original list,
and then replace it with the filtered list. The original list must have
column headers.

Caution Because you are permanently deleting data, it's a good idea to
copy the original list to another worksheet or workbook before using the
following procedure.

Select all the rows, including the column headers, in the list you want to
filter.

Click the top left cell of the range, and then drag to the bottom right cell.
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy .
The filtered list is highlighted with bounding outlines and the selection
appears as an item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

Press the DELETE key.
The original list is deleted.

In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

Regards...

--
Ken Hudson


"Sdbenn90" wrote:

I need a formula that will do the following: I want to copy A1:A3 to
different cells A10:A12. if the upper cells have identical entries I want the
lower cells to have the duplicated entry only once.

A
1 Joe
2 Bill
3 Joe
Because Joe was entered twice in the upper cells I want
the result to look like the lower cells. Essentually grouping like entries in
the upper cell to one entry in the lower cell.
A
10 Bill
11 Joe
12

Any help regarding this matter will be greatly appreciated. Thanks for your
time.