Solved

Excel Macro Clearing Dup Fields Not Working

Posted on 2013-01-04
12
213 Views
Last Modified: 2013-01-07
Experts,

This application was working fine and then I added about 10 more fields to it and the "clear" code at the bottom is no longer working. I tried to debug and fix on my own but was unsuccessful. Could one of you take a look and fix this for me?

The input file holds the data that needs to be reformatted. The macro can be run by entering ctl/r. The output as tested is in the second workbook.

I need to have the duplicate SSN, Last, and First name cleared which is the code at the end of the macro. Let me know if you have questions. :-)


Thanks, Janis
Ineligible-Sites-Transpose-Input.xlsm
EE-Test-Output.xlsx
0
Comment
Question by:DMKetcher
  • 7
  • 5
12 Comments
 

Author Comment

by:DMKetcher
ID: 38745056
Just increased the points. Need this soon. Thanks!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38746893
Pls correct my understanding:
You want to delete the rows where there is a duplicate in SSN, Last Name and First Name correct ?

My understanding of your macro it copies lines from the output file to the current file at the cell specified by destination cell.
Question: Your duplicate are from what block ? the block that exist in the Excel and the new block or ... ???

gowflow
0
 

Author Comment

by:DMKetcher
ID: 38747189
I don't want to delete the whole row. I just want the SSN, Last, and First name to appear once for each student. I figured out a formula in Excel that will do the trick but if you want to work on this and tweak the code for me I would use it instead since that is easier with it all being done in the macro. If I use the Delete Dup feature in Excel it also deletes data needed so it is of no help.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38747199
well this last loop will never clear anything as it point wrongly. let me fix it for you but hv to go out now will revert lateron ... stay tuned.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38747661
Sorry it took sometime as I was out.

Is this what you are looking for ?
Pls test it and let me know. I am sorry but I code in a simpler way and do not like these super fancy endless offests etc.. that sometimes takes ages to decode.

gowflow
Ineligible-Sites-Transpose-Input.xlsm
0
 

Author Comment

by:DMKetcher
ID: 38748998
The code you fixed works but it needs to apply to the output workbook not the input. Can you adjust that? Thanks!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
ID: 38749090
Well then the code from the beginning was wrong as when you run it, It put the new range in the Input workbook and not the output. Let me look at the whole thing and will revert. I do not like this ttype of coding altogether I will review it altogether and revert. I only looed at the last part as this is what you requested but seems it is writing in the wrong place. Will revert.

rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38749191
I would like to know altogether what is this macro supposed to do ? can you explain it in couple of words pls so I can on track correctly ?
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 38749248
ok pls chk this version and let me know if this is what you want.
gowflow
New-Ineligible-Sites-Transpose-I.xlsm
0
 

Author Comment

by:DMKetcher
ID: 38749311
I understand completely why you don't like this code but I was trying to avoid having the input and output in different workbooks because the output is a report that will go to the Department of Education and i want to keep the Input in the original format. I could always write the output to another tab.

The input data has to be reformatted so there is one full line record per student and sub lines under the student that contain other lending or grant data.
0
 

Author Closing Comment

by:DMKetcher
ID: 38749312
Thank you!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38750050
Your mistaken !!! I hv no problem with multiple workbooks that's not the issue, the issue is the variable manipulation and the relying on default workbook which I do not like. If you noticed in the code posted I declared wsFm and wsTo to be respectively the FROM worksheet and the TO worksheet this way you can control the code and not the code controls you as sometimes by doing a select the focus changes and when you rely on default workbook you see yourself writing to the FROM where in fact you want to write to the TO !!!

You were also missing a major part which is the request to indicate the output file reason why it was writing to your FROM sheet and not to the TO sheet.

Hope your satissfied with the code and if any other issue you may need help with pls do not hesitate to put a link here and I will assist.
gowflow
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now