0

My rows contain data similar to below in different cells right next to each other

+--------------+--------------+--------------+---------------+---------------+ | Print1 | Print2 | Print3 | Print4 | Print5 | +--------------+--------------+--------------+---------------+---------------+ | Pad Print | Pad Print | Screen Print | Digital Print | Digital Print | +--------------+--------------+--------------+---------------+---------------+ | Screen Print | Screen Print | Screen Print | Pad Print | Digital Print | +--------------+--------------+--------------+---------------+---------------+ | Screen Print | Screen Print | Pad Print | Pad Print | Pad Print | +--------------+--------------+--------------+---------------+---------------+

I want to remove repeating cells and get them to look like below +--------------+--------------+---------------+--------+--------+ | Print1 | Print2 | Print3 | Print4 | Print5 | +--------------+--------------+---------------+--------+--------+ | Pad Print | Screen Print | Digital Print | | | +--------------+--------------+---------------+--------+--------+ | Screen Print | Pad Print | Digital Print | | | +--------------+--------------+---------------+--------+--------+ | Screen Print | Pad Print | | | | +--------------+--------------+---------------+--------+--------+

Can you guys please tell me how to achieve this?

nasty
  • 6,797
  • 9
  • 37
  • 52

2 Answers2

2

Copy the data and right click (on a black sheet) > Paste Special > Check transpose. It will paste it to a blank sheet but rotate everything 90 degrees. Then you can select a column and use remove duplicates in the data tab.

If you have to do it many times record a macro highlighting a column and remove duplicates then select the next column, stop macro recorder.

EDIT: Here is the VBA to remove the duplicates of the selected cells.

Sub RmDuplicates()
    With Selection
        Application.CutCopyMode = False
        .RemoveDuplicates Columns:=1, Header:=xlNo
    End With
End Sub
Blake
  • 230
  • 1
  • 8
  • Thanks Blake. Im having problems with Macro. It doesnt seem do to anything after following your instructions. why is that? – nasty Oct 07 '18 at 07:16
  • Did you turn on use relative references when you ran the recorder? If you did not it will just run the macro on the same cells every time. With relative references it will do it based on the current selected cell. – Blake Oct 07 '18 at 08:15
  • Yes it goes through them fine, but doesnt remove duplicates. I think eventhorugh its looking at relative columns, its trying to look for duplicates comparing to the original cells? – nasty Oct 07 '18 at 09:55
  • I will add the vba in my answer to remove the duplicates (do not select the header). Paste it in the VBA editor (Alt+F11) in Module1. You can assign a shortcut by pressing Alt+F8 select the macro and click options. This will not save in your workbook unless you save it as a macro enabled workbook. – Blake Oct 07 '18 at 10:22
0

Without VBA and Assuming Print1 is in A1. Then in say H2 copied across and down to suit:

=IF(COLUMN()=8,A2,IF(AND(A2<>$H2,COUNTIF(A2:$E2,A2)=1),A2,FALSE))

Then select H2 to end and Delete... FALSE with Shift cells left. These are easy to select with Find & Select > Go To Special... > Formulas > Logical ticked (only).

Copy A1 to H1 and across to suit.

However, based on this precedent may make your Q off topic here.

pnuts
  • 58,317
  • 11
  • 87
  • 139