LinkBack URL
About LinkBacks
Remove Duplicates and Find out the rows from sheet 1 and sheet 2 using borrower id and loan date
Remove Duplicates and Find out the rows from sheet 1 and sheet 2 using borrower id and loan date
This formula is not working pls give solution
I want to compare B & C Column with E & F
=IF(B2:C121196 = E2:F126497,"Match","No Match")
=IF(B2:B121196,C2:C121196 = E2:E126497,F2:F126497,"Match","No Match")
What error in this formula ?
Try
=IF(ISNUMBER(MATCH(B2&C2,INDEX($E$2:$E$126497&$F$2:$F$126497,),0)),"Match","No Match")
Copy down.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Please explain about the formula, Actually B&C column has 121196 rows and E&F has 126496 rows if i put formula means it will come the difference only 5300 rows but this formula shows 7356 rows.
This formula would give you match if B&C cells value is in EF column if B&C cell value doesn't exit in E&F column then It would give your Not match. As per post #1 you were trying If condition for checking = two ranges, If function can't do the same.
What you wish to do can you post a sample file with expected result. for attaching sample follow the below mention steps.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I want to show result in sheet 3 that is I want to compare sheet 1 and sheet 2 borrower id and loan date if borrower id and loan date is different means then I want to display that record only in sheet 3. Example the result comes 5300 rows because sheet 1 has 121196 and sheet 2 it has 126496 so if we minus the record means the unique record is 5300 that result need to come in sheet 3
I can't attach here morethan 2mb xcel or .zip file so i have attaching 2.zip files you copy and paste it in 1xcel and do it
Last edited by maddyrafi1987; 11-27-2017 at 02:28 AM.
Try
G3=IFERROR(INDEX($D$3:$D$12,MATCH(0,INDEX(COUNTIF($G$2:G2,$D$3:$D$12)+COUNTIFS($B$3:$B$9,$E$3:$E$12,$A$3:$A$9,$D$3:$D$12),),0)),"")
H3=INDEX($E$3:$E$12,MATCH(G3,$D$3:$D$12,0))
Row\Col Sheet1 Sheet2 Sheet3 Result ID Date ID Date ID Date A E E B A K C B M D C Z E D P F K H G M Z P H
I tried but it shows answer 0
Do upload you sample data, I don't know which type data you have what you wish to achieve. I asked you sample file in post #5 but you didn't update, I can't give you solution further with sample data with expected result.
No sample data i have, In my previous post i attached 2excel file you just copy and paste the 2 files in 1excel and note that the 1st 2column has 121196 rows and next is having 126496 rows if we minus the rows means the ans is 5300 rows so i want to compare the both rows borowweer id and loan date and display the difference 5300 rows as output thats it.
I can't attach my excel file it has moethan 3mb.
This is the huge data set and formula's can be much slow for extracting the result. I have a trick if you wish extract only unique values then copy down second data set under the first data set, Assume you have first data set in A:B till row no 121197 and second data in D:E till 126497. Copy the D:E data and paste it in A:B column. In C2 put =A2&B02 and drag down, select ABC column then press Alt-M you will get a window Remove duplicate Uncheck first two option and only make check mark C column and then ok. These steps remove the duplicate and only present unique list.
Hope this help you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks