Changing lowercase to uppercase for identical values in column and copying to seperate TAB

Dear Experts ,

This may be a quickie for most ,

Please see attached files "Input " -what we shall have and Output- what i basically want


In Input file spreadsheet attached , you shall observe that in Column E that there are identical values in both lowercase as well as uppercase ,

For example,in column E there may be value "ran" -I.E Lowercase and then again "RAN" i.e Uppercase ,similarly there may be a value "edn" and then again EDN ,

To understand what i want ,you can now see attached file "Output "
I want all lowercase values to be converted to uppercase values so that all identicall values are UPPERCASE ,
atached output sheet ,...basically in above example ,i want ran to be converted to "RAN" and edn to be converted to "EDN" so that we only have distinct "RAN " AND "EDN" AND no lowercase values ...for the converted lower to upper,i want the values to have a color (say brown like in attached output sheet ) so that i know which values have actually got converted ....

Also for Column E when there are blank cells ,i want cell G for that row to be copied into Cell E -Mostly this value shall be "NOT FOUND " as can be observed from the attached output sheet ,

All this i want in a seperate tab so that i can compare the final result from the actual result and see/study the difference ,

Thanks in advance to anyone attempting to solve this ,


Regards ,


Amlan Dutta

That works in converting lowercase to uppercase ,

Please Login or Register  to view this content.

and does it incredibly well in flash of a second !

I wished that the converted values have a seperate color and rathe rthan getting converted in the same tab does so in another tab of the same workbook for me to compare Input and output ,

I have run your macro and attached the output file ,also i attach the desired output file ,

In any case ,much of the thing is solved ,so i am glad ,

A small thing remained ,for the cells which were blank i wanted column G values to be pasted ,(but that i can manage )

What you have done is already so much ,so i will mark thread solved and give you sweet deserved reps,

Thanks so so much !

Regards ,


Amlan Dutta

Dear ,

As a student ,i am studying the code ,am i correct in understanding that in this part of the code

"IF(ISTEXT(" & .Address & ")-the macro is checking the text value in the given range -step 1



,UPPER(" & .Address & ")-here it is seeing whether in the selected range there is a uppercase match to the above match in step 1 -step 2


REPT(" & .Address & ",1))"-rhere it converts to upper case with 1 being boolean true ......-converts all step 1 to step 2 -step 3

Thanks ,

Amlan Dutta

Very slick ,i googled IF function and understand it now fully ...also your explanation rocks ,

I don't get the third part ,

If the value in the cell is not Text ,then this part 3 happens

REPT(" & .Address & ",1))...what is this 1 for and exactly what does it end up doing since no text means null cell !

Thank's,

Amlan Dutta

Stuff of the champions ,you rock !i now get it fully ....thanks so much !

(i might sound silly here but let me be honest ,i was thinking that 1 was meaning boolean true and was priding myself on that logic ,i now feel so silly ,lol)

Thanks ,i have no further questions to the thread ,i may also attempt to solve it using for loop ,if i end up doing that ,i will also post it !

Thanks a tonne for all help ,i did give you reps and marked the thread as solved ,

Hugs ,

Amlan

Any idea why this might not work ,

Please Login or Register  to view this content.

Regards ,


Amlan

Please give me solution with loop ,i will remain forever indebted ,i am a huge fan of "For loop"....the other code looks like dracula but works great ,but "for "loop looks cute like a baby but doesn't work !

It will take you 3 seconds max ,but it will be loads of knowledge for me !

I tested yours ,it works like a dream !

So i put your entirerow.delete in mine but it refuses to behave ,

Please Login or Register  to view this content.

i know i miss something very minute !plz help ,plz plz !

Dear ,

Thank you!!!!!!!!!!!!!!!!!

Using the logic you provided i did proceed to solve the third and final part of the project and that is when the cell in E is empty ,i wanted to paste "Not Found " in it


I have attached the Input and output sheets ,

It does paste "Not found " in the range but actually goes on to paste the values below the range ,

Please Login or Register  to view this content.

Please don't get angry !

Thanks in advance ,

After this i need to take it to a seperate tab also ,but i know how to do that ,

Regards ,


Amlan

Finally this works after i tried out many variations but i fail to understand why the same operation cannot take place inside a single for loop and i have to define 2 for loop's thereby increasing execution time ,also "Not found "gets pasted below the range as well -see attached output file .

The code goes like this

Please Login or Register  to view this content.

Oh ,i see you already posted a reply ,actually i posted my above solution after i read your solution ...

My revised solution above works (see the attached file ) but i fail to understand why the "Not Found " after the range ,but something in your thread makes me feel that the rows which got deleted ,this not found are exactly for those deleted row ,maybe defining a seperate subroutine and calling from the first subroutine will help ,

Will give it a shot and let you know,

Regards ,

Amlan Dutta

Yahoooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo!!!!!!!

What i guess did worked !(But after only i read your thread i understood that ,so huge thanks !!

See the attached input and output files,

what i did was make 2 seperate subroutines - i mean what you gave me ,i just made two seperate subroutines ,i already had a parent subroutine ,from that parent subroutine ,i then called this 2 subroutines and now it works like a dream ,

I attach both the input and output files for your perusal ,


The code goes like this ,

Please Login or Register  to view this content.

This works like a charm ,but we need to still take it to a seperate tab ,i think i can handle it ,but i will prefer help ....

Regards ,

Amlan Dutta