Microsoft Excel - need excel formula for one condition

hi ,

i need one formula for ecel pro.
i have 2 excel sheet 1st one is the base data now in both the sheet 1 sr no is unique , now i nedd if unique no same and last meter same then base last no put against same unique no in 2 sheet,

Base Sheet

Base Sheet
SR NO	ID	last No	last meter
123	1	4563	8523
123	5	8563	9632
Req data
SR NO	ID	last No	last meter
123	789633	8563	9632

123
123	5	8563	9632 1 4563 8523

Hello Anu
Try

=MAX((Sheet1!$A$2:$A$3=$A2)*(Sheet1!C$2:C$3))

This has to be entered as an array formula (CTRL/SHIFT/ENTER).  It can be copied into the next cell to give the corresponding last meter reading.  It assumes that the two values change in the same direction at the same time.

Harry

hi harry ,
i need last id  match with sr no and last meter fropm sheet 1.

anu

Hello Anu,
I don't really understand but if you copy the formula to the left you will get the corresponding ID assuming that it increases with the other data.
Harry

hi harry,

data is like below now i ned in req Sheet No is given and last reading given now i need last no in req sheet match no and last meter

Sheet -1
NO	last No	last meter
6467	3575720	25826
6467	3599286	27924
Req
Sheet -1
NO	last No	last meter
6467	3575720	25826

Hell Anu,
Still don't understand.  What determines which is the 'last number'?  Is it that both 'NO' and 'last meter' on Sheet-1 must match the values  for 'NO' and 'last meter' entered  on Req?
Harry

hi harry ,

if Sr no And Meter mactch then case Id put in 2nd sheet B column Case id should be 7896.

Sr no	Case ID	meter
123	7896	4563
123	122	4256
Sr no	Case ID	meter
123		4563

Hello Anu,
Try
=($A$2:$A$3=A6)*($C$2:$C$3=C6)*($B$2:$B$3)
Once again enter as an array formula (C/S/E).  You will have to adjust for references on a different sheet.
Harry

Hello Anu,
You can also use this formula.  It doesn't need to be entered as an array formula.  As before you have to adjust for different ranges and sheet addressing.
=SUMPRODUCT(($A$2:$A$4=A6)*($C$2:$C$4=C6)*($B$2:$B$4))
Harry

hi harry ,

i have one data  like given below  i need in 2nd data match number and Sr no thne fill date and meter in 2nd given data

Number	Sr no	Date	Meter
ab1236	133	10-Jan-14	118381
ab1236	133	10-Jan-14	270686
efd123	742	07-Mar-14	173593
yik753	4451	04-Jan-14	299049
olp321	2121231	05-Mar-14	165907
Number	Sr no	Date	Meter
ab1236	133
ab1236	133
ab1236	1123
efd123	742
yik753	4451
olp321	2121231

Hello Anu,
Can the SR number really be repeated as in your sample data.  If it can that makes the problem much more difficult.
Harry

hi harry ,

yes , i need the data last date data .1st match number and sr no after that take latest date data.
thanks
Anu

Hello Anu,
Can you show me the output that you expect?
Harry

hi Harry,

Number	Sr no	Date	Meter
ab1236	133	10-Jan-14	118381
ab1236	133	10-Dec-13	7563
efd123	742	07-Mar-14	173593
efd123	133	10-Jan-14	11923
yik753	4451	04-Jan-14	299049
olp321	2121231	05-Mar-14	165907
Out put
Number	Sr no	Date	Meter
ab1236	133	10-Jan-14	118381
efd123	1123
efd123	742	07-Mar-14	173593
yik753	4451	04-Jan-14	299049
olp321	2121231	05-Mar-14	165907
wddsfd	2121231
efd123	133	10-Jan-14	11923

Hi Anu,
So, in your first table you want to ignore the second dataset with Number ab1236 and SR 133?
Harry

yes harry ,

because i need last date of data . in row 2 number & sr  are same dut date is 10 dec-13, and in 1 row date is 10-jan-14 so i need 1 row data where my number and sr match and latest date and meter.

anu

Hello Anu,
=MAX((Data!$A$2:$A$7=$A2)*(Data!$B$2:$B$7=$B2)*Data!C$2:C$7)
has to be entered in C2 on the Summary sheet and can be copied across and down as an Array Formula (Ctrl/Shift/Enter).  It assumes that your original data is on a sheet named Data.  Obviously the ranges will have to be adjusted/made dynamic to match your real data.  It gives a zero for non-matching pairs on the Summary sheet.  You will have to decide what you want to do about that.  Perhaps another column with the logic to substitute blanks for zero (=IF(C2<>0,C2,""). It could be done in situ by substituting the given first formula in the second formula whereever there is a C2.  It is a bit clunky and still has to be entered as an Array Formula.
Regards
Harry

hi harry ,

in this formula data not calculate correctally.

Out Put Sheet
Number	Sr no	Date	Meter
ab1236	133	10-Dec-13	7563
efd123	1123	00-Jan-00	0
efd123	742	00-Jan-00	0
yik753	4451	04-Jan-14	299049
olp321	2121231	05-Mar-14	165907
Data Sheet Data
Number	Sr no	Date	Meter
ab1236	133	03-Jun-14	118381
ab1236	133	10-Dec-13	7563
efd123	742	07-Mar-14	173593
efd123	133	10-Jan-14	11923
yik753	4451	04-Jan-14	299049
olp321	2121231	04-Jan-14	299049
Needed Req Data
Number	Sr no	Date	Meter
ab1236	133	03-Jun-14	118381
efd123	1123
efd123	742	07-Mar-14	173593
yik753	4451	04-Jan-14	299049
olp321	2121231	04-Jan-14	299049

Hello Anu,
You must have an error somewhere, it works absolutely fine for me. 
Harry

hi haarry ,

it`s working but when in both sheet numser & sr no in same column. is in data sheet one sr no in row A2 and same sr no in summary sheet in B3 row then results comes 0

thanks
 anu

Hello Anu,
This is what you should have in cell C3
=IF(MAX((Data!$A$2:$A$7=$A3)*(Data!$B$2:$B$7=$B3)*Data!C$2:C$7)<>0,MAX((Data!$A$2:$A$7=$A3)*(Data!$B$2:$B$7=$B3)*Data!C$2:C$7),"")
with curly braces {}around it for the array formula.
Harry

hi harry ,

in summary sheet there is a big data like 5000 rows and it`s not in sequence as data sheet .


anu

Hello Anu,
Then you had better give me some more realistic data.
Harry

Hello Anu,
This works absolutely fine as far as I can see.  There is a problem in the data that you provided in that meter readings for earlier dates were higher than for more recent.  I assume that in the real world the meter reading would increase from reading to reading.  Obviously if the meter rolls over that would require some extra processing in any case.
anu_0614_data sheet.zip
Regards
Harry

Excel-As-Database-demo-v1 (8).zip

hi harry,

i have one excel sheet i need add one drop down box for Month.

thanks
Anu

Hello Anu,
Here is your file with the extra field added.  I do hope that you are trying to understand and learn how to do these things yourself.  It encourages people to help if it is obvious that you have had a go yourself and genuinely have a problem rather than just outright expecting somebody else to do it for you.  You are obviously well aware of Chandoo's site and would do well to work through his excellent tutorials to learn how to do things and not just lift his demonstration spreadsheets and expect somebody to modify them for your own purposes.
Excel-As-Database-demo-v1 (8).zip
Regards
Harry

hi harry ,

thankss .. i try to lean this things one thing i want to know is there any option in this file that we can choose multipal option like two months data filter and show that data

Anu

Hello Anu,
Yes it is possible.
Excel-As-Database-demo-v2 (8).zip
Harry

hi ,

i have one data in excel file i need in branch fshhet data Remarks D column compile in sheet 1 (main sheet) in both the sheet one unique value in Bcolumn . and also if in main file already D cell is fill then no need to be change. on blank data pull from branch file.

thanks
Anuexpl.zip

Hi anu,
Something like:

=VLOOKUP($B$2:$B$4,BranchA!$B$2:$D$4,3,FALSE)

Harry

hi harry,

Actually

 branch data coming every day so evey time i put vlookup for some data .

 So I need some macro so that every time no need to put vlookup.

Anu

Hello anu,
expl.zip
Hope this helps.
Harry

hi harry,

it`s working fine . one thing i need if Once remarks update in main sheet , then updation not required. like in main sheet
one remarks already update like ABc . but in branch file remarks Blank or other remarks .i need no change in main file remarks .

thanks
Anu

hi harry ,

it`s working I Have update in macro

For Each rngSCCell In rngSummaryCode
If rngSCCell.Offset(0, 2).Value = 0 Then
    For Each rngBCCell In rngBranchCode

thanks
 Anu

hi harry ,
 i have one sheet with me like a dash bord . in this excel file 2 sheet one is the summary sheet and other is base data shaat
 i need in sheet 1 pul data from base data ,2 thing when we chane month in sheet one drop down option auto matically change data below and pull data for the same month from base data sheet .


thanks
Anudashborad exmp.zip