To max and min comparing with two sheets:

Hi.

I have attached three excel sheets are Data 1,Data 2 and Required Format.

Querry-01.

To compare two excel sheets to return max and min number.

DATA 1

CUSTOMERID* DISPATCH *****DATE BATCH NO
31 ********02/01/2016 **********11814
31 ********02/01/2016 **********11814
31 ********02/01/2016 **********11814
31 ********02/01/2016 **********11814
31 ********02/01/2016 **********11814
31 ********02/01/2016 **********11808
31 ********02/01/2016 **********11808
31 ********02/01/2016 **********11808



DATA 2

CUSTOMER ID ****DISPATCH ********DATE BATCH NO
42 *************08/01/2016 ********11884
42 *************08/01/2016 ********11884
42 *************08/01/2016 ********11884
42 *************08/01/2016 ********11884
35 *************08/01/2016 ********11892
35 *************08/01/2016 ********11892
35 *************08/01/2016 ********11892
35 *************08/01/2016 ********11892
35 *************08/01/2016 ********11892


REQUIRED FORMAT:

STARTING BATCH NO:11808

END BATCH NO:11892

Querry-02.

To end of date how many records passed on single day comparing with two sheets.

REQUIRED FORMAT:

DATE************NO OF RECORDS

02/01/2016*************8

04/01/2016*************19

Herewith attached file.Please help me.

Thanks you so much sir.formulas return perfect.

Query-1

Return max and min number end of date comparing with two sheets.

for Example:

Starting BATCH NO:11897

End Of Batch No:11898

Querry-02.

As requested to you without additional column D if not possible because data very long(i have use column A to Z)

please help me.

shukla.ankur281190

thank you so much as consider my query.

Value return 0.please check with the same.file attached.

B2={MIN(IF(DATA1!$B$2:$B$65536=MAX(DATA1!$B$2:$B$65536),DATA1!$C$2:$C$65536+0))} (if find any blanks column C did not work.without blanks column C value

return perfect)

B3={MAX(IF(DATA1!$B$2:$B$65536=MAX(DATA1!$B$2:$B$65536),DATA1!$C$2:$C$65536+0))}

B5={SUM((FREQUENCY(IF(DATA1!$B:$B='JJ SL.NO'!B$4,DATA1!$C:$C+0),IF(DATA1!$B:$B='JJ SL.NO'!B$4,DATA1!$C:$C+0))>0)+0)}

-(posted by nfsale)

the above mentioned three formula are working fine with comparing single sheets.(DATA1)

the same formula i have try to apply two sheets value return error.

i need to compare two sheets based on results.

please help me.

SORRY SIR wrong attachment.actual file attached please help me.

B5={SUM((FREQUENCY(IF(DATA1!$B:$B='REQUIRED FORMAT'!B$4,DATA1!$C:$C+0),IF(DATA1!$B:$B='REQUIRED FORMAT'!B$4,DATA1!$C:$C+0))>0)+0)}

thanks you so much sir value return is perfect.

i required formulas column B2 AND B3 compare two sheets.

please help me.

thanks you so much for your reply to consider my querry.


B2=MIN(IF(DATA2!$B$2:$B$400=MAX(DATA2!$B$2:$B$400),DATA2!$C$2:$C$400+0),IF(DATA1!$B$2:$B$400=MAX(DATA1!$B$2:$B$400),DATA1!$C$2:$C$400+0))

value return 0.(actual data-11897)

B3=MAX(IF(DATA2!$B$2:$B$400=MAX(DATA2!$B$2:$B$400),DATA2!$C$2:$C$400+0),IF(DATA1!$B$2:$B$400=MAX(DATA1!$B$2:$B$400),DATA1!$C$2:$C$400+0))


value return 11897.(actual data-11898)

please check with attachment.

Great work value return is perfect thank you so much as consider my query continues.

THANKS A LOT.

I have try add reputation but some message to me

"you must spread some reputation around before giving it to shukla.ankur281190 ".

what can i do.please your suggestion because do not add reputation someone helped.