Please help/guide me, Our Experienced Developers

Please help/guide me, Our Experienced Developers
0

#1

I am still new in web development and JavaScript. I am very close to completing my FCC Front end courses. Presently I work as an IT support Officer in my Organisation, I am Just learning Web development on my own because of my interest and love for programming. I hope to transform to a developer once I acquire the basic skill.

We have a challenge in my office that I think needs a developer’s help to solve. I am the one managing an access control system in our office, we have noticed that whenever we pull the report from the system which comes in form of a .CSV file or excel, there are cases where multiple rows will contain an Individual’s information. This might be as a result of users flashing their IDs multiple times or malfunctioning of the system’s application sometimes.

Our Challenge is to remove these duplicates before sending it out to our HR team. We do not have a developer or programmer to do this for us. I am pleading for experienced developers here to help me develop a script that will take in the .csv of excel file and remove duplicate record for an individual. That is, whenever the script encounters two or more rows containing information for one person, it will keep the first record and delete other rows with same information until it encounters record for a different person. I have attached a sample file to clarify my need. In the attached file, we want a script that will keep row 4 with the records for John and delete rows 5,6 and 7 till row 8. It will keep row 8 and delete rows 9 to 27 and keep row 28.
Can this challenge be solved using excel alone? if possible guide me else I will like to work with any experienced developer that will help me write a script that will solve this. It is a good learning challenge for me. I hope I can get help here. I will really appreciate. We are all learning.
Thanks in anticipation for a favourable response.
Zubenna.


#2

You may want to delete that photo as it appears to contain sensitive information.


#3

Do not bother about the sensitivity of the photo, It is just to clarify potential contributors. The information there is not important.


#4

You can import Excel into MS Access if you have it. You can then write an SQL query within MS Access to filter the data as you want, and then move it back to Excel. I know little about Excel, so there maybe a way to do it from Excel alone, but I do know you can import from one MS product to another.

Another way to do it is with Perl or AWK, if those are available to you. Both are programming languages that can be used for text processing, and both have built in facilities to parse delimited values in a csv file. I haven’t use those in years, so it would take me a while to remember how to do it. Also you need to remove the header before you do anything like this.

But here is an example of AWK and CSV:
http://www.joeldare.com/wiki/using_awk_on_csv_files


#5

Yes. There is a feature in Excel (2007 and newer) called “Remove Duplicates”.

If you want to automate this (as have it happen in response to certain events, such as opening the workbook), you can do that in VBA. The “Record Macro” feature can be helpful to get started with this. This is not a hard macro to write.


#6

@dpberry552 thanks. I am not very good in excel. I use excel 2013. I will appreciate if you can help me write the required Macro of formula to solve this challenge.