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

Sirmarte

New member
Joined
May 23, 2014
Messages
3
Reaction score
0
Points
0
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
 

Attachments

  • Rev Excel.xlsx
    388.2 KB · Views: 19

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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
 

Sirmarte

New member
Joined
May 23, 2014
Messages
3
Reaction score
0
Points
0
Hi NBVC,

It helps me lot your formula but I have another last inquiry, as you can see there are 2 variables that I´m 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 a moderator:

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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:

Sirmarte

New member
Joined
May 23, 2014
Messages
3
Reaction score
0
Points
0
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! I´m 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!
 
Top