How to remove just the value in a cell in a row

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3171043%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHow%20to%20remove%20just%20the%20value%20in%20a%20cell%20in%20a%20row%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3171043%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20want%20to%20remove%20duplicate%20values%20in%20a%20column%2C%20but%20just%20the%20value%20in%20the%20cell.%20I%20don't%20want%20to%20remove%20the%20entire%20row%20and%20move%20all%20the%20rows%20up.%20Attached%20is%20a%20screenshot%20of%20what%20I'm%20looking%20to%20do.%20On%20the%20left%20is%20what%20I%20want%20to%20remove%20the%20duplicates%20from%2C%20and%20on%20the%20right%20is%20what%20I%20want%20it%20to%20look%20like.%20I%20have%20a%20spreadsheet%20with%20~300%2C000%20rows%20that%20I%20need%20to%20run%20this%20on.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348570i09463A9B632ACF89%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22mstritt4302_0-1644974042236.png%22%20alt%3D%22%5C%26quot%3Bmstritt4302_0-1644974042236.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3171043%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Visitor

I want to remove duplicate values in a column, but just the value in the cell. I don't want to remove the entire row and move all the rows up. Attached is a screenshot of what I'm looking to do. On the left is what I want to remove the duplicates from, and on the right is what I want it to look like. I have a spreadsheet with ~300,000 rows that I need to run this on.

 

mstritt4302_0-1644974042236.png

 

2 Replies

@mstritt4302 

 

To do that, follow these steps...

  1. In cell D2, place the formula =COUNTIF(A$2:A2,A2)=1 and double click the fill handle to copy it down.
  2. Type Flag in cell D1.
  3. Select cell D1 and press Ctrl+Shift+L to apply Filter.
  4. Expand the filter dropdown in D1 and uncheck (Select All) and check False and click on OK.
  5. Now select the first cell in column A below the column header 'Color', in this case, it would be cell A3 as per your sample data and press Ctrl+Shift+Down Arrow Key to select all the visible values in column A.
  6. Press Delete from the keyboard to delete all the selected values in column A.
  7. Press Ctrl+Shift+L again to remove the Filter and you will have the desired data in the end.

Delete Duplicate Values.jpg

 

I use the formula "if".
put formula in a new row, "=if(A2=A1,"",A2), you will get a new row exactly the same as you wish, and then just copy this new row and replace row A. be careful to paste as value.