+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Forum Contributor
    Join Date
    03-13-2013
    Location
    india
    MS-Off Ver
    excel 2007
    Posts
    243

    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.
    Attached Files Attached Files
    Last edited by keshavtale; 02-15-2020 at 02:48 AM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,133

    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: copy to clipboard
    Please Login or Register  to view this content.

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    03-13-2013
    Location
    india
    MS-Off Ver
    excel 2007
    Posts
    243

    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.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,133

    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.

  5. #5
    Forum Contributor
    Join Date
    03-13-2013
    Location
    india
    MS-Off Ver
    excel 2007
    Posts
    243

    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
    Attached Images Attached Images
    Last edited by keshavtale; 02-22-2020 at 03:14 AM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,133

    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.
    Attached Images Attached Images

  7. #7
    Forum Contributor
    Join Date
    03-13-2013
    Location
    india
    MS-Off Ver
    excel 2007
    Posts
    243

    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

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,133

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1