from sir olayskie's formula: just added Find function. let us know if its working...
=IF(LEFT(A1,3)="110",MID(A1,5,FIND("-",A1,5)-5),"")
Kircchoffs, try mo rin pglaruan ang mga string functions. it will help u a lot in simple formulas like this one.
Forum Topic
excel macro programming
-
@olayskie and dodick
thanks mga sir both working po sila ^_^ - Post deleted #11873661
-
mga sir need your help po ulit para sa new project ko
-
=if(D2 = "x", E2, D2* E2)?
-
=if(D2 = "x", E2, D2* E2)?
di po working sir. 3 po kasi yung variable, LEVEL, ITEM at QTY.
anyway may tumulong na rin po saken sa ibang thread. ^_^
=IF(AND(B2=1,D2="X")=TRUE,E2,IF(INDIRECT("D"&ROW()-1)="X",E2*INDIRECT("E"&ROW()-1),IF(INDIRECT("D"&ROW()-2)="X",E2*INDIRECT("E"&ROW()-2),IF(INDIRECT("D"&ROW()-3)="X",E2*INDIRECT("E"&ROW()-3),IF(INDIRECT("D"&ROW()-4)="X",E2*INDIRECT("E"&ROW()-4),IF(INDIRECT("D"&ROW()-5)="X",E2*INDIRECT("E"&ROW()-5),"array")))))) -
Tanong: Meron akong date column with the following format 02-05-2017 8:54:00 AM I need to add another column that should have either a Y or N. The rule is if yung date column is equivalent to yesterdays date then dapat Y if not then N. Salamat in advance.
-
@Crash,
easiest way is to extract the day with this function @DAY(date) then create an IF statement as shown below:
A1
2/5/17 8:54 AM
B1
2/4/17 12:00 AM (for comparison)
C1
=IF(DAY(A1)-1=DAY(B1),"Y","N")
Just note that this formula assume the comparison as same month and year. Hope it helps... -
=if(D2 = "x", E2, D2* E2)?
di po working sir. 3 po kasi yung variable, LEVEL, ITEM at QTY.
Should be working since the 2 variables are just the same.
B and D columns can be used since X is always equal to 1. -
Kaya lang hindi nag work kasi wala pang value yung blanks mo sa column D.
-
any tips on "where to start" for VBA para sa beginners?
-
Should be working since the 2 variables are just the same.
B and D columns can be used since X is always equal to 1.
Di talaga magwowork yun Sir, kasi nakabase yung first factor sa prior Level 1 niya (E2, E8, E14, and so on). And mali rin po yung D2*E2 kasi ang value ng D2 ay "x" or blank lang.
If nagwowok na yung may array and INDIRECT, thats' good. :) Medyo nakukumplikaduhan lang ako, baka may mas simpleng way. :)
Hello pala! ngayon ko lang nakita na may ganitong thread. :) -
Tanong: Meron akong date column with the following format 02-05-2017 8:54:00 AM I need to add another column that should have either a Y or N. The rule is if yung date column is equivalent to yesterdays date then dapat Y if not then N. Salamat in advance.
=IF(DATE(YEAR(A1),MONTH(A1),DAY(A1))=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-1),"Y","N")
A1 sir yung column ng dates niyo. But note sir na yung Today() ay nagbabago bago bawat araw. :) You can replace today() by a cell kung saan nakalagay yung reference date. :) -
any tips on "where to start" for VBA para sa beginners?
start by using macro, record ka ng mga simple tasks (e.g. Find&Replace, Text to Columns, select ranges and so on..). then from there, edit mo ung macro at dun ka mgstart pumickup ng mga coding and eventually you are already learning vba... maraming approach na pwede, mas maige prin yun learn by sample/application. remember nandyan naman palagi si F1 and/or Google... good luck. -
Di talaga magwowork yun Sir, kasi nakabase yung first factor sa prior Level 1 niya (E2, E8, E14, and so on). And mali rin po yung D2*E2 kasi ang value ng D2 ay "x" or blank lang.
No...try the formula first... or pwede dagdagan pa ng isang condition na pag blank eh kunin niya yung last column value
tignan mong maigi... 2 levels lang meron
1&2 and X = 1 and blank = 2
so if we consider the blank space sa column D
Per se ganito ang pinaka simple
Formula nested if's
=IF(D10="x",E10,IF(D10="",E10,D10*E10))
potek nawala net kahapon... eto ang formula para dyan talaga...tinanggal ko kasi yung mga hindi kelangan
-- edited by emcel on Apr 27 2017, 01:47 PM -
Mga Sir pa help naman po about excel. Kanina pa ko nag search sa google pero wala ako makita exact scenario sa problem ko.
Gusto ko sana i match ung first 4 characters sa column A sa column B
then ung match pupunta sa column C, yung di match ay iwan lang.
Bale yung column A ang pipiliin at yung column B ang pagpipipilian
let say 100 items sa column A, then 500 items sa column B
TIA -
=IF(C3="x",D3,IF(C3="",D3,C3*D3))
Sir emcel, sorry naconfuse ako sa references niyo. C Column is Part Number and D column is Item. Do you mean C3 is D3 and D3 is E3 instead?
I got your point sir, pero tingnan mo maigi yung Column F (Expected Result), From row 2 to row 7, sa E2 siya nakamultiply, from row 8 to 13 sa E8 siya nakamultiply, and so on. So not applicable yung formula niyo Sir dahil hindi fix yung reference niya. Saan ba siya nakareference? Sa previous na Level 1 or "x" Item niya.
-- edited by johns06 on Apr 27 2017, 12:37 PM -
Mga Sir pa help naman po about excel. Kanina pa ko nag search sa google pero wala ako makita exact scenario sa problem ko.
Gusto ko sana i match ung first 4 characters sa column A sa column B
then ung match pupunta sa column C, yung di match ay iwan lang.
Bale yung column A ang pipiliin at yung column B ang pagpipipilian
let say 100 items sa column A, then 500 items sa column B
TIA
Sir dekisugi, can you give simple example to your prob? Sorry, di ko pa kasi mavisualize. Thanks Sir. -
=IF(AND(B2=1,D2="X")=TRUE,E2,IF(INDIRECT("D"&ROW()-1)="X",E2*INDIRECT("E"&ROW()-1),IF(INDIRECT("D"&ROW()-2)="X",E2*INDIRECT("E"&ROW()-2),IF(INDIRECT("D"&ROW()-3)="X",E2*INDIRECT("E"&ROW()-3),IF(INDIRECT("D"&ROW()-4)="X",E2*INDIRECT("E"&ROW()-4),IF(INDIRECT("D"&ROW()-5)="X",E2*INDIRECT("E"&ROW()-5),"array"))))))
Sir Kircchoffs, I tried to test the suggested formula above. Please note na may limit itong formula ito. Upto 5 rows above lang niya ang kaya niyang icheck if may "x", kapag more than that , "array", na ang ilalabas niyang result. So kay Cell F32 and F33, "array" na ang ilalabas niyan dahil more than 5 rows above na yung previous "x" niya. Thus, hindi flexible yung formula.
Here's an alternative if adding a helper column is possible.
Lets add a helper column in column G and the formula for G2 is ="x"&COUNTIF($D$2:D2,"x").
Then the formula for Column F (Expected Result), in cell F2 =E2*IF(D2="x",1,INDEX(E:E,MATCH(G2,G:G,0)))
Tapos copy down mo na. Yan sky's the limit na yan and no need for indirects also. HTH. :) -
I got your point sir, pero tingnan mo maigi yung Column F (Expected Result), From row 2 to row 7, sa E2 siya nakamultiply, from row 8 to 13 sa E8 siya nakamultiply, and so on. So not applicable yung formula niyo Sir dahil hindi fix yung reference niya. Saan ba siya nakareference? Sa previous na Level 1 or "x" Item niya
I stand corrected... hindi ko binasa yung dulo hahaha... hindi pala naka fix. -
I stand corrected... hindi ko binasa yung dulo hahaha... hindi pala naka fix.
Hahaga ganun din akala ko Sir, pero nung nakita kong may Indirect na ginamit, nagtaka na ako kaya tinitigan ko uli. Haha Thanks sir. -
The Benefits of EpiClear Pro There are many benefits to be had when you add Epiclear Pro into your daily skincare routine. It means that if you are not satisfied with its results you can easily get all your money refunded!! Under eye circles clears Another advantage to this formula works well to eliminate under-eye circles that have developed over the years. All you need to do is just follow the below steps on a regular basis:- Step 1: Clean the affected area thoroughly using an antiseptic soap or body wash and pat dry that area with a soft towel. What does it mean to say what you mean when that provides so much info with reference to <click here for link>? The product has also been appreciated for its ability to minimize fine lines and wrinkles, as well as eliminate dark circles and under-eye bags.
Browse this page for more detais>>http://www.healthdietalert.com/epiclear-pro/ -
@johns06 salamat sir, and yes yun ang idea na yung actual date ang gagamitin since daily mag run yung report.
-
@CrashAndBurn, alright! :) Glad to help.
-
@johns06
Sir ganito po,
*Sa column A nakalagay ay name. For example nba players. approx 100 nba players
*Sa column B nakalagay ay nba players name din. Pero mas specific. For ex: GS Warriors approx 15 names
What i want to do is:
- i match ko yung first 4 or 5 letters ng players name sa column A kay B.
then yung maiiwan, mapupunta sa column C
-May sumama kasing ibang characters after the first 4 or 5 characters
-
^
Add ko lang sa taas..
dapat mag match yung column A sa Column B, then malipat sa Column C yung hindi match
Ex:
Column A Column B Column C
DURANT DURANT WADE
GREEN GREEN HOWARD
CURRY CURRY HAYWARD
THOMPSON THOMPSON BUTLER
CLARK CLARK THOMAS
MCGEE MCGEE JAMES....and so on.. -
@dekisugi
Hmmm.. Mukhang di madadaaan sa formulas ito sir. Mukhang need mo itong i-macro.
1.) Ang ibig mo ba sabihin ay example
Let x1, x2, and so on be player names
Before:
Column A: {x1, x2, x3, x4, x5}
Column B: {x2, x5}
After:
Column A: {x2, x5}
Column B: {x2, x5}
Column C: {x1, x3, x4}
Ganun ba sir?
2.) kailangan magkamukha na yung A & B? and same order?
3.) Yung mga A na hindi match sa B mapupunta sa C, pero paano yung mga nasa B na hindi match sa A? Saan mapupunta? or subset ba ng A yung B (meaning lahat ng nasa B ay nasa A, master list si A)?
Sorry Sir, di kita mahelp right of the bat.
EDIT: Ooops may image ka palang sinama, di kasi nagloload images dito sa office. haha Pero pakisagot na rin mga question ko sir. Thanks!
-- edited by johns06 on Apr 28 2017, 12:48 PM
-- edited by johns06 on Apr 28 2017, 12:48 PM -
Sir johns06
Simplehan ko na lang po yung gusto kong gawin. Pwede na din wala ng column C.
Ex: Sa Column A, ilista natin lahat ng active NBA players.
Sa Column B, let say gusto ko kunin yung Golden State Warriors players. Meaning more or less 12-15
players ang nasa column B.
Dapat sa column A, aangat sa list yung GSW players, katapat ng GSW players sa column B, in exact order po dapat.
Then yung maiiwan, means other NBA teams sila (not GSW) so bale nasa ilalim lang sila ng
Column A
Ano po ba pinaka simple at pinakamabilis na paraan pag match sa col A at Col B.
Salamat po sir!
-- edited by dekisugi on Apr 28 2017, 08:53 PM -
@dekisugi
Eto sir ang initial kong nasisip na paraan. Possible na formula based lang siya if magdadagdag tayo ng helper column, let's call it "Helper" Column.
Gawin nating si Helper Column ay nasa Column A, MasterList ay sa Column B, at Wanted List sa Column C.
Ang formula ng helper column ay (sa row 2 nagstart yung names, row 1 ay headers)
=IFNA(MATCH(B2,C:C,0),"x")
Then copy down mo. Ang magkakanumbers lang ay yung maga nasa selected columns, the number represent kung anong row sila sa Column C. If wala sa Column C, "x" ang lalabas.
After nito, iselect or highlight mo si column A and B (make sure na di kasama column C), press mo Alt+D+F+F (press Alt button, tapos D, tapos F, Tapos F). Shortcut ng Filter yun.
So filtered na si Column A and B. Click mo drop down ni column A, then select Sort A to Z. Ayun, arranged na siya in a way na exact order ng Column C and nasa ilalalim na yung Others.
The other way is to create a VBA macro na di ko na kaya iexplain dito. Hahaha
If magkaprob ka sir, PM mo na lang ako. Send mo sa akin sample file. -
dapat mag match yung column A sa Column B, then malipat sa Column C yung hindi match
If formula lang kelangan mo
Ilagay mo to sa C column
=IF(A1=B1,"",A1)
-- edited by emcel on Apr 29 2017, 04:27 PM