How to distinct the excel


#1

image

I want to find out the A B and C column write them to another excel sheet
and select column B start with FM7171
finally delete the same row,just save one row A=6 B=FM7171_A… C=C6

how can I do it with excel function or Excel VBA also ok
thank you very much


#2

Do you want to remove the duplicates only for rows that have column B starting with FM7171?


#3

yes
is there any way?
thank you


#4

I just want to find the A B and C column


#5

@jmy Follow below link
Select Distinct and obtain all row of it


#6

thanks for your reply
but I want to imply it with excel function and my request was a little complex
thank you


#7

Can you show me how your result excel would be like? For the given excel sheet above?


#8

filtered table like this
image
thak you


#9

@jmy Can you share your excel file


#10

just do a sample excel is ok
thank you very much


#11

Hi @jmy,

Please take a look to get idea

Regards
Balamurugan.S


#12

@jmy check attached file

RemoveDuplicatesInColumn.zip (12.9 KB)


#13

Okay… I am gonna help you do this with just excel shortcuts.

You can use
Excel Application Scope to open excel
Select Range Sheet1 - A1 to move to the header
Send hotkey Ctrl + Shift + L - shortcuts for applying filter (make sure filter was not applied earlier)
Select Range Sheet1 - B1 to move to the Column B header
Send hotkey Alt + Down to open the filter dropdown
Type Into “FM7171[k(enter)]” into the search box
Send hotkey Ctrl + a to select filtered text
Send hotkey Ctrl + c to copy filtered text
Select Range Sheet2 - A1 Move to the new sheet (Use a sheet (or create a sheet) in the same workbook like Sheet2. This is easier than copying the content and pasting it in a new excel workbook)
Send hotkey Ctrl + v to paste copied text
Select Range Sheet2 - D1 move to column after D
Send hotkey Ctrl + Shift + Right to select column headers after column C that you want to delete
Send hotkey Shift + Space to select entire column of values
Send hotkey Ctrl + - to delete all columns selected
Select Range Sheet2 - A1 Move to header
Send hotkey Alt + am to choose remove duplicates
Click to click on OK of the ‘Remove duplicates’ dialog window
There you have all the filtered rows, with duplicates removed! Just like this!

Save Workbook to save all these hardwork!!

Its a bit lengthy. Hope it serves your purpose!


Filter Datatable contains datetime