2 Attachment(s)

Match specific numbers of two different files and do sum of values ahead of that numbers

I attached two files, match numbers between of two files then do sum of values ahead of that numbers , in this instance

in file which name is mandi in that file if i select range N5 to N550 in that range you can see numbers like 636,646,679 and so on,

ahead of that numbers there is some values , in cell N4 number is 636 ahead of that number in cell Q4 value is 244,

in cell N5 there is number 646, that 646 number also in cell N34 so make sum of values which is ahead of number 646, here is sum is 340 (60+280=340)

so count the value of 646 number's is 340 then match this numbers with numbers which is in file which name is MRN but there you find number

like for example number 636 , this 636 you can found in file MRN like M636 so treat both numbers as same (or duplicate) , then match values

ahead of that number in MANDI file number 646 value ahead of that number is 340, in MRN file number M646 value ahead of number is 340 which is in cell C33

then do sum of values ahead of that selected numbers in selected range which i selected in file MANDi then match sum of both files if not match highlight

that numbers in green colour which values not match which is ahead of that numbers.

Re: Match specific numbers of two different files and do sum of values ahead of that numbe

Perhaps this formula based proposal will help.

In the MRN file on the 01-04-2019 to 31.03.2020 sheet paste the following formula into cell Z15 and copy down:
Formula:

=INDEX('[MANDI.xlsx]Sheet1'!N$3:N$1010,MATCH(0,INDEX(COUNTIF('[MANDI.xlsx]Sheet1'!AB$2:AB2,'[MANDI.xlsx]Sheet1'!N$3:N$1010),,),))

Note that cell Z15 should display "MRN NO."

Paste the following into cell AA16 and copy down: =SUMIFS('[MANDI.xlsx]Sheet1'!Q$3:Q$1010,'[MANDI.xlsx]Sheet1'!N$3:N$1010,Z16)

Paste the following into cell W15 and copy down: =IF(A15="","",VALUE(RIGHT(A15,LEN(A15)-1)))

Paste the following into cell X15 and copy down: =IFERROR(INDEX(AA$16:AA$108,MATCH(W15,Z$16:Z$108,0)),"")

Select the column of cells that you want to format and use the following formula as the conditional formatting rule: =AND(A15<>"",X15<>"",X15<>C15)

Note that the above conditional formatting formula assumes that column A is the column to format. If that is not the case then change the A in A15 to the column to which conditional formatting is applied.

Test by changing the value of cell Q5 on Sheet 1 of the MANDI file.

Let us know if you have any questions.

Re: Match specific numbers of two different files and do sum of values ahead of that numbe

I think i could not apply your formula in appropriate way so it is not working can you apply that formula in attached file for me.

Re: Match specific numbers of two different files and do sum of values ahead of that numbe

The MANDI file will need to be open for the formula to work without a full file path.

That said, in the version of Excel I am using, when I close the MANDI file the first two formulas automatically add the file path to the formula.

If that doesn't work in Excel 2007, and if you need to retrieve the data when the MANDI file is closed, you may need to manually add the path in front of each instance of ...[MANDI.xlsx]Sheet1'!... as in 'C:\keshavtale\Documents\[MANDI.xlsx]Sheet1'!N$3:N$1010...

When I attempt to upload the file to this post it includes the file path to MANDI file on my computer which would be of no use to you.

Please try again to paste the formulas into the MRN file on the 01-04-2019 to 31.03.2020 sheet with the MANDI file open.

Let us know if you have any questions.

1 Attachment(s)

Re: Match specific numbers of two different files and do sum of values ahead of that numbe

However you upload the file by applying formula or send screenshot image so i can view how formula is showing succesfull result,

I also sent shcreenshot of my excel file please see formula appear right or not

1 Attachment(s)

Re: Match specific numbers of two different files and do sum of values ahead of that numbe

There is an errorin the formula I gave you (my apologies) for Z15 and down.

Please change it to read: =INDEX([MANDI.xlsx]Sheet1!N$3:N$1010,MATCH(0,INDEX(COUNTIF(Z$14:Z14,[MANDI.xlsx]Sheet1!N$3:N$1010),,),))

Let us know if you have any questions.

Re: Match specific numbers of two different files and do sum of values ahead of that numbe

Thanks , now my problem is perfectly solved, thanks again, i am very greatfull to you

Re: Match specific numbers of two different files and do sum of values ahead of that numbe

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.