Results 1 to 5 of 5

Thread: Obtain last inmediatly value with certains conditions on changing column In XL07

  1. #1

    Talking Obtain last inmediatly value with certains conditions on changing column In XL07



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,


    I have one worksheet. I am needing to match up column G with value on E3 and with value E4 and give consecutive numbers on column F depending if the matched column G with value on E3/E4 is on same date of column L and if column Q (Tipo de movimiento) appers "Ventas"; I have plenty blankcells and other values on column G.
    I trying catch up with excel..
    Thanks 4 your help
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Try this formula in F2:

    =IF(G2="","",IF(AND(ISNUMBER(MATCH(G2,E$2:E$3,0)),Q2="Ventas"),IF(LOOKUP(2,1/(F$1:F1<>""),L$1:L1)=L2,LOOKUP(10^10,F$1:F1)+1,1),""))

    copied down


  3. #3
    Hi NBVC,

    It helps me lot your formula but I have another last inquiry, as you can see there are 2 variables that Im looking for (E2 and E3) you help me to take both of them like the same but they must be counted diferent.. I mean, if excel find the 1st E$2will put 1 and continue counting E$2 but if finds E$3 has to count from 1 to any as the date permit.
    Sorry for my non-well explanation but I'm caching up also with that..
    Thanks in advance..
    Last edited by NBVC; 2014-05-27 at 04:42 PM. Reason: correcting spelling of my name :p

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Sorry for the late reply....



    Try in G3 or your attachment:

    =IF(ISNUMBER(MATCH(G3,$E$3:$E$4,0)),COUNTIFS($G$3:$G3,$G3,$L$3:$L3,$L3,$Q$3:$Q3,"Ventas"),"")

    copied down.

    P.S.

    In case you are also interested in my initial interpretation again - combining E2 and E3 (i.e. Post # 2), I realized after creating my formula above, that my original formula can be simplified to a similar formula... i.e.

    =IF(ISNUMBER(MATCH(G3,$E$3:$E$4,0)),SUMPRODUCT(COUNTIFS($G$3:$G3,$E$3:$E$4,$L$3:$L3,$L3,$Q$3:$Q3,"Ventas")),"")
    Last edited by NBVC; 2014-05-27 at 04:41 PM.


  5. #5
    Hi NBVC,

    The 1st one works perfectly, I only have to avoid obtaining a value on
    column Q (Tipo de movimiento) if appers "Any other text".. but I think I can do it!
    Thanks for your help! Im a begginer in excels use but seems awesome things you can do and these kind of sites that can share
    knowledge.
    Have a great day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •