Microsoft Excel - need excel formula for one condition

Asked By anu anu on 07-May-14 11:49 PM
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
Harry Boughen replied to anu anu on 08-May-14 08:38 AM
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
anu anu replied to Harry Boughen on 08-May-14 11:53 PM
hi harry ,
i need last id  match with sr no and last meter fropm sheet 1.

anu
Harry Boughen replied to anu anu on 08-May-14 11:59 PM
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
anu anu replied to Harry Boughen on 09-May-14 02:40 AM
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
Harry Boughen replied to anu anu on 09-May-14 02:50 AM
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
anu anu replied to Harry Boughen on 09-May-14 06:13 AM
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
     
Harry Boughen replied to anu anu on 09-May-14 08:11 AM
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
Harry Boughen replied to anu anu on 09-May-14 03:57 PM
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
anu anu replied to Harry Boughen on 02-Jun-14 06:20 AM
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
Harry Boughen replied to anu anu on 02-Jun-14 07:55 AM
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
anu anu replied to Harry Boughen on 03-Jun-14 12:21 AM
hi harry ,

yes , i need the data last date data .1st match number and sr no after that take latest date data.
thanks
Anu
Harry Boughen replied to anu anu on 03-Jun-14 01:03 AM
Hello Anu,
Can you show me the output that you expect?
Harry
anu anu replied to Harry Boughen on 03-Jun-14 01:18 AM
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
Harry Boughen replied to anu anu on 03-Jun-14 01:22 AM
Hi Anu,
So, in your first table you want to ignore the second dataset with Number ab1236 and SR 133?
Harry
anu anu replied to Harry Boughen on 03-Jun-14 01:27 AM
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
Harry Boughen replied to anu anu on 03-Jun-14 02:01 AM
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
anu anu replied to Harry Boughen on 03-Jun-14 02:47 AM
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
Harry Boughen replied to anu anu on 03-Jun-14 03:05 AM
Hello Anu,
You must have an error somewhere, it works absolutely fine for me. 
Harry
anu anu replied to Harry Boughen on 03-Jun-14 11:59 PM
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
Harry Boughen replied to anu anu on 04-Jun-14 12:16 AM
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
anu anu replied to Harry Boughen on 04-Jun-14 12:31 AM
hi harry ,

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


anu

Harry Boughen replied to anu anu on 04-Jun-14 12:55 AM
Hello Anu,
Then you had better give me some more realistic data.
Harry
anu anu replied to Harry Boughen on 04-Jun-14 01:42 AM
Harry Boughen replied to anu anu on 04-Jun-14 02:18 AM
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
anu anu replied to Harry Boughen on 05-Jun-14 06:07 AM
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
Harry Boughen replied to anu anu on 05-Jun-14 09:28 PM
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
anu anu replied to Harry Boughen on 06-Jun-14 01:07 AM
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
Harry Boughen replied to anu anu on 07-Jun-14 03:46 AM
Hello Anu,
Yes it is possible.
Excel-As-Database-demo-v2 (8).zip
Harry
anu anu replied to Harry Boughen on 26-Jun-14 07:56 AM
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
Harry Boughen replied to anu anu on 26-Jun-14 09:24 AM
Hi anu,
Something like:

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

Harry
anu anu replied to Harry Boughen on 26-Jun-14 11:20 PM
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
Harry Boughen replied to anu anu on 27-Jun-14 01:59 AM
Hello anu,
expl.zip
Hope this helps.
Harry
anu anu replied to Harry Boughen on 27-Jun-14 02:38 AM
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
anu anu replied to Harry Boughen on 27-Jun-14 02:54 AM
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
anu anu replied to anu anu on 25-Jul-14 02:18 AM
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