Excel Function extraction based on delimeter

Good day,

Today i am presented with thousand entries of data and i am interested in presenting information based on the content of two columns in this nature.

This is like pulling first name and surname stuff only i am doing a conditional concatenation of the source data from two different sources.

Column 1   Column 2
A/B             1,C
A/B             2
A/B             3
A/B             4,D
A/B             5,E
A/B            6
A/B            7,F
A/B            8,G
A/B            9,H


And the logic is if Column 2 has a comma -> content after C should be substituted for content in Column 1 after the forward slash "/"

let column1 be C5,C6....
and column 2 be D5,D6.....


=CONCATENATE(TRIM(LEFT(C5,FIND("/",C5))),IFERROR(TRIM(RIGHT(D5,LEN(D5)-FIND(",",D5))),TRIM(RIGHT(C5,LEN(C5)-FIND("/",C5)))))

trim is put in to eliminate any white space that may occur.

This formula does it, constraint column 1 must always have a "/" present, however a logic case can be built like as applies to column 2 to take care of the absence or presence of a "/" in column 1.

Cheers!
Kodako!

HTML 5 download attribute

Glad i found this resource it would reduce server side php force download links




TAG a href="link" download="to be saved"Visible link TAG
this would either ways call a stream type that would invoke a save as dialog box.


Currently Chrome is supported.

LOL

Promises worth remembering

I would never take advantage of you
My heart would always be open to you

My function of the day

Excel land today i had to create a uique column based on two columns that have non-continguous data (a blank space if the above row applies for the current row)


so i have  a set like

List 1   List 2      Results
20W   Fridge
           Freezer
30W
30W   Cxxxx
40W          
40W   xols
50W


where C8 and B8 are List 1 and List 2

=CONCATENATE((IF(C8="",(LEFT(G7,FIND("-",G7)-1)),C8)),"-",(IF(B8="",(RIGHT(G7,LEN(G7)-FIND("-",G7))),B8)))


results would be like 20W-Fridge, 20W-Freezer,30W-Freezer,30W-Cxxxx,40W-Cxxx,etc

Calculate TAT based on working hours(8am - 5pm), Microsoft Excel

I needed to calculate Turn Around Time from time stamps recorded at a site,

I have entry date and time, and exit date and time.

I adjust entry time so that all adjusted entry times are within 8am and 5pm: anytime less than 8am but greater than 12am would be adjusted to 8am whiles any time greater than 5pm but less than 12am would be adjusted to next day 8am

here is the formula =TRUNC(G43)+(IF((G43-TRUNC(G43))>=0.708332696762227,1.33333321758982,IF((G43-TRUNC(G43))<=0.333333217589825,0.333333217589825,(G43-TRUNC(G43)))))

Now to calculate the TAT a simple logic case of if adjusted entry time is greater than exit time use entry time instead

here is the formula =IF(J43>H43,H43-G43,H43-J43)


G43 = entry time
H43 = exit time
J43 = adjusted entry time
0.708332696762227 = time value for 5pm
0.333333217589825 = time value for 8am

Cheers!
Kodako!

Digging wells

Proverbs 5:15-23

New International Version (NIV)

¹⁵ Drink water from your own cistern,
    running water from your own well.
¹⁶ Should your springs overflow in the streets,
    your streams of water in the public squares?
¹⁷ Let them be yours alone,
    never to be shared with strangers.
¹⁸ May your fountain be blessed,
    and may you rejoice in the wife of your youth.
¹⁹ A loving doe, a graceful deer—
    may her breasts satisfy you always,
    may you ever be intoxicated with her love.
²⁰ Why, my son, be intoxicated with another man’s wife?
    Why embrace the bosom of a wayward woman?

²¹ For your ways are in full view of the Lord,
    and he examines all your paths.
²² The evil deeds of the wicked ensnare them;
    the cords of their sins hold them fast.
²³ For lack of discipline they will die,
    led astray by their own great folly.

Deligence by compulsion

There are times we ask where is God?
But He is here, and and these are a few things He requires of us.

*[[Pro 4:18-27]] ISV*
18% The path of the righteous is like the light of dawn that grows brighter until the full light of day.
19% But the way of the wicked is like deep darkness, and they do not know what they are stumbling over.
20% My son, pay attention to my words, and listen closely to what I say.
21% Do not let them out of your sight; keep them within your heart.
22% For they are life to those who find them, and healing to their whole body.
23% Above everything else guard your heart, because from it flow the springs of life.
24% Never talk deceptively and don’t keep company with people whose speech is corrupt.
25% Let your eyes look directly ahead; fix your gaze straight in front of you.
26% Carefully measure the paths for your feet, and all your ways will be established.
27% Do not turn to the right or to the left; turn your foot away from evil.

Hurt

I will honour the Lord even when it hurts, i will not hurt anyone, i will do what is best for others in the light of God's word.

A sequel to [In the Valley]

This is a sequel to In the valley

I woke up this morning with tears, my eyes were filled with tears.

My time with the Lord resulted in tears, i cried because "i can't let go" and really wish it was different.

I had a struggle with God on the subject matter, i asked the Lord several times what i should do, then, i asked Him to speak explicitly to me about the matter. [ I heard Him and asked Him to confirm by His word, the Bible]

He said to me - let go of your intentions and keep calm.

These were the verses that confirmed what i had to do:

Genesis 29:11

New International Version (NIV)

¹¹ Then Jacob kissed Rachel and began to weep aloud.

Genesis 26:9

New International Version (NIV)

⁹ So Abimelek summoned Isaac and said, “She is really your wife! Why did you say, ‘She is my sister’?”

Isaac answered him, “Because I thought I might lose my life on account of her.”

Genesis 20:3-8

New International Version (NIV)

³ But God came to Abimelek in a dream one night and said to him, “You are as good as dead because of the woman you have taken; she is a married woman.”

⁴ Now Abimelek had not gone near her, so he said, “Lord, will you destroy an innocent nation? ⁵ Did he not say to me, ‘She is my sister,’ and didn’t she also say, ‘He is my brother’? I have done this with a clear conscience and clean hands.”

⁶ Then God said to him in the dream, “Yes, I know you did this with a clear conscience, and so I have kept you from sinning against me. That is why I did not let you touch her. ⁷ Now return the man’s wife, for he is a prophet, and he will pray for you and you will live. But if you do not return her, you may be sure that you and all who belong to you will die.”

⁸ Early the next morning Abimelek summoned all his officials, and when he told them all that had happened, they were very much afraid.

Verdict is clear, i will always love those i have professed to love, and i'll at all costs love as i ought to.

Let this not be said about you, but said about you.

*[[Pro 3:32]] ISV* Indeed, a perverse man is utterly disgusting to the LORD, but he takes the upright into his confidence.

A necessary rite of passage

Biblical Dating: Principles for Drawing Boundaries

My conviction

*[[Psa 9:9]] ISV* The LORD is a refuge for the oppressed, a refuge in times of distress.

In the valley

Valleys represent times of lowness, depression, or just an undesireable situation.

As i type i find myself in a valley, one that a part of me would be happy to stay in because it presents to me some possibilities that my lower self would be happy to indulge in whereas the higher self would prefer to come out of the valley and address the issue differently.

The honest truth is, i think i know what is happening but yesterday i was told "you don't know what you are talking about, you don't know......" maybe it is true, but whatever i am feeling is real, let me give it time to see if it is not real.

How did it start? Just as every relationship starts with meeting someone, so has this one, this took a quick rise unto a plateau of deep emotional thoughts and feelings.

I am content because I know it is real and true and my deepest desire would be to carry on but for us to do things the right way as we have started or ended.

You may be as confused as I am but i am glad for your honesty and openness, that means a lot to me.

I don't know the outcome yet, but i know we need strength to overcome and be true friends, that are noble, and would not succumb to the preliminary desires of the flesh.

Someday i would look back to this post and whatever the decision is, we'll laugh it off or ......

Just a hint to myself incase i don't remember why i posted this post (enebay hlday, iglatinpay)

Kodako.