• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to make the check and uncheck process of Excel Autofilter easier?

Bookus

New Member
I have a huge excel file which has the details of many people. My job is to check each person's details individually and standardise their data to all the rows (For example, A person whose name is in many identity numbers, and their birthdate or the address or any personal it may be, is given only in one row, I've to copy paste the data to all the rows wherever this person's name is. Likewise, I have to do the same to other columns). For this, what I'm doing now is using the auto filter option to filter the person's first name one by one and checking their data (i.e. I first filter the first name, standardise the details and uncheck that name in auto filter menu and check the next name to work on it. I do like this for every name). As I have thousands of people's data, it takes so much time to check and uncheck the names in auto filter menu for each and every name. I don't have the knowledge of excel VBA or power query. So I don't know how to create a macro for this. I don't even know if this could be done through a macro. Also, I didn't find any solution for this particular problem in Google as far as I searched. My doubts:
  1. Can we simplify the checking and unchecking process in auto filter menu to move to the next name in any way? How to filter the First name column easily without using the check&uncheck option in the filter menu? Like adding a previous and next button which does job with a click instead of manually checking and unchecking the names?
  2. I need to make sure that all the details of every person is same on all the columns after the Last name column. Is there any way to do that instead of copy pasting the data from one row to all the other rows? I'm a beginner in excel. So, please help me on this. I'm attaching the example pictures of the real file that I'm working on here --> Sample 1; Sample 2; Sample 3 Please note that I've hidden the columns which are not needed to be standardised on person basis. Hope I’ve explained my doubts properly. Thanks in advance! ps: I'm using Excel 2016.
 

p45cal

Well-Known Member
Attached is a demonstration of a Power Query solution.
It's a bit of a mess because I only noticed you had hidden columns later.
There's your table in blue at the top, and a green result table below.

For this to work, you need your table to have a single row of headers which are all distinct, I've done this a bit by concatenating some of your 2 row headers. For this to look the same your sheet I've hidden this new header row (row 3).

This solution depends on the combination of Signer First Name and Signer Last Name to establish related rows, and that's is all.
What this does is to group rows according to those two fields (as you might do if you filtered on these two columns), then I copy down then copy up all the data in the other columns. This means if there is conflicting data in a given column, you won't lose that data, but some rows will be different.

Try changing/adding to the data in some columns of the blue table, then right-click on the lower table and choose Refresh.

If you want me to re-write this, supply a file with your own single row of headers (preferably including hidden columns too)
 

Attachments

Top