# 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