copy,paste,calculate Cell value based on calculations & comparisonsother cells same row. Decimal places

[fixer]

“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.








vba code will be placed in a seperate file macro.xlsm
i have one more file 1.xls (all files are located in a different path)
i have attached a sample pic of 1.xls & result pic too
check if Column H is greater or lower than column D
if column H is greater than column D then calculate 1% of column H and paste it to column J and then subtract Column H & column J and paste the result to column K
Or
if column H is lower than column D then calculate 1% of column H and paste it to column J and then add Column H & column J and paste the result to column K
Plz help me in solving this problem sir







Almost the same as from MoldyBread at excelforum
https://www.excelforum.com/excel-programming-vba-macros/1317283-compare-columns-and-based-on-that-increase-or-decrease-the-column-data-by-percentage.html#post5338184








Some more recent cross posted duplicsate on another one of Avinashes infinite cycles of starting again
https://eileenslounge.com/viewtopic.php?f=30&t=34932
https://chandoo.org/forum/threads/put-decimal-before-second-last-number-by-vba-in-column-e.44609/#post-266257 ( Leonardo1234 starting again )

[DocAElstein]

This is so simple, that even I find it easy to do, with a formula...



_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1087
1030
955.5
998.45
1079.9
22
=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))
=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))


3NSEADANIPOWEREQ
148.05
27.75
25.65
25.65
146.5
25
=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H"))
=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))


4NSEDLFEQ
265
419.7
350.05
387.25
267.15
17388
=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H"))
=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))


5NSEAMBUJACEMEQ
30.4
155.8
142.55
145.85
29.95
15083
=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H"))
=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))
Worksheet: 1-Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1087
1030
955.5
998.45
1079.9
22
=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))
=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))


3NSEADANIPOWEREQ
148.05
27.75
25.65
25.65
146.5
25
=IF(H3>D3,1/100*H3,IF(H3<D3,1/100*H3,"D is equal to H"))
=IF(H3>D3,H3-J3,IF(H3<D3,H3+J3,"D is equal to H"))


4NSEDLFEQ
265
419.7
350.05
387.25
267.15
17388
=IF(H4>D4,1/100*H4,IF(H4<D4,1/100*H4,"D is equal to H"))
=IF(H4>D4,H4-J4,IF(H4<D4,H4+J4,"D is equal to H"))


5NSEAMBUJACEMEQ
30.4
155.8
142.55
145.85
29.95
15083
=IF(H5>D5,1/100*H5,IF(H5<D5,1/100*H5,"D is equal to H"))
=IF(H5>D5,H5-J5,IF(H5<D5,H5+J5,"D is equal to H"))
Worksheet: 1-Sheet1

Each formula uses 2 Excel If functions, one is nested in the other...


( If _>_ , Do this , Else [ If _<_ , Do this , Else "......" ] )

If ( _>_ , Do this , Else If( _<_ , Do this , Else "......" ) )

Column J

=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"d is equal to H"))

if column H is greater than column D then calculate 1% of column H
=IF(H2>D2,1/100*H2,
Else
if column H is lower than column D then calculate 1% of column H
IF(H2<D2,1/100*H2,

Else
"D is equal to H"))

Column K



=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))

if column H is greater than column D subtract Column H & column J
=IF(H2>D2,H2-J2,
Else
if column H is lower than column D add Column H & column J
IF(H2<D2,H2+J2,


Else
"D is equal to H"))



Alan

Ref
https://support.office.com/en-us/article/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2?ui=en-US&rs=en-US&ad=US
https://www.techonthenet.com/excel/formulas/if.php

[fixer]

Sub STEP8()
Dim Wb1 As Workbook, Ws1 As Worksheet, Lr1 As Long
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets.Item(1)
Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
Ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"d is equal to H"))"
Ws1.Range("J2:J" & Lr1 & "").Value = Ws1.Range("J2:J" & Lr1 & "").Value
Ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,"D is equal to H"))"
Ws1.Range("K2:K" & Lr1 & "").Value = Ws1.Range("K2:K" & Lr1 & "").Value
Wb1.Save
Wb1.Close
End Sub







Doc Sir i am getting error sir i dont know why i got it plz have a look sir




http://www.eileenslounge.com/viewtopic.php?f=30&t=34670

[DocAElstein]

Hi
Your macro is very good.
You have written good coding.
It is almost perfect.

There is just one very small problem. It is just because VBA is confused when quotes are inside quotes. VBA does not like to see quotes inside other quotes. It get confused.

If VBA see this: …_

" " "
_.. VBA does not know what it is. VBA thinks maybe it is either this

" " "
or this

" " "
VBA does not like to see inside quotes 1 quote or 3 quotes or 5 quotes … etc

VBA must always see 2 quotes "" ( or 4 quotes m"""", or 6 quotes """""" )etc..
We must do a trick , like this

" "" "

Or this:

" "" "" "

If you do this trick, then VBA will see for "" just one quote.

So in your macro , like this is the problem: "________"_D is equal to H_"____"

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,"D is equal to H"))"

We must do trick: "________""_D is equal to H_""____"

"=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"



So in your macro,
change the single quotes inside the outermost enclosing quotes to double quotes
like this:
______________Ws1.Range(" 2: " & Lr1 & "").Value =__"__________""_D is equal to H_""____"

Sub STEP8()
Dim Wb1 As Workbook, Ws1 As Worksheet, Lr1 As Long
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets.Item(1)
Let Lr1 = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row
Ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,1/100*H2,IF(H2<D2,1/100*H2,""D is equal to H""))"
Ws1.Range("J2:J" & Lr1 & "").Value = Ws1.Range("J2:J" & Lr1 & "").Value
Ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,""D is equal to H""))"
Ws1.Range("K2:K" & Lr1 & "").Value = Ws1.Range("K2:K" & Lr1 & "").Value
Wb1.Save
Wb1.Close
End Sub


You did almost perfect in your macro. It was very close.

Alan




http://www.eileenslounge.com/viewtopic.php?f=30&t=34670

[fixer]

I will place the macro in a seperate file macro.xlsm
and i have a file name 1.xls
in 1.xls in column K i have data (i have attached the sample pic of that)
what i need is i need a macro that will remove third number after decimal and along with it it should remove the decimal
Result will be
1090.699 after runing the macro the ouput will be 109069
147.965 after runing the macro the ouput will be 14796
264.4785 after runing the macro the ouput will be 26447
30.2495 after runing the macro the ouput will be 3024

plz help me in solving this problem by vba

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem

[DocAElstein]

Using simple VBA techniques
' We can find the position of the . using Instr function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function
' Then we can take the left of the number for a length equal to the position of the . + 2 using the Left function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/left-function
' Then we can remove the . using the Replace function , https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function

Sub TrimRemoveDot() ' http://www.excelfox.com/forum/showthread.php/2456-Remove-decimals-by-vba?p=13068#post13068
Dim Ws1 As Worksheet
Set Ws1 = Workbooks("1.xls").Worksheets.Item(1) ' First worksheet in open workbooks 1.xls
Dim LrK As Long: Let LrK = Ws1.Range("K" & Ws1.Rows.Count & "").End(xlUp).Row
Dim RngK As Range: Set RngK = Ws1.Range("K2:K" & LrK & "")
Dim SnglCel As Range
For Each SnglCel In RngK
Dim Pos As Long: Let Pos = InStr(1, SnglCel.Value, ".", vbBinaryCompare) ' We can find the position of the . using Instr function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function
Let SnglCel.Value = Left(SnglCel.Value, Pos + 2) ' Then we can take the left of the number for a length equal to the position of the . + 2 using the Left function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/left-function
Let SnglCel.Value = Replace(SnglCel.Value, ".", "", 1, -1, vbBinaryCompare) ' Then we can remove the . using the Replace function https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function
Next SnglCel
End Sub

Or using formulas
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
K

1


2
1090.699


3
147.965


4
264.4785


5
30.2495


6
Worksheet: 1-Sheet1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
L
M
N
O

8
=FIND(".",K2)=LEFT(K2,L8+2)=SUBSTITUTE(M8,".","")=SUBSTITUTE(LEFT(K2,FIND(".",K2)+2),".","")


9
=FIND(".",K3)=LEFT(K3,L9+2)=SUBSTITUTE(M9,".","")=SUBSTITUTE(LEFT(K3,FIND(".",K3)+2),".","")


10
=FIND(".",K4)=LEFT(K4,L10+2)=SUBSTITUTE(M10,".","")=SUBSTITUTE(LEFT(K4,FIND(".",K4)+2),".","")


11
=FIND(".",K5)=LEFT(K5,L11+2)=SUBSTITUTE(M11,".","")=SUBSTITUTE(LEFT(K5,FIND(".",K5)+2),".","")
Worksheet: 1-Sheet1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
L
M
N
O

8
51090.69109069109069


9
4147.961479614796


10
4264.472644726447


11
330.2430243024
Worksheet: 1-Sheet1

Or using a final formula in Evaluate Range One liner technique

Sub EvaluateRangeTrimRemoveDot() ' http://www.excelfox.com/forum/showthread.php/2456-Remove-decimals-by-vba?p=13068#post13068
Dim Ws1 As Worksheet
Set Ws1 = Workbooks("1.xls").Worksheets.Item(1) ' First worksheet in open workbooks 1.xls
Dim LrK As Long: Let LrK = Ws1.Range("K" & Ws1.Rows.Count & "").End(xlUp).Row
Dim RngK As Range: Set RngK = Ws1.Range("K2:K" & LrK & "")
Let RngK.Value = Evaluate("=if({1},SUBSTITUTE(LEFT(" & RngK.Address & ",FIND("".""," & RngK.Address & ")+2),""."",""""))")
End Sub







Alan




Some more recent cross posted duplicsate on another one of Avinashes infinite cycles of starting again
https://eileenslounge.com/viewtopic.php?f=30&t=34932
https://chandoo.org/forum/threads/put-decimal-before-second-last-number-by-vba-in-column-e.44609/#post-266257 ( Leonardo1234 starting again )

[fixer]

Thnx Alot Doc Sir for ur Great Support
Problem Solved

[fixer]

all file are located in a different path
vba code will be placed in a seperate file macro.xlsm
my file name is 1.xls
If column H of 1.xls is lower than column D of 1.xls then with column K (subtract it and make it to the number ending with 0 or 5 whichever met first)
If column H of 1.xls is greater than column D of 1.xls then with column K (add it and make it to the number ending with 0 or 5 whichever met first)
i have attached the sample file and result too.. plz have a look sir
and help me in solving this problem sir

[sandy666]

just for fun with Power Query

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split = Table.SplitColumn(Table.TransformColumnTypes(Sourc e, {{"raw", type text}}, "en-GB"), "raw", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"raw.1", "raw.2"}),
Extract = Table.TransformColumns(Split, {{"raw.2", each Text.Start(Text.From(_, "en-GB"), 2), type text}}),
Merge = Table.CombineColumns(Table.TransformColumnTypes(Ex tract, {{"raw.1", type text}}, "en-GB"),{"raw.1", "raw.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result"),
Type = Table.TransformColumnTypes(Merge,{{"Result", Int64.Type}})
in
Type

rawResult


1090.699
109069


147.965
14796


264.4785
26447


30.2495
3024


should be ok
thanks Alan :cool:

[DocAElstein]

Hi
:confused:
I do not understand your question ??
I do not understand what you want.??



If column H of 1.xls is lower than column D of 1.xls then with column K (subtract it and make it to the number ending with 0 or 5 whichever met first)
If column H of 1.xls is greater than column D of 1.xls then with column K (add it and make it to the number ending with 0 or 5 whichever met first)r :confused: :confused:
That makes no sense. ?? It is rubbish - In English it makes no sense.

Your samples do not explain anything which happens to column K as a result of H or D. You explanation and your sample data do not seem to have any relevance. :confused:
Your explanation is no use.

But I can get your results with some maths… All of this is based on maths for column K. I see no relevance to column H or D ??

From your data

BeforeResult
After

1090.69
1090.65

147.96
147.95

264.47
264.45

30.24
30.2



_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
J
K
L
M
N
O
P
Q

1


21090.69
=K2*100/5
=INT(L2)
=M2*5/100
=INT(L2)*5/100
=INT(K2*100/5)*5/100


3147.96
=K3*100/5
=INT(L3)
=M3*5/100
=INT(L3)*5/100
=INT(K3*100/5)*5/100


4264.47
=K4*100/5
=INT(L4)
=M4*5/100
=INT(L4)*5/100
=INT(K4*100/5)*5/100


530.24
=K5*100/5
=INT(L5)
=M5*5/100
=INT(L5)*5/100
=INT(K5*100/5)*5/100


6
Worksheet: Tabelle1
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
J
K
L
M
N
O
P
Q

1


21090.69
21813.8
21813
1090.65
1090.65
1090.65


3147.96
2959.2
2959
147.95
147.95
147.95


4264.47
5289.4
5289
264.45
264.45
264.45


530.24
604.8
604
30.2
30.2
30.2


6
Worksheet: Tabelle1

[fixer]

Column H & Column D has relation bcoz Either Market can Move Up Or Down ,I have to Buy shares Or i have to sell shares
& why i need macro is
1090.69
147.96
264.47
30.24

After decimal if the number doesnt ends with .05 or .0 then that order is not accepted by system

[DocAElstein]

That does not explain anything about the question you asked. Once again we have the problem that you cannot communicate in English language.

I still do not understand
I still do not understand the relevance to the question http://www.excelfox.com/forum/showthread.php/2457-change-the-second-number-after-decimal-conditionally?p=13074&viewfull=1#post13074 . … I see not relation to your test data in H and D and the results which you ask for

I have no idea what you are asking for. :confused:
I have given you the results you asked for, but it has nothing to do with anything in column H and D. My results are what you asked for, but I do not consider anything in column H and D.

[fixer]

if column H of 1.xls is lower than column D of 1.xls then with column K do the subtraction after decimal and make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore that cell
If column H of 1.xls is greater than column D of 1.xls then with column K do the addition after decimal and make it to the number ending with 0 or 5 whichever met first and if it ends with .05 or .00 then ignore that cell

this is the conditon Doc Sir

[DocAElstein]

Sorry, I do not understand
What you wrote makes no sense in English.

Try to explain again with lots more detailed data.

Your screenshots are almost useless. They explain only the problem which I answered. They explain nothing about column H or D. They show only a change in column K. There is no explanation from you about the relavance to the question and column H or D.

You explanations so far are almost useless. They say almost nothing. Or they make no sense.. what does it mean...
do the subtraction :confused:
do the addition :confused:

[fixer]

column H is a Last traded price & column D is a opening price
from this we came to know about market is moving up or moving down in that situation , u cant rely on high or low bcoz it always makes a new high & new low
Doc Sir what information u need plz specify that so that i can answer the same bcoz i havent understood ur question yet sir





Moderator Translation:
I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a total fucking spacko ... and never mind anyway, fuck off you mug, as some other twat gave me a macro at one of my many duplicated cross postings.......( Intro to Avinash Singh... https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction )

[fixer]

Doc Sir as u said u understood the problem
so plz help me to solve this problem Sir

[DocAElstein]

It is almost impossible to communicate with you.

You are telling me things which appear to have no relevance to your question.
This I understand:
… column H is a Last traded price & column D is a opening price
from this we came to know about market is moving up or moving down in that situation , u cant rely on high or low bcoz it always makes a new high & new low
But I see no relevance to the question??


It is impossible to help you further. I cannot guess what you want.


Doc Sir as u said u understood the problem
so plz help me to solve this problem Sir
No . I did not say that. I do not understand the problem !!!!
what does it mean...
do the subtraction :confused:
do the addition :confused:

[DocAElstein]

I will look again tomorrow.
If , before tomorrow, you have explained fully , with test data what you want, then I will try to help further.

If, by tomorrow you have not explained fully what you want, then I will delete this Thread because it is full of rubbish.

Alan



Doc Sir what information u need plz specify that so that i can answer the same bcoz i havent understood ur question yet sir
My question is simple.

What do you want? What is your question?
So far all your explanations make no sense in English language

You tell me things about column H and D. But you do not tell me in English what is the relavance to your results and question

what does it mean...
do the subtraction :confused:
do the addition :confused:

[fixer]

Doc Sir plz give me 24hrs from now due to internet issue
i will share the sample file with all details in it
thnx for the support sir





Moderator Translation:
I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a total fucking spacko ... and never mind anyway, fuck off you mug, as some other twat gave me a macro at one of my many duplicated cross postings.......( Intro to Avinash Singh... https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction )

[DocAElstein]

OK, take your time. :)
( I am busy until late tommorrow )

[fixer]

Doc Sir plz see the sample file Sir
Each & Every details is mentioned with examples sir

[fixer]

No Problem Doc Sir take ur time







Moderator Translation:
I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a total fucking spacko ... and never mind anyway, fuck off you mug, as some other twat gave me a macro at one of my many duplicated cross postings.......( Intro to Avinash Singh... https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction )

[DocAElstein]

Hi
Your file helps , but only a little bit..
I still do not understand from your explanations… The problem is your explanations make no sense in English…
( In next post I try to guess what you want http://www.excelfox.com/forum/showthread.php/2457-change-the-second-number-after-decimal-conditionally?p=13099&viewfull=1#post13099 )

from your uploaded file explanations . This I do understand

compare column H with column D
there is only two option column H will be greater than column D or column H will be lower than column D
If column H is greater than column D then with column K do the addition after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
If column H is lower than column D then with column K do the subtraction after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
example

If column H is greater than column D then with column K do the addition after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
column H is greater than column D
so now we will check column K data is perfect or not means after decimal the second number is 5 or 0, here the data in column K is 264.47 so the second number after decimal is 7 but we need 5 or 0 ,so as per condition if column H is greater than column D then we have to do addition we cant do subtraction so with 264.47 (264.48,264.49,264.50) so we got first 264.50
so the data will be in column K 264.50

If column H is lower than column D then with column K do the subtraction after decimal & make it to the number ending with 0 or 5 whichever met first & if it ends with .05 or .00 then ignore
column H is lower than column D
so now we will check column K data is perfect or not means after decimal the second number is 5 or 0, here the data in column K is 1090.69 so the second number after decimal is 9 but we need 5 or 0 ,so as per condition if column H is lower than column D then we have to do subtraction we cant do addition so with 1090.69(1090.68,1090.67,1090.66,1090.65) so we got first 1090.65
so the data will be in column K 1090.65

wheather column H is greater than column D or column H is lower than column D
if after decimal the second number in column K has data ending with 0 or 5 already then don’t change the data
see the blue colour data
here column K has 147.95 so after decimal the second number is 5 so we will not do anything with column K data

wheather column H is greater than column D or column H is lower than column D
if column K doesn’t have decimal then don’t change the data
here column K has 30 so it doesn’t have decimal so we will not do anything with column K data


All this I done for you: http://www.excelfox.com/forum/showthread.php/2457-change-the-second-number-after-decimal-conditionally?p=13076&viewfull=1#post13076
Again
Before
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )
Row\Col
K

2
1090.69


3
147.95


4
264.47


5
30


After
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )


1090.65


147.95


264.5


30


Formula solution from here: http://www.excelfox.com/forum/showthread.php/2457-change-the-second-number-after-decimal-conditionally?p=13076&viewfull=1#post13076
_____ Workbook: SAMPLE1 18Apr2020.xlsx ( Using Excel 2007 32 bit )


21813.8
21813
1090.65
1090.65
1090.65


2959
2959
147.95
147.95
147.95


5289.4
5289
264.45
264.45
264.45


600
600
30
30
30



I do not understand … :confused:
What is Do addition ???
What is Do subtraction ??

I still do not understand what has H and D to do with the question????
I see no relevance to column H and column D to the problem
what does it mean...
do the subtraction :confused:
do the addition :confused:

How does column H and column D effect the answer ???

What does all the addition and subtraction mean ???
what does it mean...
do the subtraction :confused:
do the addition :confused:

How does column H and D effect the problem ??????



From your explanations it is very difficult to understand anything
But…

In next post I try to guess what you want http://www.excelfox.com/forum/showthread.php/2457-change-the-second-number-after-decimal-conditionally?p=13099&viewfull=1#post13099

[DocAElstein]

The problem is again , as always , that you cannot explain your problem in English.
Also you did not take enough care with your choice of original test data. You original test data did not test all scenarios.
You do not take enough time to prepare your question. This means that we both waste a lot of time later.
If you continue to give such bad information, it will not be possible for me to find lots of time to waste trying to understand your question!!


I will make a guess at the question:

This is my guess at what you want:
Explanation 1
In column K are numbers given to a maximum of 2 decimal places, for example
Column K
1090.69
147.95
264.47
30

The value in Column K must be adjusted so that it has the decimal format to 2 decimal places in steps of .05
So in this form, of like
23.95
234
34.25
4.30
100.35
45.45
56.05 ……… etc….

So for example, in the above Column K test data, no adjustment is needed for 147.95 or 30
For 1090.69 and 264.47 some adjustment is needed. The adjustment could be to raise or lower the value. These are the possibilities:
change 1090.69 to 1090.65 or 1090.7
change 264.47 to 264.45 or 264.50

Which of the two adjustments is necessary will depend on the following:
If column H is greater than column D , then we adjust up .
If column H is lower than column D, then we adjust down .

Explanation 2
For all data rows, we compare column H to column D. If column H is greater than column D , then we adjust the value in column K up to the nearest multiple of .05. If column H is less than column D , then we adjust the value in column K down to the nearest multiple of .05. ( If the value in column K is an exact multiple of .05, then no action is to be taken )

_.________

For example
Before:
Row\Col
D
E
F
G
H
I
J
K

2
1087
1088
1077.25
1067.25
1079.9
25
10.799
1090.69


3
148.05
149.9
146.5
146
146.5
22
1.465
147.95


4
265
269.3
265
262.85
267.15
15083
2.6715
264.47


5
30.4
30.4
29.8
29.65
29.95
17388
0.2995
30


After:
Row\Col
D
E
F
G
H
I
J
K
L

2
1087
1088
1077.25
1067.25
1079.9
25
10.799
1090.65This nuber is adjusted down


3
148.05
149.9
146.5
146
146.5
22
1.465
147.95This number is not changed


4
265
269.3
265
262.85
267.15
15083
2.6715
264.5This number is adjusted up


5
30.4
30.4
29.8
29.65
29.95
17388
0.2995
30This number is not changed



Solution ( guess )
See next post:

[DocAElstein]

Solution ( guess )
The previous formula solution already always adjust number down,

Row\Col
D
H
K
L
M
N
O
P

2
1087
1079.9
1090.69
21813.8
21813
1090.65
1090.65
1090.65


3
148.05
146.5
147.95
2959
2959
147.95
147.95
147.95


4
265
267.15
264.47
5289.4
5289
264.45
264.45
264.45


5
30.4
29.95
30
600
600
30
30
30

Row\Col
D
H
K
L
M
N
O
P

2
1087
1079.9
1090.69
=K2*100/5
=INT([COLOR="#0000FF"]L2)
=M2*5/100
=INT(L2)*5/100
=INT(K2*100/5)*5/100


3
148.05
146.5
147.95
=K3*100/5
=INT(L3)
=M3*5/100
=INT(L3)*5/100
=INT(K3*100/5)*5/100


4
265
267.15
264.47
=K4*100/5
=INT(L4)
=M4*5/100
=INT(L4)*5/100
=INT(K4*100/5)*5/100


5
30.4
29.95
30
=K5*100/5
=INT(L5)
=M5*5/100
=INT(L5)*5/100
=INT(K5*100/5)*5/100

The above solution is .05 too small for row 4

So previous solution is correct if H < D
If H > D , the previous solution is .05 too small , so previous solution must be adjusted by +.05 if H > D


=IF(H2<D2,INT(K2*100/5)*5/100,IF(H2>D2,(INT(K2*100/5)*5/100)+0.05,"H is equal to D"))


=IF(H3<D3,INT(K3*100/5)*5/100,IF(H3>D3,(INT(K3*100/5)*5/100)+0.05,"H is equal to D"))


=IF(H4<D4,INT(K4*100/5)*5/100,IF(H4>D4,(INT(K4*100/5)*5/100)+0.05,"H is equal to D"))


=IF(H5<D5,INT(K5*100/5)*5/100,IF(H5>D5,(INT(K5*100/5)*5/100)+0.05,"H is equal to D"))


But we must also check if number is already exact multiple of .05
Like if ( integer (value/.05))

[fixer]

Thnx Doc Sir for showing ur great interest to solve this problem
& Doc Sir as u know i am not that smart that i can make this complicated vba code for me so plz help sir








Moderator Translation:

I cant speak , write or understand a word of English I haven’t a clue what is written in this Thread, most of the time I haven’t got a clue what I wont and rarely even understand what I write myself, so I will post any rubbish and hope magically a code appears here that when I run it will do what I want. If not fuck the twat helping me here: I have duplicated cross posted all over the place so maybe from there something will come of it.

[DocAElstein]

.. as u know i am not that smart..You are smart enough to get others to do your work .. maybe :)
आपण मुर्ख नाही कारण आपण इंग्रजीमध्ये संप्रेषण करू शकत नाही. मी मराठी किंवा हिंदी बोलू शकत नाही. पण या कारणास्तव मी मूर्ख नाही

I try this as the Question…..
Explanation 2
For all data rows, we compare column H to column D. If column H is greater than column D , then we adjust the value in column K up to the nearest multiple of .05. If column H is less than column D , then we adjust the value in column K down to the nearest multiple of .05. ( If the value in column K is an exact multiple of .05, then no action is to be taken )

VBA answer

Put columns in arrays
Row\Col
D

1Open


2
1087


3
148.05


4
265


5
30.4

arrD() =


1087


148.05


265


30.4



Row\Col
H

1LTP


2
1079.9


3
146.5


4
267.15


5
29.95

arrH() =


1079.9


146.5


267.15


29.95



Row\Col
K

1


2
1090.69


3
147.95


4
264.47


5
30

arrK() ( initial ) =


1090.69


147.95


264.47


30


The macro below manipulates the contents of arrK() as per the question requirement, then pastes the modified array over the initial values
Macro here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13105&viewfull=1#post13105

After running that macro, Sub ChangeSecondNumberAfterDecimalConditionally() , the arrK() contents change to


1090.65


147.95


264.5


30


And that is then pasted out into the range
Row\Col
K

1


2
1090.65


3
147.95


4
264.5


5
30

[DocAElstein]

Another obvious solution which I missed…
Row\Col
K
L

2
1090.699
109069


3
147.965
14796


4
264.4785
26447


5
30.2495
3024

Row\Col
K
L

2
1090.699
=INT(K2*100)


3
147.965
=INT(K3*100)


4
264.4785
=INT(K4*100)


5
30.2495
=INT(K5*100)


And thank you Mr Sandy sir, for your interesting alternative solution ;) :) :cool:

[fixer]

Thnx Sandy Sir for ur great help

[fixer]

..You are smart enough to get others to do your work
Yes I am Smart enough Doc Sir to get others to do my work
But No Doubt Doc Sir The work that they do is priceless
If i were smart then i can make a vba code for me %O

Problem Solved Doc Sir thnx Alot for giving ur Precious time & Great Support

[fixer]

Calculate 2% of colum H & column I & considered the greater number between them
column S should be positive, so don't considere the no. which are negative
& if column S is lower than that 2% of column H or Column I (whichever is greater )then put -1
vba macro will be placed in a seperate file , sheet name can be anything, all files are located in different place
example
the U2 cell will become -1 after runing the macro






Similar to this from MoldyBread at excelforum
https://www.excelforum.com/excel-programming-vba-macros/1317283-compare-columns-and-based-on-that-increase-or-decrease-the-column-data-by-percentage.html#post5338184

[DocAElstein]

Hi

Before

_____ Workbook: ap.xls ( Using Excel 2007 32 bit )
Row\Col
H
I
J
K
L
M
N
O
P
Q
R
S
T
U

1NetBuyValueNetSellValueNetValueNetBuyQtyNetSellQt yNetQtyBEPSellAvgPriceBuyAvgPriceLastTradedPriceMa rkToMarketRealized MarkToMarketUnrealized MarkToMarketEL MarkToMarket


2
10781.10
10878.30
97.20
54
54
201.45
199.65
201
97.2
97.2
97.2


3
420.60
430.50
9.90
2
2
215.25
210.30
210.35
9.9
9.9
9.9


4
2429.10
2405.70
-23.40
18
18
133.65
134.95
135
-23.4
-23.4
-23.4


5
6120.90
6064.20
-56.70
54
54
112.30
113.35
111.6
-56.7
-56.7
-56.7


6
66.30
65.70
-0.60
2
2
32.85
33.15
32.5
-0.6
-0.6
-0.6


7
15114.60
14966.10
-148.50
54
54
277.15
279.90
279.15
-148.5
-148.5
-148.5


8
570.60
567.00
-3.60
6
6
94.50
95.10
93.35
-3.6
-3.6
-3.6


9
7344.00
7272.40
-71.60
54
54
134.67
136.00
138.5
-71.6
-71.6
-71.6


10
Worksheet: ap-Sheet1

After

_____ Workbook: ap.xls ( Using Excel 2007 32 bit )
Row\Col
U

1EL MarkToMarket


2
-1


3
9.9


4
-23.4


5
-56.7


6
-0.6


7
-148.5


8
-3.6


9
-71.6


10
Worksheet: ap-Sheet1


Macro here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13424&viewfull=1#post13424


Alan

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem Sir
Have a Awesome Day


1 June : https://www.eileenslounge.com/viewtopic.php?f=30&t=34710

[fixer]

Hi Experts,
I am looking for a macro that will do the things mentioned below

Check column H of 1.xls has how many number before decimal (If decimal is not present then check how many numbers it has )
after knowing this, match column I of 1.xls with Column B of 2.xlsx & if matched then put decimal in column E of 2.xlsx after that many numbers (if decimal is not present) & if decimal is present then remove that decimal & reput the decimal in column E of 2.xlsx according to the numbers

Plz see the sample file


Macro will be placed in macro.xlsm
both file can be located anywhere in the pc so the path should be hardcoded in the macro so that i can change it as per my needs in the future

Thnx For the Help

Any Doubts Any Question Plz Ask

[DocAElstein]

I assume that we do nothing if there is no match in data from column I 1.xls in the column B in sample2.xlsx

So, another way of explaining the requirement is
Try to find a match in the data from each row, starting at 2 of column I 1.xls in column B 2.xlsx
If we have a match, then we find the length of the integer of the value for the H ( in variable, LHint ) in 1.xls for that row
Once we have that we first, remove any decimal place in the matched row in 2.xlsx in column E
What we then do is convert that number in the matched row in 2.xlsx in column E to effectively be
_______________= the first LHint characters of it & a decimal point & then the rest of the characters
__ arr2E(MtchRes, 1) = Left(arr2E(MtchRes, 1), LHInt) & "." & Mid(arr2E(MtchRes, 1), LHInt + 1)





Before:
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ12651282.71246.51275.3124722BUY116761.05

3NSEADANIENTEQ151.85165.45151.4151.85152.3525BUY14 104.65

4NSEADANIPORTSEQ348348338.5346.55338.8515083BUY323 5.03

5NSEADANIPOWEREQ38.8538.937.6538.837.8517388SHORT3 469.653

6NSEAMARAJABATEQ662.5665.9642.55662.5643.5100BUY56 80.24
Worksheet: 1-Sheet1 18July

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1NSE226<115605AGTT

2NSE256<13965AGTT

3NSE150836<3203AGTT

4NSE173886>3504.7AGTT

5NSE1006<5624AGTT
Worksheet: Sheet1 18 July

After:

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1NSE226<1156.05AGTT

2NSE256<139.65AGTT

3NSE150836<320.3AGTT

4NSE173886>35.047AGTT

5NSE1006<562.4AGTT
Worksheet: Sheet1 18 July


Macro Here : https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14674&viewfull=1#post14674

[fixer]

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem
Have a Awesome Day Sir

[fixer]

Hi Experts,

I am looking for a macro that will do the the things mentioned below
Macro will be placed in a macro.xlsm
macro.xlsm & 1.xls both are located in different path so the path should be hardcoded in the macro, so i can change it as per my needs


In column K, If after decimal if the second digit is 0 then Move all the data from after decimals to before decimals
K2 has data that has the second number after decimal is 0, so the output would be in K2 10030.00


Thnx For the Help
https://www.excelforum.com/excel-programming-vba-macros/1325615-if-decimal-found-then-move-the-data-from-after-decimal-to-before-decimal.html

[fixer]

Problem Solved

[DocAElstein]

The macro that you obtained from excelforum just multiplies all the column K values by 100. Your description suggests that you did not want just that.

[fixer]

Yes Sir, I got the macros for the same Sir
Thnx Alot Sir for ur Great Help Sir
Have a Awesome Day Sir