Start Free Trial

asked on

macro to delete repeated records

hi, i need macro to delete repeated records in excel file. Thanks

well depending on the file you may use the removeduplicates propert and it will only keep unique values like following

activesheet.range("C:C").Removeduplicates

Will in fact only keep unique values in Col C.

But As I have been following your specific case and as you are dealing with high volume of records RemoveDuplicates will not work in your case.

Can you pls put the specific sample table you need rows to be unique /
gowflow

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

ok, before running the code, I have two questions:

i have rows from A to AQ, i did not create empty row AV-1 to AV-44, should i do this first?

what about if the last column is AP and it has values from 0 to 4, or from 1 to 5. (like in the old work) will the code work too?

thanks

Please create the first row AV1 etc .... and here do not create the last column Y and regardless if AP or AZ or 1 or Normal .... it does not matter as it is cheking the ENTIREROW and see if another ENTIREROW is similar then it will only keep 1

Isn't this what you want ??? the ENTIRE ROW to be checked ???
gowflow

ASKER

yes boss, that is what i need. you got it right... i will do it now.. cheers

Your welcome. Anctiously waiting for your feedback on this.
gowflow

ASKER

my feed back is known ...

ASKER

amazing, surprising, astounding, Thanks a lot

Your welcome gret to know it worked for you. Do not spare me if you need more help you can post the link in here.
Rgds/gowflow

ASKER

yes, i need something new.. i will put the link here after i post it.

the thing is. i need to apply mathematical formula in the data set.. i can implement it manulay... and this takes time.. I will get back to you with full detials.. Thanks

ok fine.
gowflow

Did you post the question you needed help with ?
gowflow

ASKER

hi... this is the formula, but i need to explain it well in one example to you.. so you might implement it well.. i need some times for that.
equation.JPG

ASKER

i did not post the question yet, before that i will inform you boss

WOW it reminds me of my University Math courses !!!
ok just one question this is a formula but it applies to what columns ? as you have 43 !!!

I prefer we initiate conversation here till the idea is pretty clear then you can post .. or else we may go chaos !!

gowflow

ASKER

I think we can go for easier formula like this:
x=x-min(Xi) / max (x) - min (x)
=(D11-55)/(90-55) in my case the 77 number was in D11 cell.
before after
77        0.6286
55        0
84        0.8286
90        1

so as you can see above the 77 has become 0.6286 after applying the formula.
* I changed the decimal number places into 4 from format cells.

this formula should be implemented in all columns from (1 to 41)... 42 and 43 is not included.

so you have to calculate the mean value for every column, the maximum and minimum value, so that you can implement it in the formula.

if you have more question please ask. Thanks

so let me get this right.

You mean to say you would have a worksheet that has 41 columns and X rows and they would all be in hte format
77 82 36 90 45 ....
All the worksheet would be this way ? not the present worksheet that has values like 0 0.2334 1 0.3453 etc ...

1) Is that what you want to apply the formula ?
2) If my previous assumption is correct then you would need this run to be done ONLY once right ? to convert the numbers into fractions using the formula ?
3) Again if my previous assumption is correct then let me get the essence of your formula then
Say we have following Columns and rows
A B C ....
20 30 70
12 06 43
25 92 67
36 45 28
22 29 34 ....

then your formula starting Col A for A1 whcih is 20 would be as follows:
MIN of all values in Col A = 12
MAX of all values in Col A = 36
So the A1 that is 20 would be (20-12)/(36-12) = 8/24 = 0.3333

and with the same logic for B1 it would be
(30-6)/(92-06) = 24/86 = 0.2791

etc ...

ok now if my previous assumptions are all correct then when we get to row2 we have something like this:

A B C ....
0.3333 0.2791 1
12 06 43
25 92 67
36 45 28
22 29 34 ....

Now A2 = 12 the formula would be:
(12-0.3333)/(36-0.3333) = 11.6667/35.6667 = 0.3271

Is this what you are trying to acheive ?

Finally if all my previous assumptions are correct then I suggest you do not 'go for an easier formula' as you stated but go for the final formula like you posted. Here I would only need 1 clarification which is

formula is
Xi = New min + ( New max - New min) / ((Xi - Xmin)/(Xmax - Xmin))

what is meant by New min (I presume it is the new values that we have converted like in the above example it would be for col A 0.3333 ?? and case we had more rows produced to the New max would be the new Maximum of these values converted ? is that what is meant by this ? and Xmax Xmin would be the Max and Min of the values prior to conversion ???

please advise and sorry for this long explanation as need to have the details cleared.
gowflow

ASKER

Not like that … let me be more clear.
Say we have following Columns and rows
A B C .... AO. (AP and AQ is we do not touche them)
1- 20 30 70
2- 12 6 43
3- 25 92 67
4- 36 45 28
5- 22 29 34 ....

X=x-min(Xi) / max (x) - min (x)

Then the formula starting on Col A for A1 = 20 would be as follows:
MIN of all values in Col A = 12
MAX of all values in Col A = 36
So the A1 that is 20 would be (A1-12)/(36-12) = 8/24 = 0.3333
and with the same logic for A2 and A3 and A4 ……etc it would be
(A2-12)/(36-12) = 24/86 = 0.2791
etc ... for A3, A4, A5 till the end of column A, I usually use hold and drag the output of A1, which will automatically give me the output of A2, A3, A4,….. etc like this
20      0.333333
12      0
25      0.541667
36      1
22      0.416667

Then for column B
MIN of all values in Col A = 6
MAX of all values in Col A = 92
So: X=x-min(Xi) / max (x) - min (x)
= (B1-6)/(92-6)= 0.27907 and here I will hold and drag the output down it will automatically give me the B2, B3, B4, B5….etc
30      0.27907
6      0
92      1
45      0.453488
29      0.267442

the last two columns i need them to be transferred in the new output sheet untouched.
Please tell me if it is not clear. Thanks

ASKER

regarding the first formula, i still do research about it. but for more information about it, please check the attached picture read here:
http://wiki.answers.com/Q/What_is_min-max_normalization

but for now we go for the formula:

X=x-min(Xi) / max (x) - min (x)

Thanks
min-max.JPG

ok for
but for now we go for the formula:

X=x-min(Xi) / max (x) - min (x)

But you did not comment on my reasoning is it correct ???
If yes then I will need a sample data to be able to build the macro so I can test it correctly.
gowflow

ASKER

hi... i did comment on your reasoning... check the above comments..
by: reoromanPosted on 2013-02-05 at 19:36:49ID: 38856207

ASKER

sample data ... oh... yes... sure ... i will upload it now.. just give me 5 to 10 minutes please

Yes sorry just saw it I thought it was my post as just looked the couple of last lines. So you do it by column not by row ?
gowflow

ASKER

yes by column.

here is the file. it took me long time until i prepare it, that is why i am late.

http://www53.zippyshare.com/v/70231260/file.html

Ok I worked on it and just to make sure I got this right you want the values after the formula to be decimal 4 digits right ?
gowflow

ASKER

yes. thank you.

ok fine you can shoot the question then,
gowflow

ASKER

https://www.experts-exchange.com/questions/28022956/excel-macro-to-do-math-formula.html