Comparing Two Sets Of Data Utilizing COUNTIFS() In Excel
The 2010 section, Use equations to discover multi-segment copies in Excel, consolidates IF() and COUNTIF() to discover copy records inside similar informational collection. Be that as it may, utilizing the COUNTIFS() work, you can likewise think about two informational collections for copy records.
For example, the two informational collections appeared underneath share just a single copy record, push 4. Alternate records share normal qualities in sections An and B, yet not C. To rapidly uncover any copies, you can utilize COUNTIFS() to analyze the two informational indexes.
The COUNTIFS() work utilizes the accompanying linguistic structure to indicate different criteria to figure out which esteems in a range to tally:
COUNTIFS(countrange1, criteria1, [countrange2, criteria2]…)
In this illustration, countrangex will allude to a segment in one information range and criteriax will allude to the main comparing information field in a similar segment in the other information go, as takes after:
=COUNTIFS($A$9:$A$13,A2,$B$9:$B$13, B2, $C$9:$C$13,C2)
This illustration incorporates every one of the three sections for a full-record correlation, however you could look at halfway records by including just the segments you need to think about. Likewise, if the informational collections are on various sheets, make certain to incorporate the sheet identifier, properly.
Presently, enter the above capacity in D2 and duplicate to the rest of the columns in similar informational index. This capacity returns 1 when the comparing record in the second informational index is a copy of the primary informational index. At the point when the columns aren’t copies, the capacity returns 0.
You can utilize this section to channel the informational collection, as takes after:
- Tap the Data tab and afterward click Filter in the Sort and Filter gathering to show dropdowns for every segment. In Excel 2003, pick Filter from the Data menu, and after that select AutoFilter.
- Tap the dropdown for segment A.
- To see the copy record(s), select 0, and snap OK.
Additionally, you’d deselect 1 to show your non-copy records and Select All to see them all.
This technique is greatly adaptable on the grounds that the copies don’t need to be in a similar line in the two informational collections. The copies can be anyplace inside the informational index and this capacity will at present discover them.
To enable you to take in this system, test Excel worksheets are accessible.
Blogs : office.com/setup